Using conditional formatting to highlight weekend dates in Excel

 Jul 09, 2015

When you design an automated calendar in Microsoft Excel, you don’t need to colour the weekends yourself. With the conditional formatting tool, you can automatically change the colours of weekends by basing the format on the WEEKDAY function. Assume that you have the date table–a calendar without conditional formatting:

Before:

conditional-formatting-01

After

conditional-formatting-02

To change the colour of the weekends, select only cells B1 and B2 to make this an interactive month and year example, open the menu Conditional Formatting > New Rule.

Select the menu Use a formula to determine which cell to format.

In the text box format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2)>5

(The parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends. Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.)

conditional-formatting-03

Then, customise the format of your condition by clicking on the Format button and choose a fill colour (green in this example).

Click OK, then open Conditional Formatting> Manage Rules

Select This Worksheet to see the worksheet rules instead of the default selection. In Applies to, change the range that corresponds to your entire table when creating your rules to extend it to the whole column. Now you will see a different colour for the weekends.

conditional-formatting-04

How do your Excel skills stack up?   

Test Now  

About the Author:

Alice Antonsen  

Alice is one of New Horizons’ most experienced Desktop Applications trainers. She is qualified with a Diploma of Business (Computing) and a Diploma of Information Technology. Since joining New Horizons in 2006, Alice has achieved the prestigious status of a certified Microsoft Office Master and for the past 4 years, has been placed in the Top 25 Desktop Applications trainers for New Horizons Worldwide. Throughout her career, she has gained a wealth of knowledge and experience in providing training to individuals and groups of varying skill levels. Alice delivers each training session with great care and consideration ensuring each one is tailored to the learning needs of her students.

Read full bio
top