Conditional Formatting In Pivot Table In Google Sheets

What Is Conditional Formatting In Pivot Table In Google Sheets?

Conditional Formatting in Pivot Table in Google Sheets highlights the required data in the generated Pivot Table and is a very useful feature when projecting reports or important data.

The Conditional Formatting in Google Sheets is a feature that helps users highlight desired cells or rows based on the rule set or the entered formula.
The Pivot table is a data table generated using an existing dataset with automatic data filters for easy display of desired data.

For example, we will generate a Pivot Table for the given data, as shown below.

Let us apply Conditional formatting by defining some rules, here, if the value is less than 2, then we will get the output shown below.

Conditional formatting In Pivot Table - Examples

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Conditional Formatting in Pivot Table in Google Sheets_Written&Edited_Sheeba

What Is Conditional Formatting In Pivot Table In Google Sheets?

Conditional Formatting in Pivot Table in Google Sheets helps us set single or multiple rules to project the desired data in a generated Pivot table.
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 have another alternate way to apply the “Conditional formatting” as follows:

First, select the dataset 🡪 right-click on the selected dataset 🡪 scroll to the end of the list and click the “View more cell actions” right arrow 🡪 select the “Conditional formatting” option, as shown below.

Easy Steps To Apply Conditional Formatting In Pivot Table In Google Sheets

The steps to understand How to Format Pivot Tables in Google Sheets are as follows:

First, we must ensure to have the raw data and its generated Pivot table.
Next, select the Pivot table data with the headings, but excluding the totals, select the “Format” tab and click the “Conditional formatting” option, as shown below.

Apply Conditional Formatting In Pivot Table In Google Sheets

The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.

Conditional format rules - Add another rule

We will see “Single Color” and “Color scale” tabs. Here, click the “Single color” tab to view multiple options to choose for the required conditions or rules, as shown below.

Finally, once we choose the required conditions, click the “Done” option. Then, we will have the Google Sheets Pivot Table Conditional Formatting output.

Examples

We will consider a couple of examples to understand Conditional Formatting Pivot Tables in Google Sheets.

Example #1

The data below shows the respective sales of three employees for the three consecutive days.

The steps to apply Conditional Formatting are as follows:

Step 1: First, let us generate a Pivot table. So, choose the cells A1:D10 🡪 select the “Insert” tab 🡪 click the “Pivot table” option, as shown below.

Step 2: A “Create pivot table” window appears. Here, choose the “New sheet” option and click the “Create” button, as shown below.

Step 3: The Pivot Table gets generated in the new worksheet without any data, as shown below. However, the “Pivot table editor” pane opens at the right side of the window.

Step 4: Let us add the data as follows:

Click on the “Rows” options “Add” button and select the “Date” values.
Click on the “Columns” options “Add” button, select the “Name” values and uncheck the “Show totals” option.
Click on the “Values” options “Add” button and select the “Sales” values, as shown below.

The added values are shown in the below images.

Example 1 - Step 4 - Add Rows and columns - Date

Example 1 - Step 4 - Add Rows and columns - Sales

The final Pivot table will be as shown below.

Conditional Formatting Example 1 - Final Pivot Table

Step 5: Let us now apply “Conditional formatting”, as follows:

Choose the data range A2:D6, select the “Format” tab 🡪 click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Example 1 - Step 5 -Conditional format rule

Step 6: Now, select the “Single color” tab and under the “Format rules” section,

First, click the “format if…” drop-down, select the “greater than” option and enter 75000 in the “Value” field that appears as soon as we select one of the options from the drop-down.
Next, in the “Formatting style” options select the color “Yellow” from the “Fill color” option.
Finally, click the “Done” button, as shown below.

Conditional Formatting Example 1 - Step 6 - Formatting rule and style

The final output is shown below, where the totals greater than 75000 are highlighted in yellow color.

Conditional Formatting Example 1 - final output

Example #2

We will consider the results of five students and apply multiple Conditional Formatting rules in the Pivot Table, as shown in the below data.

You can download this Conditional Formatting in Pivot Table Google Sheets template here – Conditional Formatting in Pivot Table Google Sheets template.

