This Pivot Table conditional formatting tutorial is the last in this series. Well done on completing the Excel accounts tutorials. More will be added, so make sure you subscribe so that you are notified about any new Excel tutorials added to this series.
If this is your first time here then why start from the Setting up the Accounts Sheet and go through the tutorials. You'll no doubt learn something new.
At first Pivot Table Conditional Formatting may seem as straightforward as regular conditional formatting. For those who are wondering what conditional formatting is, it's when you change the colour of a cell based on a value. Also, conditional formatting can be used to display Icon's, change text colour or even show mini charts.
In this Pivot Table conditional formatting tutorial we will look at the proper way to use conditions in Excel to format data in a PivotTable. As a result you will be able to apply what you learn to your own Excel spreadsheets.
Starter file - should you want to follow along. If, however, you want to jump to the end here is the completed file.
The main reason that Conditional Formatting with Pivot Tables doesn't work for most people is that they go about it the way they would usually go about Conditional Formatting in a spreadsheet. They, probably, would highlight the rows that they want to apply the Conditional Formatting to and click on the Conditional Formatting drop-down menu on the home tab. They might even think that it's working OK. That is until you filter the data or add a Slicer. Once you include a Slicer on your Pivot Tables or do any small amount of filtering, it will be painfully clear that Conditional Formatting is not working for you or your Pivot Tables.
The reason behind this is because instead of using cell references, which Conditional Formatting needs to work, Pivot Tables use the column headers of the source data to build the Pivot Table report. You may have notice that when you try to refer to a cell within a Pivot Table you get a weird GETPIVOTDATA function. So, obviously, when you try to use Conditional Formatting in a Pivot Table you encounter the same issue. (This tutorial will show how to add conditional formatting within a Pivot Table. If you want to use standard conditional formatting then check out this Microsoft Excel Support page on conditional formatting.
So if you want to do Conditional Formatting in an Microsoft Excel Pivot Table the correct way, then you can apply the formatting to a single cell. After that, you can use the formatting options menu to apply the formatting to the entire field. However, there is one thing in which you need to be careful. Before you click on the Conditional Formatting menu in the Ribbon at the top of the screen, make sure that you select a cell in the Pivot Table that's within the grouping level you want to apply the Conditional Formatting to. If you want to know what I'm talking about, try the following. (Download the Exercise file first.)
Now you can see that all values in the % Profit column that are below 90% are highlight in red. What is more you can filter the Pivot Table using the Slicer and it still works.
In Pivot Tables Conditional Formatting an entire row can be a little tricky. Because Pivot Tables are using the column headers in the source data, you can't use the dollar sign to copy the formula across. I'm afraid that you're going to have to do this manual. Or, at least, it's the only way I know how to do this. Before you carry out this exercise you'll have to delete the previous Conditional Formatting rule we set up in the last exercise.
Here's how to delete a Pivot Table Conditional Formatting rule.
So now you're wondering how to conditionally format an entire row in a Pivot Table aren't you? First you need to create a Conditional Format based on a formula. Here's how to do it.
We're nearly finished. The last thing we need to do is to is copy, or duplicate, the Conditional Formatting rule to apply to the other columns in the Pivot Table.
The last thing that we will do is to apply Conditional Formatting in our Pivot Table based up the value of another cell. As a result we will be able to set a target that can be updated with ease. Here is what you do:
At last you're finished. In conclusion, it can be a real pain Conditionally Formatting in Pivot Tables. However, the effort is worth it. By knowing how to apply Pivot Table Conditional Formatting you can create KPI's within your Pivot Tables and bring your data to life.
This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everyday Excel hints and tips.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!
At Computer Tutoring, we prioritise your privacy and only collect the necessary information to provide our services. We will only share the personal data you provide in a form if you have opted in for those services. Our website also uses Google Analytics. You have the right to access, amend, or request deletion of your personal data by contacting us at info@computertutoring.co.uk.
For more details, please read our full Privacy Policy.