Excel Unplugged

How to Highlight Weekend Dates in Excel Table

Highlighting weekends in Excel How-To Step by Step Tutorial

Highlighting Weekends can be hard in Excel. So just for fun, we’ll do this in three different ways: we’ll use conditional formatting to highlight the weekend dates in a column. Secondly, we’ll then use conditional formatting to highlight the entire row in a table. In conclusion of this post, we’ll level up and use the Lambda function to highlight the whole row in a table. Let’s do this!

Highlighting Weekend Dates Using Conditional Formatting

First, let’s start with a column of dates.

Highlight Weekend Dates in Excel with Conditional Formatting - Excel Table - List of Dates
Table of dates

We add a column WEEKDAY and enter the following formula.

= WEEKDAY([@Date])
Highlight Weekend Dates in Excel with Conditional Formatting - Excel Table - List of Dates
Table of dates with weekday

The week starts with Sunday in the US, so one represents Sunday, and 7 represents Saturday. We wrap the formula in the OR() function and state we are only interested in weekdays values 7 or 1.

= OR(WEEKDAY([@Date])=7,WEEKDAY([@Date])=1)
Highlight Weekend Dates in Excel with Conditional Formatting - Excel Table - List of Dates
Table of dates with TRUE value for weekend days

As a result, we have now found the dates occurring on weekends. Therefore, we can now use this function to create conditional formatting.

Firstly, we select the Date column (this is a crucial step and should not get overlooked as Conditional Formatting will only apply to the area you have selected!).

Highlight Weekend Dates in Excel with Conditional Formatting - Excel Table - List of Dates
Selecting Date column

Select Conditional Formatting > Highlighting Cells Rules > More Rules.

Highlight Weekend Dates in Excel with Conditional Formatting -  Setting Conditional Formatting
Setting Conditional Formatting

After that, we select Use a formula to determine which cells to format.

Highlight Weekend Dates in Excel with Conditional Formatting -  Setting Conditional Formatting
Setting Conditional Formatting

Enter the formula.

= OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1)

Click the Format button in the preview below and set Fill color to green and Font color to white.

Highlight Weekend Dates in Excel with Conditional Formatting -  Setting Conditional Formatting
Setting Conditional Formatting

To sum up, confirm all with OK.

Highlight Weekend Dates in Excel with Conditional Formatting -  Excel Table with Conditional Formatting
Formatted weekends in Excel table

We can now remove the Weekday column and enjoy our neatly formatted Date column!

Highlighting Entire Rows with Weekend Dates Using Conditional Formatting

Similarly, we now have a table of dates with some additional attributes. We want to highlight entire rows in our table. The process is very similar.

Highlight Weekend Dates in Excel with Conditional Formatting -  Excel Table
Excel table of dates

Firstly, select the table.

Highlight Weekends in Excel with Conditional Formatting -  Excel Table
Selected table of dates

Secondly, select Conditional Formatting > Highlighting Cells Rules > More Rules.

Highlight Weekends in Excel with Conditional Formatting -  Setting Conditional Formatting
Setting Conditional Formatting

Select Use a formula to determine which cells to format.

Highlight Weekends in Excel with Conditional Formatting - Setting Conditional Formatting
Setting Conditional Formatting

Enter the formula.

= OR(WEEKDAY($B2)=7,WEEKDAY($B2)=1)

The formula is the same as before, and we only added an absolute reference to the B column using the $ symbol. This means Excel will always check the B column and the corresponding row for each cell in our table and format accordingly.

Again click the Format button in the preview below and set Fill color to green and Font color to white.

Highlight Weekends in Excel with Conditional Formatting - Setting Conditional Formatting
Setting Conditional Formatting

Confirm all with OK.

Highlight Weekends in Excel with Conditional Formatting -  Excel Table with Conditional Formatting
Formatted weekends in Excel table

Highlight Weekend Dates Using Lambda Function

We’ll now level-up and create a new function to use in our conditional formatting.

Select Formulas > Name Manager > New.

Highlight Weekends in Excel with Conditional Formatting - Adding Name with Name Manager
Adding new Name to Name Manager in Excel

We obviously it BestThingEver and enter the formula, wrapped in LAMBDA() function.

= LAMBDA(a, OR(WEEKDAY(a)=7,WEEKDAY(a)=1))

We’ll use letter a as an input parameter.

Highlight Weekend Dates in Excel with Conditional Formatting -  Adding Name with Name Manager
Adding new Name to Name Manager in Excel

Confirm with OK.

The process from here on is similar to before. We select the table.

Highlight Weekend Dates in Excel with Conditional Formatting - Excel Table
Selected table

Select Conditional Formatting > Highlighting Cells Rules > More Rules.

Highlight Weekends in Excel with Conditional Formatting - Setting Conditional Formatting

Select Use a formula to determine which cells to format.

Highlight Weekends in Excel with Conditional Formatting - Setting Conditional Formatting
Setting Conditional Formatting

Enter the formula.

= BestThingEver($B2)
Highlight Weekends in Excel with Conditional Formatting -  Setting Conditional Formatting Rule by Using New Name
Setting Conditional Formatting by calling the Name

We again have our formatted table.

Highlight Weekends in Excel with Conditional Formatting - Excel Table with Conditional Formatting
Formatted weekends in Excel table

Easy as an apple pie!

Watch the Video Tutorial

We know some of you prefer to watch the mastery live, and we have this one for you in the video as well. You can find the video version of this tutorial here: https://www.youtube.com/watch?v=GFVOI6yHwz8

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!

Read related posts: