# Highlight Weekends in Excel

#### Frédéric LE GUEN

Last Updated on 25/06/2023

How to highlight the color of the weekends in Excel? To do that, we must use the conditional formatting tool.

In this article, we have seen how to create and apply your custom rule as conditional formatting. With the same technique, we will apply a specific color for the weekend in Excel.

## Step 1: Create the test for the weekend

When you create your custom rules for conditional formatting, the most important is to create your logical test.

Here, the logical test is quite simple. The function WEEKDAY returns the day value in a week from a date(1 is Sunday, 2 is Monday, ...)

=WEEKDAY(B5) => 1

But you can also add an argument to this function to start your week the Monday and then Saturday = 6 and Sunday = 7

=WEEKDAY(B5, 2) => 7

Finally, to know if a day is a weekend or not, the test is

=WEEKDAY(B5, 2)>5

## Step 2: Use your test as a conditional formatting rule

1. Open the menu Conditional Formatting > New Rule
1. In the next dialog box, select the menu Use a formula to determine which cell to format.

## Step 3: Copy the formula of the test

Note: In this case, you must lock the reference of the rows in order to stay on the date (row = 5)

## Step 4: Customize the color when the test is TRUE

Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

• Select the tab Fill
• Choose one color

## Step 5: Apply the rule to a range of cells

At this step, the conditional formatting is for one cell only. So we must extend the range of cells for this rule.

Click OK, then open Conditional Formatting> Manage Rules

1. Select This Worksheet to see the worksheet rules instead of the default selection.
2. Then in the Applies to section, change the range corresponding to your initial selection when creating your rules to extend it to the whole column.

And now, all the dates equal to a weekends have a different color 😎😍😃

## Change the color of the public holidays.

You can also highlight the public holidays with the COUNTIF function. The technique is explained in this article.

#### 1 Comment

1. Trudy
17/04/2019 @ 17:27

please help me. I have been working on this for 4 hours. I need to know how to write formula to highlight current month and next month in yellow, and highlight a month or any month that is out of date for the month in red. i am tracking upcoming license plate renewals. I want a two month warning and any month past due in red. I prefer to use an icon but a cell highlight is fine.

# Highlight Weekends in Excel

Last Updated on 25/06/2023

How to highlight the color of the weekends in Excel? To do that, we must use the conditional formatting tool.

In this article, we have seen how to create and apply your custom rule as conditional formatting. With the same technique, we will apply a specific color for the weekend in Excel.

## Step 1: Create the test for the weekend

When you create your custom rules for conditional formatting, the most important is to create your logical test.

Here, the logical test is quite simple. The function WEEKDAY returns the day value in a week from a date(1 is Sunday, 2 is Monday, ...)

=WEEKDAY(B5) => 1

But you can also add an argument to this function to start your week the Monday and then Saturday = 6 and Sunday = 7

=WEEKDAY(B5, 2) => 7

Finally, to know if a day is a weekend or not, the test is

=WEEKDAY(B5, 2)>5

## Step 2: Use your test as a conditional formatting rule

1. Open the menu Conditional Formatting > New Rule
1. In the next dialog box, select the menu Use a formula to determine which cell to format.

## Step 3: Copy the formula of the test

Note: In this case, you must lock the reference of the rows in order to stay on the date (row = 5)

## Step 4: Customize the color when the test is TRUE

Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

• Select the tab Fill
• Choose one color

## Step 5: Apply the rule to a range of cells

At this step, the conditional formatting is for one cell only. So we must extend the range of cells for this rule.

Click OK, then open Conditional Formatting> Manage Rules

1. Select This Worksheet to see the worksheet rules instead of the default selection.
2. Then in the Applies to section, change the range corresponding to your initial selection when creating your rules to extend it to the whole column.

And now, all the dates equal to a weekends have a different color 😎😍😃

## Change the color of the public holidays.

You can also highlight the public holidays with the COUNTIF function. The technique is explained in this article.

#### 1 Comment

1. Trudy
17/04/2019 @ 17:27

please help me. I have been working on this for 4 hours. I need to know how to write formula to highlight current month and next month in yellow, and highlight a month or any month that is out of date for the month in red. i am tracking upcoming license plate renewals. I want a two month warning and any month past due in red. I prefer to use an icon but a cell highlight is fine.