How to set up Excel pivot table conditional formatting to highlight number values that are connected to weekend dates in the row labels.
You can use conditional formatting in Excel, to highlight specific data, such as months with high sales numbers.
In this example, conditional formatting is applied to the data in a pivot table. This pivot table has workplace safety data, from a fictitious company. You can get the sample Excel file on my Contextures site in the Download section, at the end of this page.
The conditional formatting rule highlights:
There are 3 main steps in setting up this pivot table conditional formatting, and the details for each step are shown in the sections below:
Before using an Excel function in a conditional formatting rule, it can be helpful to test that function in a worksheet formula.
To test the formula on the worksheet, I did these steps:
To find out which dates occur on a weekend, you can use the Excel WEEKDAY function – it returns a number, based on a date.
We'll use the WEEKDAY function in the conditional formatting rule, and test it on a worksheet first.
In this example, a date is entered in cell B3, and formatted as "ddd, mm d", so the weekday name is visible.
In cell B6, enter the following formula, to find the weekday number for that date.
The default numbering for the WEEKDAY function is from 1 (Sunday) to 7 (Saturday), shown in the screen shot below.
In the numbered list of weekdays, shown above, the weekend day numbers are 1 (Sunday) and 7 (Saturday).
On another worksheet, I've entered a list of dates to test, in cells B3 to B9.
In columns C, there is a formula that combines the OR function, with two WEEKDAY functions, to test for the numbers 1 and 7. This formula is in cell C3, and copied down to C9:
That formula works, and shows a TRUE for each weekend date, but we can improve it, with a small change.
See how to make a simpler formula, in the next section.
In the WEEKDAY function syntax, there are 2 arguments:
The return_type argument controls how the weekdays are numbered, and you can see the numbered list of options in the screen shot below.
Return Type Notes:
The conditional formatting rule should check for weekend dates, and that will be easier, if the WEEKDAY function uses return_type 2: Numbers 1 (Monday) through 7 (Sunday)
Return type 2 puts the two weekend days together, at the end of the list:
The WEEKDAY number can test if a date has a weekday number of greater than 5
To test for weekend dates:
Here's the revised formula in cell C3, to check if the weekday number is greater than 5:
That's the formula to use for the conditional formatting rule
Next, here's how to use that WEEKDAY test formula to highlight weekend data in a pivot table. We want to highlight the
Follow these steps to apply the weekend highlighting in the pivot table:
The data for weekend dates are highlighted in the Incidents column.
WARNING: There's one more important step, so be sure to read the next section!
The conditional formatting looks good when you apply it, but if you change the pivot table layout later, or add new data, the correct cells might not be formatted.
Watch this video to see the steps for applying conditional formatting to pivot tables cells. Then adjust the rule, so new cells are formatted if the pivot table layout changes.
The written steps for changing the pivot table conditional formatting rule are below the video.
Be sure to complete this final step, after adding conditional formatting to pivot table cells.
Follow these steps to adjust the conditional formatting rule, so it refers to the pivot fields, instead of a specific range of cells
Now, if the pivot table layout changes, or if more data is added, the correct cells will continue to be highlighted.
The workplace safety data sample file has 3 years of data in its records. If you want to compare the weekend data for different years, the easiest solution is to make copies of the completed pivot table, and line them up across a worksheet.
To see the weekend incidents over a three year date range comparison, follow these steps:
Workplace Safety: Click here to get the workplace safety data file. The file contains the safety data used in this example, and you can follow the steps above, to set up the pivot table conditional formatting. The zipped Excel file is in xlsx format, and does not contain any macros.
Last updated: March 2, 2022 3:01 PM