Conditional formatting for weekends

Excel 2016
When you create a data table with daily records, you can need to highlight weekends or other specific days. You don’t need to open a Calendar and do it manually, Excel proposes conditional formatting that can help you do it without any calculation.

Select cells you want to format, for example, daily competition results for two teams:

Data of daily competition results in Excel 2016

To highlight weekends, do the following:

   1.   On the Home tab, in the Styles group, click the arrow next to Conditional Formatting... and then click New Rule...:

Conditional Formatting in Word 2016

   2.   In the New Formatting Rule dialog box:

  • In the Select a Rule Type list, select the Use a formula to determine which cells to format:
    Use a formula to determine which cells to format in Excel 2016
  • In the Edit the Rule Description group:
    • In the Format values where this formula is true field enter the formula:

      = WEEKDAY (<date range>, 2) > 5:

      Formula in Format values where this formula is true Excel 2016

      The formula WEEKDAY returns a number between 1 and 7 that corresponds to a particular day of the week. With return_type = 2: 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday and 7 = Sunday.

    • Click on the Format... button to select a format for these cells, for example, just add a background color:
      Format values where this formula is true in Excel 2016
  • Click OK:
Conditional formatting for weekends in Excel 2016

You can create several different formulas for the same cells. For example, to highlight Wednesdays in red as control days:

Edit Formatting Rule for Wednesdays in Excel 2016

You will see the highlighted data without calculating week days:

Conditional formatting for weekends and Wednesdays in Excel 2016

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.