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 365

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 365

   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 365
  • 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 365

      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 365
  • Click OK:
Conditional formatting for weekends in Excel 365

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 365

You will see the highlighted data without calculating weekdays:

Conditional formatting for weekends and Wednesdays in Excel 365

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.