Highlighting the top or bottom values in a PivotTable

Companies of all kinds are interested in their best salespeople, their best customers, and their best-selling products. Excel enables you to create what are called Top 10 conditional formats to identify the top or bottom values in a PivotTable. You can create two types of Top 10 conditional formats in Excel, formats that identify a certain number of top or bottom values or a format that identifies the top or bottom values based on the percentage. For example, if you wanted to identify the top 10% of your customers by sales regardless of the total number of customers, you could create a percentage based format.

In this case, I have monthly revenue data from two companies, FirmA and FirmB, and what I'd like to do is identify the top seven values within the PivotTable. In other words, I don't want to look at totals or grand totals or subtotals; instead, I want to look at individual months. To create that Top 10 conditional format I'll click any cell in the data area and then on the Home tab click Conditional Formatting, point to Top/Bottom Rules, and then select the type of format that I want to create, and in this case I will make it Top 10 Items.

I want to format cells that rank in the top seven so I will edit the value in the number box to the left so that it read 7, and then I want to create a custom format rather than using any of the existing formats, so I'll click the Format box's down arrow and click Custom Format. And this is the Format Cells dialog box that we are all familiar with. In this case, I will change the format of any cell that contains one of the top 7 values to a light orange, and with that change in place I'll click OK and click OK again to accept the format and to apply the change.

Now, in this case, because I had a single cell selected, Excel only applied the conditional format to that cell. However, if I click the Format Options button I can select which other cells to apply the format to. Now I could select All cell showing "Sum of Revenue" values, but that would include Grand Totals, Subtotals, and so on. So instead, what I want to do is to click the Formatting Options button again, and click the final option, which is All cells showing "Sum of Revenue" values for "Month" and "Company".

that excludes all of the Subtotal and Grand Total cells. I'd also like to give you an example of how to create a Bottom Percentage Format. So let's say that I want to find the bottom 33% of the values in this PivotTable. To do that click any data cell in the PivotTable. One is already selected. Then click Conditional Formatting, point to Top/Bottom Rules and then click Bottom 10%. In this case, I want to identify the bottom 33%, so I will delete the 10 in the percentage box and type 33, and in this case I'll leave the format exactly the same, Light Red Fill with Dark Red Text.

Click OK and there's the format. Now I want to apply it to all of the cells excluding subtotals and grand totals, so I'll click the Formatting Options button and then click the last option. Conditional formats that identify the top or bottom values in the PivotTable field make it easier for you to visualize the most and least effective performers in your organization. Remember that you can identify a specific number of top or bottom values or create a rule that identifies the top or bottom percentage of the group, such as the Top 15%.

This flexibility enables you to create exactly the rule you need.