At first Pivot Table Conditional Formating may seem as straighforward 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.
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 wihin 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 everday Excel hints and tips.