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.

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.