Table of Contents
Conditional Formatting Pivot Table in Excel
Conditional formatting is a tool which allows us to apply or change formats to a cell or range of cells. Conditional formatting can be done on Pivot tables also. The formatting depends on the condition or the value or the change in the formula.
When we use conditional formatting in pivot it works as dynamic formatting. Whenever we change the values or filter the data, the conditional formatting automatically gets updated with the respective change.
Let us learn Conditional Formatting in Pivot Tables with the help of examples.
How to Apply Conditional Formatting in the Pivot Table?
Below are the examples of applying Conditional Formatting in Pivot Table.
We have a set of Data in which three sales person, Alpha, Beta & Gamma have done sales from 22nd of Dec to 25th of Dec.
To apply the pivot table in this data,
- Select the Data and in the Insert Tab, click on Pivot Tables.
A Dialog box appears.
- Insert the pivot table in a new worksheet by clicking ok. Currently, a pivot is blank and we need to bring in the values.
- Drag Down Date in Rows Label, Name in Column and Sales in Values.
- The pivot table looks like this,
- Now to apply conditional formatting in the pivot table, First, select the column to format in this example select Grand Total Column. In the Home Tab in the Styles Section click on Conditional Formatting and a dialog box pops up.
- Click on New rules and another dialog box pops up.
- There are a variety of rules to be applied in the pivot table. For this example, we will select, “Format Only Cells That Contain” and we see another dialog box in the same window.
- In the cell, the value section inserts any value you desire, in this case, I have mentioned range from 70000 to 90000.
- Click on the format on the bottom.
- Select the type of formatting you want, for example, I have selected color (red color) from Fill section.
- Click on Ok and again ok to close the Formatting rule dialog box.
And the pivot table is formatted with red color with the values between 70000-90000 in red color.
The above example is an example of dynamic formatting means if we change the values the formatting changes.
- Change the values for the dates of 22nd & 25th Dec so that total does not come in the range of 70000-90000.
- The value in yellow color are recently changed and the effect on the pivot table formatting is
As the values changed the formatting changed too.
We have Data for a canteen which is newly launched and we have the records for the number of products sold in each of five months i.e. Jan, Feb, March, Apr, May
First, let us insert a pivot table in this data.
- Select the data and in the insert Tab, click on Pivot Tables.
- Insert the pivot table in a new worksheet by clicking ok.
- And drag down Product in Row label and months in the value fields.
- The pivot table looks like this,
- Now our motive is to highlight those cells which have the highest number of product sold in each row. Select any cell in the pivot table. Click on Conditional formatting and click on a new rule.
- In the first option apply a rule to select the third option.
- In the select rule type select the third option of “Format only top and bottom ranked values”.
- In the edit rule description enter 1 in the input box and from the drop-down menu select “each Column Group”.
- Click on format.
In the fill section select “Green Colour” this time. Click on Ok.
Click on ok to end the formatting rule dialog box.
The above formatting compared the value in the column if we want the formatting in the rows then we can select each row group.
Explanation of Conditional Formatting in Pivot Table
We know that a pivot table is a very important tool to analyze and summarise a large chunk of data. Conditional formatting makes it more appealing to the users. Like for example in a company performance sheet if we have data for various employees regarding their work performance and the data is in a pivot table.
Instead of letting the user go ahead and use a filter to check the top and bottom performer we can use conditional formatting in the same pivot table to highlight the highest and lowest values which will therein show the highest and lowest performer
Rules of conditional formatting in the pivot table?
In the home Tab under the styles section, there is a button for Conditional Formatting. We can insert new rule as per our requirements.
There are various rules for conditional formatting. They are as follows:
- Format Cells Based on their values
- Format cells that only contain a specific data
- Format only top and bottom ranked values
- Format only values that are above or below average
- Use a formula to determine which cells to format.
Things to Remember About Excel Conditional Formatting in Pivot Table
- Select rules as per requirement.
- When the conditional formatting is applied to a block of cells then formatting is applied to only those specific cells.
- Any change in Data may represent the wrong illustration in conditional formatting in pivot tables.
You can download this Conditional Formatting in Pivot Table Excel template here – Conditional Formatting in Pivot Table Excel Template
This has been a guide to Conditional Formatting in Pivot Table. Here we discuss how to Apply Conditional Formatting to Excel Pivot Table along with practical examples and downloadable excel template. You may learn more about excel from the following articles –