Conditional formatting for weekends

Excel 365
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, select the Conditional Formatting drop-down list, and then click New Rule...:

Conditional Formatting in Excel 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, add 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 weekdays:

Conditional formatting for weekends and Wednesdays in Excel 2016

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