Last week I got an email from Tim. I’ve never met him but according to his email, he enjoys watching my YouTube videos. Thank you Tim. In the email he asked if there’s a way to highlight the highest and lowest values in a pivot table row. I promised Tim that I’d answer his question in this week’s video.

In the Pivot Table above the highest value in each row has a green background and white text and the lowest value in each row has a red background with white text. In the video I show two methods to do this.

The first method applies a Conditional Formatting Rule to each row. For speed, I applied the Conditional Formatting Rule to B3:H3 and used the Format Painter to copy it to rows 4:9. However as there are 7 rows of values in the Pivot Table and each row requires two Rules (one to highlight the highest value and one to highlight the lowest value), I ended up with 14 Conditional Formatting Rules. That is not ideal especially if you need to change the Rule definitions in the future (the Boss prefers blue and yellow for example).

The second method is slightly more complex but only requires two Rules, applied to B3:H9. These are the cells that contain the Pivot Table values. I deliberately excluded the headings, the Grand Total column and the Grand Total row, which will by their very nature contain the largest values.

Both Rules use the “Use a Formula” Rule Type. The formulas I used were:

=B3=MAX($B3:$H3) to find the highest value in the row and =B3=MIN($B3:$H3) to find the lowest value in the row.

You can download a copy of the sample file by clicking through to YouTube. The link is in the YouTube description.