Conditional Formatting rules in the Pivot Table -Example 2

The steps to apply multiple Conditional formatting rules are as follows:

Step 1: First, let us generate a Pivot table.

Choose the cells A1:B6 🡪 select the “Insert” tab 🡪 click the “Pivot table” option.
A “Create pivot table” window appears. Here, choose the “New sheet” option and click the “Create” button, as shown below.

Step 2: The Pivot Table gets generated in the new worksheet without any data, as shown below. However, the “Pivot table editor” pane opens at the right side of the window.

Step 3: Let us add the data as follows:

Click on the “Rows” options “Add” button and select the “Names” values.
Click on the “Values” options “Add” button and select the “Percentage” values, as shown below.

Example 2 - Step 3 - Pivot table editor - Add Data

The final Pivot table will be as shown below.

Step 4: Let us now apply “Conditional formatting” as follows:

Choose the data range A1:B6, select the “Format” tab 🡪 click the “Conditional formatting” option. When the “Conditional format rule” pane opens on the right, click the “Add another rule” option, as shown below.

Example 2 - Step 1 - Conditional formatting

Step 5: Now, select the “Single color” tab and under the “Format rules” section,

First, click the “format if…” drop-down, select the “is between” option and enter 65% and 70% in the fields that appears as soon as we select one of the options from the drop-down.
Next, in the “Formatting style” options select the color “Yellow” from the “Fill color” option.
Finally, click the “Done” button, as shown below.

Conditional formatting - Example 2 - Step 5 - Rule 1

We get the output shown below.

Conditional formatting - Example 2 - Step 5 - Rule 1 output

Step 6: Let us set another rule. Therefore, click the “Add another rule” in the “Conditional format rules” pane just below the first set rule. Then, select the “Single color” tab and under the “Format rules” section,

First, click the “format if…” drop-down, select the “is between” option and enter 75% and 80% in the fields that appears as soon as we select one of the options from the drop-down.
Next, in the “Formatting style” options select the color “magenta” from the “Fill color” option.
Finally, click the “Done” button, as shown below.

The final output is shown below, where the values in-between 65% and 70% are marked in yellow, in-between 75% and 80% are marked in magenta, as shown below.

Conditional Formatting Example 2 - final output

Rules For Conditional Formatting In Pivot Table

There are various rules for Conditional Formatting as follows:

Format Cells based on their values.
Format cells that only contain specific data.
Format only the high and low ranked values.
Format only values that are greater than or less than the average.
Use a formula to determine which cells to format.

Important Things To Note

Ensure to click the “Done” option after every rule set, otherwise, all the changes or atleast a few changes will not be saved.
Even though we select a cell range and apply Conditional Formatting, the formatting gets applied to the specific cells that fulfil the criteria.

Frequently Asked Questions (FAQs)

1. Can we generate a Pivot Table in the existing sheet?

• Yes, we can generate a Pivot Table in the existing Sheet itself. We must follow the steps shown below.
• Choose the dataset or a cell range 🡪 select the “Insert” tab 🡪 click the “Pivot table” option.
• A “Create pivot table” window appears. Choose the “Existing sheet” option,
• Once we choose the “Existing sheet” option, a field to enter the destination location or cell to generate the Pivot Table appears. Select a cell close to the dataset and click the “Create” button, as shown below.

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

We can create a Pivot Table using the following path,
First, choose the dataset 🡪 select the “Insert” tab 🡪 click the “Pivot table” option, as shown below.

3. Why is the Formatting Pivot Tables in Google Sheets not working?

The Conditional Formatting in Pivot Table may not work for the following reasons, namely:
a. The dataset of the generated Pivot Table has been modified or updated and is not refreshed to display the updated data.
b. We have not set the conditional formatting rules or the set rules are deleted.
c. Also, since Google Sheets are online, it might not work due to network issues.

Download Template

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Conditional Formatting in Pivot Table in Google Sheets_Written&Edited_Sheeba

Conditional Formatting In Pivot Table In Google Sheets

What Is Conditional Formatting In Pivot Table In Google Sheets?

Table of contents

Key Takeaways