Conditional Formatting In Pivot Table

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Conditional Formatting In Pivot Table?

Conditional Formatting is a feature in Excel that helps users to highlight desired cells or rows based on the rule we set or the formula we input. Conditional Formatting in Pivot Table is the same, except it highlights the required data in the generated Pivot Table.

Table of contents
  • Conditional Formatting in Pivot Table helps us highlight the desired data in the Pivot table that is generated. It’s important to understand these two features individually.
    • Conditional Formatting highlights specific words, or rows as per the set rule.
    • Pivot Table is a new table generated for a dataset or a cell range that has automatic filters for easy display of data.
  • Ensure to refresh the Pivot Table when data modification is done because the Conditional Formatting may not work as it is set for a fixed dataset.
  • We can also create Pivot Table Slicers linked to the existing Pivot Table.

Easy Steps To Apply Conditional Formatting In The Pivot Table

The steps to apply Conditional Formatting in the pivot table are,

  1. First, we must select the data. Then, in the “Insert” Tab, click on “PivotTable”.


    Conditional Formatting Pivot Tables Example 1-1

  2. As a result, the “Create PivotTable” dialog box appears, as shown below.


    Conditional Formatting Pivot Tables Example 1-2

  3. Next, we must insert the pivot table in a new worksheet by clicking “OK”. Currently, a pivot table is blank. Next, we need to bring in the values.


    Conditional Formatting Pivot Tables Example 1-3

  4. Then, drag down the “Date” in the “Rows” Label, “Name” in the “Column,” and “Sales” in “Values.”


    Conditional Formatting Pivot Tables Example 1-4

  5. As a result, the pivot table will look like the one below.


    Conditional Formatting Pivot Tables Example 1-5

  6. To apply Conditional Formatting in the pivot table, first, we must select the column to format. In this example, select “Grand Total Column”. Then, in the “Home” Tab in the “Styles” section, click on the “Conditional Formatting” option drop-down to open the available options, then click the “New Rule” option, as shown below.

    Conditional Formatting Pivot Tables Example 1-6

  7. As a result, the “New Formatting Rule” dialog box will pop up.


    Conditional Formatting Pivot Tables Example 1-7

  8. There are a variety of rules to be applied in the pivot table. For example, we will select “Format only cells that contain”, and we can see another dialog box in the same window.


    Conditional Formatting Pivot Tables Example 1-8

  9. Next, we must insert any value in the “Cell Value” section. In this case, we have mentioned a range from 70000 to 90000.


    Conditional Formatting Pivot Tables Example 1-9

  10. Then, click the “Format” option at the bottom.


    Conditional Formatting Pivot Tables Example 1-10

  11. Next, we must select the type of formatting we want. For example, we have chosen a color (red color) from the “Fill” section.


    Conditional Formatting Pivot Tables Example 1-11

  12. After that, click “OK”, and once again “OK” to close the “Formatting Rule” dialog box.


    Conditional Formatting Pivot Tables Example 1-12

    And the pivot table is formatted in red with the values between 70000-90000, as shown above.

Example

We will consider an example for Conditional Formatting in Pivot Table.

We have data for a newly launched canteen, and we have the records for the number of products sold in each of the five months – Jan, Feb, March, April, and May.

Example 2

The steps to insert a pivot table in this data are:

  • Step 1: We must select the data. Then, in the “Insert” Tab, click on “PivotTable”.
Example 2-1
  • Step 2: Then, insert the pivot table in a new worksheet by clicking “OK”.
Example 2-2
  • Step 3: Drag down the “Product” in the “Row” label and “Months” in the “Value” fields.
Example 2-3
  • The pivot table will look as shown below.
Example 2-4
  • Step 4: Our motive is to highlight those cells with the highest number of products sold in each row. For that, we must select any cell in the pivot table. Then, click on “Conditional Formatting” and click “New Rule”.
Example 2-5
  • Step 5: In the first option, apply a rule to select the third option.
Example 2-6
  • Step 6: Now, select the third option of “Format only top and bottom ranked values” in “Rule type”.
Example 2-7
  • Step 7: In “Edit the Rule Description”, we must enter 1 in the input box, and from the drop-down menu, select “each Column Group”, as shown below.
Example 2-8
  • Step 8: Then, we must click on the “Format”.
Example 2-9
  • Step 9: Select “Green Color” in the “Fill” section. And click on “OK”
Example 2-10
  • Step 10: Again, click on “OK” to close 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, 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, 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.

Important Things To Note

  • We must select rules as per requirement.
  • For example, when Conditional Formatting is applied to a block of cells, formatting is applied to only those specific cells.
  • Any change in data may represent the wrong illustration in Conditional Formatting in Pivot Tables.

Frequently Asked Questions

1. How to use the Conditional Formatting option in Excel?

We can use the Conditional Formatting option using the following path,
First, choose the dataset - select the “Home” tab - go to the “Styles” group - click the “Conditional Formatting” option drop-down - select the “New Rule” option, as shown below.

Conditional Formatting - New Rule

2. How to generate or create a Pivot Table in Excel?

We can create a Pivot Table using the following path,
First, choose the dataset - select the “Insert” tab - go to the “Tables” group - click the “PivotTable” option, as shown below.

Insert - Pivot Table

We do have other options as well when we click the “PivotTable” option drop-down, as shown below.

Insert - From Table-Range

• If we select the “From Table/Range” option, then a window pops up where we can enter the dataset cell range.
• If we select the “From External Data Source” option, then we can select the data range from the current or another Excel workbook data.

3. Why is the Conditional Formatting in Pivot Table not working?

The Conditional Formatting in Pivot Table may not work for the following reasons, namely:
• The dataset of the generated Pivot Table has been modified or updated and is not refreshed to display the updated data.
• We have not set the conditional formatting rule w.r.t the right cell range.

Download Template

This article must help understand Conditional Formatting in Pivot Table with its formulas and examples. You can download the template here to use it instantly.