Easy Steps to Apply Conditional Formatting in the Pivot Table
Follow the steps to apply conditional formatting in the pivot table.
- Step 1: Select the Data and in the Insert Tab, click on Pivot Tables.
- Step 2: A Dialog box appears.
- Step 3: Insert the pivot table in a new worksheet by clicking ok. Currently, a pivot is blank and we need to bring in the values.
- Step 4: Drag Down Date in Rows Label, Name in Column and Sales in Values.
- Step 5: The pivot table looks like this,
- Step 6: 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.
- Step 7: Click on New rules and another dialog box pops up.
- Step 8: 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.
- Step 9: In the cell, the value section inserts any value you desire, in this case, I have mentioned range from 70000 to 90000.
- Step 10: Click on the format on the bottom.
- Step 11: Select the type of formatting you want, for example, I have selected color (red color) from Fill section.
- Step 12: 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.
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.
- Step 1: Select the data and in the insert Tab, click on Pivot Tables.
- Step 2: Insert the pivot table in a new worksheet by clicking ok.
- Step 3: And drag down Product in Row label and months in the value fields.
- The pivot table looks like this,
- Step 4: 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.
- Step 5: In the first option apply a rule to select the third option.
- Step 6: In the select rule type select the third option of “Format only top and bottom-ranked values”.
- Step 7: In the edit rule description enter 1 in the input box and from the drop-down menu select “each Column Group”.
- Step 8: Click on the format.
- Step 9: In the fill section select “Green Colour” this time. Click on Ok.
- Step 10: 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.
Rules For Conditional Formatting in Pivot Table
In the home Tab under the styles section, there is a button for Conditional Formatting. We can insert a 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 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
- 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.
This has been a guide to Conditional Formatting in the 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 –