## What Is Conditional Formatting In Pivot Table?

Conditional formatting in pivot table is an Excel option to highlight cells of a pivot table in a specific format based on a set of rules. Users can apply the

Conditional Formattingfeature to draw viewers’ focus to sales figures in financial summary reports that satisfy the specified criteria.For example, the table below shows the monthly order quantities at three branch offices of a firm.

And the requirement is to summarize the above data in an Excel pivot table. And then conditionally format the three branch offices’ order data in the pivot table to understand the order quantity trend.

Then, we can use the **conditional formatting in pivot table based on row value **technique to achieve the required output.

First, we select the given dataset and use the** **option in the **Insert **tab to create a pivot table in cell A18 of the same sheet as the source dataset.

Next, we choose the cell range B20:C23, containing the branch offices’ order quantity data, and select the **New Rule** option under the Excel **Conditional Formatting** feature in the **Home** tab.

And then, using the second rule type, we set the **3-Color Scale** setting to format the least to highest values in the chosen cell range.

Thus, in the above **conditional formatting in pivot table based on row value **example, the minimum, mid, and maximum values are formatted in the specified colors. And hence, it makes it easier to visually understand in which month the order quantity at each branch office was the least, intermediate, and maximum.

##### Table of contents

###### Key Takeaways

- The conditional formatting in pivot table enables one to format pivot table cells based on the specified criteria.
- Users can conditionally format a pivot table to highlight data and trends for visual understanding and analyzing summary reports.
- We can use the
**PivotTable**option in the**Insert**tab to create a pivot table. And the**Conditional Formatting**feature in the**Home**tab helps apply conditional formatting in pivot tables. - Excel offers six conditional formatting rules, with the first five offering inbuilt conditions. And the sixth one enables us to create customized formatting conditions using Excel functions and formulas.

### Easy Steps To Apply Conditional Formatting In The Pivot Table

The steps to apply and **keep conditional formatting in pivot table** are as follows:

- Select the source data cell range and choose the
**Insert**tab – select the**PivotTable**option to open the**Create PivotTable**window. [Alternatively, pressing**Alt**+**N**+**V**will open the**Create PivotTable**window.]

- Ensure the chosen cell range is correct in the first field in the
**Create PivotTable**window. And then, set the target cell settings, where we must display the pivot table.

- Clicking
**OK**in the**Create PivotTable**window will show the space to create the pivot table in the worksheet. And we will see the**PivotTable Fields**pane to set the rows and columns in the table.

- Drag the column headings or fields to the required areas in the
**PivotTable Fields**pane to achieve the required pivot table.

- Update the field names according to the requirements.

- Choose the cell range, where we must apply and
**keep conditional formatting in pivot table**, and select the**Home**tab – choose the**Conditional Formatting**feature. And then, choose the**New Rule**option under the**Conditional Formatting**feature.

[Otherwise, use the keyboard shortcut **Alt **+ **H **+ **L **+ **N** to access the **New Formatting Rule** window.

Alternatively, use the shortcut keys,** Alt **+ **O **+ **D**, to open the **Conditional Formatting Rules Manager** window. And then choose the **New Rule** option to open the **New Formatting Rule** window.]

- The
**New Formatting Rule**window will open, where the chosen cell range will appear at the top.

Though the first option is the default in the first section, choose the third option. Otherwise, when we add or refresh new data to the existing pivot table, the specified conditional formatting is not applied to the new data.

And then, we can select the applicable rule type and description in the second and third sections to apply the required conditional formatting rule to the chosen cells.

- Select the
**Format**option to open the Excel**Format Cells**window highlighted above. - Use the options in the
**Format Cells**window to set the desired format, in which we must highlight the required data in the pivot table.

- Click
**OK**in the**Format Cells**window to exit and view the**New Formatting Rule**window.

- Click
**OK**in the**New Formatting Rule**window to view the pivot table with the conditional format applied in the chosen cell range.

Likewise, we can apply **multiple conditional formatting **for the same or different cell ranges using the above-explained method.

### Examples

The following illustrations will help you understand the practical ways to use the **Conditional Formatting **feature in pivot tables.

**Example #1**

We shall see an example of **multiple conditional formatting in pivot table**.

The table below lists currency symbols and their current prices and price changes in the US stock market.

The requirement is to summarize the above data in a pivot table. Next, conditionally format column D values to visually analyze the percentage changes using icons in the pivot table. And then, highlight the top three currency symbols’ prices in column B in the pivot table.

Then, the steps are as follows:

**Step 1: **Choose the whole dataset and select **Insert **– **PivotTable** to open the **Create PivotTable **window.

Update the target cell reference in the **Location **field, where we require the pivot table.

And click **OK** to view the space to create the pivot table in the worksheet and the **PivotTable Fields** pane.

**Step 2: **Drag the fields to the required areas in the **PivotTable Fields** pane to achieve the required pivot table.

And update the column names to suit our requirements.

**Step 3: **Select the cell range D15:D22 and choose **Home **– **Conditional Formatting **– **New Rule** to open the **New Formatting Rule **window.

And set the following settings in the **New Formatting Rule** window.

The first section shows the chosen cell range, where we must select the last option under it.

Next, choose the first rule type in the second section. And in the third section, set **Format Style **as **Icon Sets** in Excel window and choose the required **Icon Style **using the corresponding drop-down buttons.

And then, update the fields under **Value **and **Type** to apply the rules to show the icons for values in the specified corresponding ranges.

Finally, click **OK** in the **New Formatting Rule** window to view the below output.

**Step 4: **Choose the cell range B15:B22 and select **Home **– **Conditional Formatting **– **New Rule** to access the **New Formatting Rule** window.

And set the following settings in the **New Formatting Rule** window.

The first section shows the chosen cell range, where we must choose the last option under it.

Next, choose the third rule type in the second section. And in the third section, set the required rule to display the top three values in the chosen cell range.

And then click **Format**, which will open the **Format Cells **window.

**Step 5: **Select the **Fill **tab to choose the required cell background color to highlight the required cells.

And click **OK**.

Finally, click **OK** in the **New Formatting Rule** window to achieve the below pivot table with multiple conditional formatting.

#### Example #2

Let us see an example of **conditional formatting in pivot table using formula**.

The table below lists students, their project points and overall scores in different subjects.

And the requirement is to summarize the above data in a pivot table. And then, highlight the students’ names in the pivot table who scored below 10 points in their Mathematics project and an overall Mathematics subject score of below 45.

Then, **conditional formatting using formula **in the required cell range can fetch the required output.

**Step 1: **Choose a cell in the source dataset and select **Insert **– **PivotTable** to open the **Create PivotTable** window.

And update the target cell reference in the **Location** field, where we wish to create the pivot table.

And click **OK** to view the location to create the pivot table in the sheet and the **PivotTable Fields** pane.

**Step 2: **Drag the fields to the required areas in the **PivotTable Fields** window to achieve the required pivot table.

And update the column and field names in the pivot table according to our requirements.

**Step 3: **Filter the subject **Mathematics** in the pivot table.

**Step 4: **Select the cell range A29:A33 and choose **Home **– **Conditional Formatting **– **New Rule** to open the **New Formatting Rule** window.

And select the last rule type to enter the following conditional formatting rule to highlight the required cells.

*=AND($B29<10,$C29<45)*

And click **Format** to open the **Format Cells** window.

**Step 5: **Select the **Fill** tab. And choose the required cell background color from the given options.

And click **OK**.

Finally, click **OK** in the **New Formatting Rule** window to obtain the below output.

The Excel **AND** function checks two conditions. The first condition checks each cell in the column range B29:B33, whether the **Mathematics** project points are below 10.

And the second condition checks each cell in the column range C29:C33, whether the overall score in **Mathematics** is below 45.

And as both conditions hold in rows 31 and 32, the corresponding students’ names in column A get highlighted in the specified format.

### Rules For Conditional Formatting In Pivot Table

The following image shows the conditional formatting rules for the pivot table in the **New Formatting Rule** window. And we can access the window by following the path **Home **– **Conditional Formatting **– **New Rule**.

Excel offers six conditional formatting rules for pivot tables. And using these rules, we can format cells based on values and those which contain specific data. We can also format the top or bottom ranking values, numbers above or below the average, and format unique or duplicate data.

Furthermore, we can use customized formatting conditions or formulas to format cells based on specific criteria.

**Important Things To Note**

- When conditional formatting in pivot table, choose the third option in the first section in the
**New Formatting Rule**window. Otherwise, the conditional formatting will not apply to any newly-added data in the pivot table. - Choose the appropriate rule type in the
**New Formatting Rule**window to apply the correct conditional formatting rule description in the pivot table. - If you update the source data, right-click the pivot table and choose
**Refresh**from the context menu to update the pivot table and the applied conditional formatting.

### Frequently Asked Questions (FAQs**)**

**1. How do I conditional format subtotals in Excel PivotTable?**

You can conditional format subtotals in Excel **PivotTable** by choosing the cell range containing the subtotals. And then, use the **Conditional Formatting **feature to apply the required conditional format to the subtotals in the pivot table.

For example, the table below contains item codes, colors, and units of colors ordered and order date data.

And the requirement is to summarize the above data in a pivot table and conditionally format the subtotals in the pivot table.

Then, the steps are as follows:**1: **Choose a cell in the dataset and select **Insert **– **PivotTable**.

The **Create PivotTable **window will open, where we must update the target cell reference in the **Location **field to display the pivot table. And click **OK**.**2: **Drag the fields in the respective areas in the **PivotTable Fields** pane to achieve the following pivot table.**3: **Choose the cell range B17:E17, containing subtotals for the first item code, and select **Home **– **Conditional Formatting** – **New Rule**.

The **New Formatting Rule **window will open, where we must apply the following settings.

And clicking **OK** will give us the following pivot table with the subtotals conditionally formatted.

**2. How to copy conditional formatting in pivot table?**

You can copy conditional formatting using the **Format Painter** option in the **Home **tab.

Select the conditionally formatted cell range in the pivot table and choose **Home **– **Format Painter**. And then, drag the cursor over the cell range in the pivot table where you need to apply the copied conditional formatting.

**3. Why does conditional formatting disappear in pivot tables?**

Conditional formatting disappears in pivot tables, perhaps because the rows/column fields got modified, such as a column of data got removed and replaced again.

### Download Template

This article must be helpful to understand the **Conditional Formatting In Pivot Table**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to What Is Conditional Formatting In Pivot Table. We learn the steps and rules to apply it, with examples and points to remember. You can learn more from the following articles –

## Leave a Reply