Conditional Formatting in Pivot Table

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.

Conditional Formatting Pivot Tables Example 1-1

  • Step 2: A Dialog box appears.

Conditional Formatting Pivot Tables Example 1-2

  • 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.

Conditional Formatting Pivot Tables Example 1-3

  • Step 4: Drag Down Date in Rows Label, Name in Column, and Sales in Values.

Conditional Formatting Pivot Tables Example 1-4

  • Step 5: The pivot table looks like this,

Conditional Formatting Pivot Tables Example 1-5

  • 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.

Conditional Formatting Pivot Tables Example 1-6

  • Step 7: Click on New Rule, and another dialog box pops up.

Conditional Formatting Pivot Tables Example 1-7

  • 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.

Conditional Formatting Pivot Tables Example 1-8

  • Step 9: In the cell, the value section inserts any value you desire; in this case, I have mentioned range from 70000 to 90000.

Conditional Formatting Pivot Tables Example 1-9

  • Step 10: Click on the format on the bottom.

Conditional Formatting Pivot Tables Example 1-10

  • Step 11: Select the type of formatting you want; for example, I have selected color (red color) from the Fill section.

Conditional Formatting Pivot Tables Example 1-11

  • 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.

Conditional Formatting Pivot Tables Example 1-12

Example

You can download this Conditional Formatting in Pivot Table Excel template here – Conditional Formatting in Pivot Table Excel template

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

Example 2

First, let us insert a pivot table in this data.

  1. Step 1: Select the data and in the insert Tab, click on Pivot Tables.

Example 2-1

  • Step 2: Insert the pivot table in a new worksheet by clicking ok.

Example 2-2

  • Step 3: And drag down Product in Row label and months in the value fields.

Example 2-3

  • The pivot table looks like this,

Example 2-4

  • 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.

Example 2-5

  • Step 5: In the first option, apply a rule to select the third option.

Example 2-6

  • Step 6: In the select rule type, select the third option of “Format only top and bottom-ranked values.”

Example 2-7

  • Step 7: In the edit rule description, enter 1 in the input box, and from the drop-down menu, select “each Column Group.”

Example 2-8

  • Step 8: Click on the format.

Example 2-9

  • Step 9: In the fill section, select “Green Colour” this time. Click on Ok.

Example 2-10

  • Step 10: Click on ok to end the formatting rule dialog box.

Example 2-11

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:

  1. Format Cells Based on their values.
  2. Format cells that only contain specific data
  3. Format only top and bottom ranked values.
  4. Format only values that are above or below average.
  5. Use a formula to determine which cells to format.

Things to Remember

  1. Select rules as per requirement.
  2. When the conditional formatting is applied to a block of cells, then formatting is applied to only those specific cells.
  3. Any change in data may represent the wrong illustration in conditional formatting in pivot tables.

Recommended Articles

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 a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *