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

See also this tip in French: Mise en forme conditionnelle pour les week-ends.

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

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more