Home » Advanced » Change the color of holidays

# Change the color of holidays

Last Updated on 07/03/2021 by Frédéric LE GUEN

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

## 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.

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)

And for the 4 July

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

## 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 write the test in the custom rule textbox.

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

## 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.

# Change the color of holidays

Last Updated on 07/03/2021 by Frédéric LE GUEN

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

## 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.

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)

And for the 4 July

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

## 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