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


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

Image: Paul Barnwell / Shutterstock

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

I am using Microsoft 365 on Windows 10 64-bit system. I would access Excel on the web through OneDrive’s app launcher for example. If you want to work with me, you can download the demo .xlsx file, but you can use your own .xlsx file. If OneDrive isn’t available to you, go to Office.com and sign in using your Microsoft account (if you don’t have one, create a free account).

For a long time, I have referred to all the online versions as Excel Online, Word Online, etc. Today, I am switching to Excel on the web, Word on the web, etc., in keeping with Microsoft’s terminology.

About the WEEKDAY() function

To highlight weekend dates, we will use the WEEKDAY() function in the 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 return type argument specifies the type of the return value. See Table A for return values.

Table A

Argument Explanation
1,17, or omitted 1 specifies Sunday as the first day of the week, Saturday is 7.
2 or 11 1 specifies Monday as the first day of the week, Sunday is 7.
3 0 specifies Monday as the first day of the week, Sunday is 6.
12 1 specifies Tuesday as the first day of the week, Monday is 7.
13 1 specifies Wednesday as the first day of the week, Tuesday is 7.
14 1 specifies Thursday as the first day of the week, Wednesday is 7.
15 1 specifies Friday as the first day of the week, Thursday is 7.
16 1 specifies Saturday as the first day of the week, Friday is 7.

In our case, it doesn’t matter which returnType argument we use as long as the returnValue adjusts its position. The simplest function uses 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 a function works always helps when you want to apply it to your work later.

Figure A Shows a long list of consecutive dates. Enter function to determine which dates are weekend dates

=WEEKDAY(C3,2)>5

in D3 and copy it to the rest of the cells. This simple function returns TRUE for the weekend dates in column C. A return value of 2 sets the following return value for the week:

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

Simple comparison will match only Saturday and Sunday. As mentioned, you can use other returnValue values, but the expression will be more complex.

Figure A

The WEEKDAY() function returns TRUE for weekend dates.
The WEEKDAY() function returns TRUE for weekend dates in Excel.

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

How to Apply Rules Online in Excel

Remember, we are 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 select New Rule from the dropdown (Figure B,
  2. Excel opens the new Conditional Formatting pane on the right. It is much easier to work with this pane as compared to the desktop interface.
  3. Apply to Category is set to the category selected in step 1 by default.
  4. From the Rule Type dropdown, select Formula.
  5. All values ​​in the format where this formula is the right 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 you can with this control if you want. The options are still limited to the specific format set. You can’t customize formats yet.
  7. check your work against Figure CAnd when done, click Done.

Figure B

Select the New Rule option (which was not always available online).
Select the New Rule option in Excel (which was not always available online).

Figure C

Set the following conditional formatting options.
Set the following conditional formatting options in Excel.

as you can see in Figure D, the New Testament highlights weekend dates. You can easily check this by looking for TRUE in column D. Again, you don’t need the function in column D. These provide some visual insight into the whole process.

Figure D

The rule only highlights weekend dates.
The rule highlights only weekend dates in Excel.

Let us know the date of the first weekend, 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 recent upgrades, you can do a lot with conditional formatting online. With conditional formatting, you can quickly review any rules that have been applied as shown in Figure E, This is a nice addition that desktop doesn’t offer.

Figure E

Keep the pane open to quickly review applicable rules.
Keep the pane open to quickly review applicable rules in Excel.

Stay Connected With Us On Social Media Platforms For Instant Updates Click Here To Connect With Us TeaveterAnd Facebook

we are on now Wire. Click here to join our channel (@TechiUpdate) And stay updated with the latest technology headlines.

for all the latest Technology News Click Here

Follow us on Google News for the latest news and updates,

read original article here

Denial of responsibility! NewsAzi is an automated aggregator around global media. All the material is available free of cost on the internet. We have arranged it on one platform only for educational purpose. In each material, a hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all content belongs to their authors. If you are the owner of the content and do not want us to publish your content on our website, please contact us E-mail – [email protected], Content will be removed within 24 hours.





Source link

Leave a Comment