Change the color of holidays

Last Updated on 07/03/2021
Reading time: 2 minutes

How to highlight specific days, like holidays, in Excel

Conditional formatting

With the conditional formatting tool, you can change the color of your cells automatically.

You can use the default setting but you can also create your own rule with formulas.

List of public holidays

In the following document, you have the list of public holidays for the United States

USA Public holiday 2019

Formula to find a public holidays

In the following calendar we have the days of July 2019. The colors of the weekends are managed by a conditional formatting rule.

How to highlight the 4th July 2019

The idea is to count if each day in the calendar is in the list of the public holiday. And the function that count items in a list it's the function COUNTIF or COUNTIFS.

For instance, for the 3 July, the formula is the following

=COUNTIF(Holiday!$A$2:$A$12,D1)

Formula to count if the 03 July is in the list of Public Holiday

And for the 4 July

=COUNTIF(Holiday!$A$2:$A$12,E1)

04 July is in the list of Public Holiday

Convert the formula to a test

As you see, the result expected is 1. So it's easy to write our test like this. Yes, the reference here is B1, the first cell of the calendar

=COUNTIF(Holiday!$A$2:$A$12,B1)=1

Create the conditional formatting rule

We have seen in this article how to create your own conditional formatting rule with formulas.

Menu conditional formatting Manage rules

We write the test in the custom rule textbox.

Conditional formatting custom rule for holidays

It is very important to lock only the reference of the row (B$1). Like that, your test will always check the values of the first row ; the date 😉

And finally, we adjust the range of cells where to apply the rules

Adjust the range of cells for the conditional formatting rule

Impact on the calendar

And then, automatically, when a date is detected in the public holiday table, the rule will change the color in the calendar.

Public holidays have a specific color

Leave a Reply

Your email address will not be published. Required fields are marked *

Change the color of holidays

Reading time: 2 minutes
Last Updated on 07/03/2021

How to highlight specific days, like holidays, in Excel

Conditional formatting

With the conditional formatting tool, you can change the color of your cells automatically.

You can use the default setting but you can also create your own rule with formulas.

List of public holidays

In the following document, you have the list of public holidays for the United States

USA Public holiday 2019

Formula to find a public holidays

In the following calendar we have the days of July 2019. The colors of the weekends are managed by a conditional formatting rule.

How to highlight the 4th July 2019

The idea is to count if each day in the calendar is in the list of the public holiday. And the function that count items in a list it's the function COUNTIF or COUNTIFS.

For instance, for the 3 July, the formula is the following

=COUNTIF(Holiday!$A$2:$A$12,D1)

Formula to count if the 03 July is in the list of Public Holiday

And for the 4 July

=COUNTIF(Holiday!$A$2:$A$12,E1)

04 July is in the list of Public Holiday

Convert the formula to a test

As you see, the result expected is 1. So it's easy to write our test like this. Yes, the reference here is B1, the first cell of the calendar

=COUNTIF(Holiday!$A$2:$A$12,B1)=1

Create the conditional formatting rule

We have seen in this article how to create your own conditional formatting rule with formulas.

Menu conditional formatting Manage rules

We write the test in the custom rule textbox.

Conditional formatting custom rule for holidays

It is very important to lock only the reference of the row (B$1). Like that, your test will always check the values of the first row ; the date 😉

And finally, we adjust the range of cells where to apply the rules

Adjust the range of cells for the conditional formatting rule

Impact on the calendar

And then, automatically, when a date is detected in the public holiday table, the rule will change the color in the calendar.

Public holidays have a specific color

Leave a Reply

Your email address will not be published. Required fields are marked *