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 Formatting feature 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.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
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)
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.
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.
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