How to work more productively online using new conditional formatting options to highlight dates in Excel

Excel for the web now supports conditional formatting, and the interface is easier to use than the desktop edition. Susan Harkins shows you how to use it.

Microsoft 365 online apps are limited a bit—you have a lot of functionality, but they lean toward basic tasks. Excel for the web’s conditional formatting options are now almost as full-bodied as desktop Excel’s. In fact, I find the interface in Excel for the web easier to use than the desktop. In this article, we’ll run through the options for the online version. Even if you’re familiar with the online interface, you might learn something new about the function used: WEEKDAY(). We’ll use this function to highlight weekend dates.

I’m using Microsoft 365 on a Windows 10 64-bit system. I’ll access Excel on the web via OneDrive’s App Launcher for examples. You can download the demonstration .xlsx file, if you’d like to work along with me, but you can use your own .xlsx file. If OneDrive isn’t available to you, visit Office.com and sign in using your Microsoft account (if you don’t have one, create a free account).

For a long time, I’ve referred to all of the online versions as Excel Online, Word Online and so on. Today, I’m making the switch to Excel on the web, Word on the web, and so on, in keeping with Microsoft’s verbiage.

About the WEEKDAY() function

To highlight weekend dates, we’ll use the WEEKDAY() function in a conditional formatting rule. This function returns a number between 1 and 7 to identify the day of the week, as a date. It uses the form

WEEKDAY(value, [returntype]

where value is a valid date value and the optional returntype argument specifies the type of return value. See Table A for the returnvalue values.

Table A

In our case, it doesn’t matter which returntype argument we use as long as returnvalue accommodates its position. The simplest function uses the returnvalue 2; Monday is 1 and Sunday is 7. Now let’s use this function at the sheet level, so you can see how it works. You won’t need the following to apply the conditional format later but understanding how the function works always helps when you want to apply it to your own work later.

Figure A shows a long list of consecutive dates. To determine which dates are weekend dates, enter the function

=WEEKDAY(C3,2)>5

into D3 and copy it to the remaining cells. This simple function returns TRUE for weekend dates in column C. The returnvalue of 2 sets the following returnvalues for the week:

• Monday is 1
• Tuesday is 2
• Wednesday is 3
• Thursday is 4
• Friday is 5
• Saturday is 6
• Sunday is 7

The simple comparison will match only Saturdays and Sundays. As mentioned, you could use other returnvalue values, but the expression will be more complicated.

Figure A

Now that we know what function we’ll use to set a conditional format, let’s do so.

How to apply the rule online in Excel

Remember, we’re working online, not in the desktop version. To apply a conditional formatting rule, do the following:

1. With the dates selected (C3:C37), click the Home tab, click Conditional Formatting, and then choose New Rule from the dropdown (Figure B).
2. Excel opens the new Conditional Formatting pane to the right. This pane is much easier to work with than the desktop interface.
3. The Apply to Range is set to the range selected in step 1 by default.
4. From the Rule Type dropdown, choose Formula.
5. In the Format All Values Where This Formula is True control, enter the following function (without the grammatical period character at the end: WEEKDAY($C3,2)>5.
6. We don’t need to change the Format With option, but if you wanted to you would do that with this control. The options are still limited to specific format sets. You can’t customize the formats yet.
7. Check your work against Figure C, and when done, click Done.

Figure B

Figure C

As you can see in Figure D, the new rule highlights the weekend dates. You can easily check, by looking for TRUE in column D. Again, you don’t need the functions in column D. These provide a bit of visual insight into the whole process.

Figure D

Let’s breakdown that first weekend date, March 5 (C7):

WEEKDAY(C7,2)>5

WEEKDAY(44625,2)>5

WEEKDAY(6)>5

TRUE

Because the function returns TRUE, Excel applies the conditional format. Thanks to the recent upgrades, you can do a lot more with conditional formatting online. With the Conditional Formatting open, you can quickly review any rule applied as shown in Figure E. This is a nice addition that the desktop doesn’t offer.

Figure E