Format Pivot Table Labels Based on Date Range

In a pivot table that contains a long list of dates, you can use conditional formatting to highlight a specific date range. In this example, the pivot table contains forecast data for 2.5 years, with dates in the OrderDate field.

When the file opens, we’re usually interested in checking the forecast amounts for the upcoming month. It can take a bit of time and concentration to find those dates in the long list. We’ll highlight the labels in yellow, so they stand out, and are easy to find.

We’ll use dynamic conditional formatting (Next Month), so the highlighting will change each month when we open the workbook.

Highlight the Upcoming Month

Follow these steps to highlight Row Labels where the order forecast date is in the upcoming month. It’s currently August, so the September dates will be highlighted.

1. In the pivot table, remove any filters that have been applied – all the rows need to be visible before you apply the conditional formatting.
• 
2. Select all the dates in the Row Labels that you want to format.
3. On the Ribbon, click the Home tab, and then in the Styles group, click Conditional Formatting.
4. In the list of conditional formatting options, click Highlight Cells Rules, and then click A Date Occurring.
• 
5. In the date range drop-down, select Next Month, and then click the arrow to open the formatting drop-down list.
6. Select one of the formatting options, or create a Custom Format. I selected Custom Format, and used a yellow fill colour.
• 
7. Click OK to close the A Date Occurring dialog box.

The dates from the upcoming month are highlighted, and will stand out in the report when you open the workbook.

Conditional Formatting Warnings

This pivot table’s source data won’t change, because we don’t alter the forecast after it has be finalized.

• However, if you apply conditional formatting to a pivot table, and new data is added, it might not be included in the formatted area. Be sure to check the range, in the Manage Rules box for Conditional Formatting, and edit the rule, if necessary.
• Also, if you change the location of the date field, the conditional formatting will not automatically move with that field. You will have to modify the conditional formatting rule, to point to the new location.

___________________________-