How to add conditional formatting a Microsoft Excel PivotTable without expressions

You don’t need a complex expression to add conditional formatting to a filtered PivotTable in Microsoft Excel.

Microsoft Excel PivotTables range from easy to complex, and data and reporting requirements determine your efforts. It’s usually a simple matter of restricting your data so Excel can analyze it without too much calculating on your part. Often, especially once you get the hang of PivotTables, it’s easier to create a PivotTable than it is to produce the right expression to get the results you need. Where conditional formatting is concerned, this is often the case.

In this Excel tutorial, I’ll show you how to apply conditional formatting to a filtered PivotTable in Excel. Although the requirement will be a bit complex, it will require no expression to achieve the results you want. Specifically, we’ll use a PivotTable to report sales figures per month. We’ll then use a slicer to filter by the region. A conditional format will then highlight the top monthly figure in each region. You might be surprised at how easy it is. Along the way, you’ll learn how to group records by date components. You download the demo for this Excel tutorial.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions through Excel 2013. Excel for the web supports PivotTables.

How to create a PivotTable in Excel

We want a simple PivotTable that reports monthly sales by the region, based on the Table shown in Figure A. You can also work with a normal data range.

Figure A

To create the initial PivotTable, do the following:

1. Click anywhere inside the Table or data range.
2. Click the Insert tab.
3. In the Tables group, click PivotTable and choose From Table/Range from the dropdown.
4. In the resulting dialog, click Existing Worksheet. Excel has correctly set the range (TableSales).
5. Click inside the Location control and then click a cell in the sheet, such as G3 (Figure B).
6. Click OK and Excel inserts an empty PivotTable frame.

Figure B

Figure C

Using Figure C, drag fields to the appropriate controls to build the PivotTable frame. Excel adds the Month field to the Rows list for you, basing it on the Date column. Next, let’s add the slicer that will filter the table by region.

How to insert a slicer in Excel

To filter the PivotTable by the region, we’ll insert a slicer. This process is extremely simple. To insert a slicer, do the following:

1. Click anywhere inside the PivotTable.
2. Click the contextual PivotTable Analyze tab.
3. In the Filter group, click Insert Slicer.
4. In the resulting slider, click Region (Figure D).
5. Click OK.

Figure D

Figure E

Once the slicer is in place, try clicking any region to filter the PivotTable. As you can see in Figure E, I’ve clicked Southwest. Now it’s time to add the conditional format.

How to add the conditional format in Excel

Once you have a PivotTable, you’re ready to add the conditional format. It’s easier to implement than you might think because Excel offers specific settings, so you don’t have to enter an expression. To illustrate this behavior, do the following:

1. Click any cell the Sum of Amount values, excluding the header cell and the total cell at the bottom.
2. On the Home tab, click Conditional Formatting in the Styles group and choose New Rule from the dropdown.
3. In the resulting dialog, click the third option, All Cells Showing “Sum of Amount Values” for “Months” in the first pane.
4. In the second pane, select the third rule option, Format Only Top or Bottom Ranked Values.
5. In the bottom pane, change 10 to 1 in the control to the right of the Top dropdown. You’re telling Excel to highlight only the highest value.
6. From the final dropdown, choose the All Values option (Figure F).
7. Click Format.
8. Click the Fill tab, choose a color.
9. Click OK twice to return to the Excel sheet.

Figure F

Figure G

As you can see in Figure G, the top value in the Southwest region is 44064.

What you’re about to see next might seem like a bit of magic, but it’s only the PivotTable doing exactly what you asked. Click Central in the slicer. Not only does the PivotTable adjust the contents, but it also highlights the top sales figure for the Central region, as shown in Figure H. Click all of the regions to see the highlight adjust accordingly. If you remove the filter, the conditional rule highlights the greatest value in the entire PivotTable, as shown in Figure I.

Figure H

Figure I

The results look as if you worked hard to get a conditional format that updates when filter. All that’s really required is a fundamental knowledge of how to format a PivotTable.