Pinterest Pixel
All You Need to Know About

Conditional Formatting in Excel Pivot Table Tutorials

Learn how to perform Excel Pivot Table Conditional Formatting. You can add color & shapes to your data & reports to highlight key findings!

Here are the top things on what you can do with applying Conditional Formatting to Pivot Tables in Excel:

Show the Difference From Previous Month with Directional Icons

Using a Pivot Table you can show the variance from your previous month’s sales very easily…
See how to Show The Difference From Previous Months With Excel Pivot Tables here
pivot table2
Now that we have the Pivot Table report above, we can show the variances per month using an up/down or directional icon suing Conditional Formatting.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Click on any variance value in the Pivot Table and go to Home > Conditional Formatting > Icon Sets > Directional
icon sets
STEP 2: This will bring up the Apply Formatting Rule to dialogue box.  Choose the 3rd option as this will apply the conditional format on all the values except the Subtotals
apply formatting rule
Your Pivot Table will look like this:
Pivot Table1
STEP 3: Now we need to make some edits in the Conditional Formatting Rule in order to get the Icons right.
Go to Home > Conditional Formatting > Manage Rules > Edit Rule
STEP 4: Inside the Edit Formatting Rule box you need to make the following changes:
Value = 0
Type = Number
“Check” the Show Icon Only box and press OK to confirm the changes:
edit formatting rule
STEP 5: In the next screen you will get the Conditional Formatting Rules Manager.  Hit Apply to see the changes and OK to confirm them:
rules manager
Now you have your Pivot Table showing the differences from the previous months with directional icons only!!!
pivot table 3

Icon Sets In A Pivot Table

An Icon Set is a Conditional Formatting icon/graphic that you can include in your cells or Pivot Tables.
The icon will depend on the cell´s value so you can highlight key variances or trends.  There are a few sets that you can include, like:
DIRECTIONAL (Change in values)
Directional2
SHAPES (Milestones)
shapes
INDICATORS (Positive/Negative)
indicators
RATINGS (Scores)
ratings
I show you how easy it is to insert an Icon Set within a Pivot Table that will show a “directional icon” depending on the change of the monthly sales values.
So when monthly sales increase from the previous month, a green up arrow is shown and when monthly sales decrease, a red down arrow is shown.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Place the SALES Field in VALUES
Icon Sets in Pivot Table
STEP 2: Click on the new field and Select Value Field Settings
Icon Sets in Pivot Table
Go to Show Values as > Difference From > (previous) to get the difference from the previous month
Icon Sets in Pivot Table
STEP 3: Click in a variance cell. Go to Home > Styles > Conditional Formatting > Icon Sets > The First Icon Set
Icon Sets in Pivot Table
STEP 4: Make sure to select the third option. This excludes the subtotals and grand totals.
Icon Sets in Pivot Table
STEP 5: Go to Home > Styles > Conditional Formatting > Manage Rules
Icon Sets in Pivot Table
Select Edit Rule.
Icon Sets in Pivot Table
Set the settings to the ones shown below. This will set the column to show the arrow icons only.
Icon Sets in Pivot Table
Your icons are now ready in your Pivot Table!
Icon Sets in Pivot Table

Conditionally Format a Pivot Table With Data Bars

Data Bars are a new Conditional Formatting feature in Excel 2010 and they add a colored bar to your values.
The length of the data bar represents the value in the cell.  A longer bar represents a higher value.
You have a Gradient Fill or a Solid Fill to choose from as well as 6 different pre-determined colors.
If you select the More Rules option then you can select more colors as well as many different values types to format.

DOWNLOAD EXCEL WORKBOOK

Conditional Formatting