Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Like we use conditional formatting in any normal data range or table we can also use conditional formatting in the pivot tables by using the conditional formatting option from the home tab, the important thing to remember is that once the data in the source change the formatting in the cells of the pivot tables also changes.
Pivot Table Conditional Formatting (Table of Contents)
Conditional Formatting Pivot Table in Excel
Conditional formatting is a tool which allows us to apply or change formats to a cell or range of cells. Conditional formatting can be done on Pivot tables also. The formatting depends on the condition or the value or the change in the formula.
When we use conditional formatting in pivot it works as dynamic formatting. Whenever we change the values or filter the data, the conditional formatting automatically gets updated with the respective change.
Let us learn Conditional Formatting with the help of examples.
How to Apply Conditional Formatting in the Pivot Table?
Below are the examples of applying Conditional Formatting in the Pivot Table.
We have a set of Data in which three sales person, Alpha, Beta & Gamma have done sales from 22nd of Dec to 25th of Dec.
To apply the pivot table in this data,
- Select the Data and in the Insert Tab, click on Pivot Tables.
A Dialog box appears.
- Insert the pivot table in a new worksheet by clicking ok. Currently, a pivot is blank and we need to bring in the values.
- Drag Down Date in Rows Label, Name in Column and Sales in Values.
- The pivot table looks like this,
- 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.
- Click on New rules and another dialog box pops up.
- 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.
- In the cell, the value section inserts any value you desire, in this case, I have mentioned range from 70000 to 90000.
- Click on the format on the bottom.
- Select the type of formatting you want, for example, I have selected color (red color) from Fill section.
- 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.
The above example is an example of dynamic formatting means if we change the values the formatting changes.
- Change the values for the dates of 22nd & 25th Dec so that total does not come in the range of 70000-90000.
- The value in yellow color are recently changed and the effect on the pivot table formatting is
As the values changed the formatting changed too.
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
First, let us insert a pivot table in this data.
- Select the data and in the insert Tab, click on Pivot Tables.
- Insert the pivot table in a new worksheet by clicking ok.
- And drag down Product in Row label and months in the value fields.
- The pivot table looks like this,
- 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.
- In the first option apply a rule to select the third option.
- In the select rule type select the third option of “Format only top and bottom ranked values”.
- In the edit rule description enter 1 in the input box and from the drop-down menu select “each Column Group”.
- Click on format.
In the fill section select “Green Colour” this time. Click on Ok.
Click on ok to end the formatting rule dialog box.
The above formatting compared the value in the column if we want the formatting in the rows then we can select each row group.
Explanation of Conditional Formatting in Pivot Table
We know that a pivot table is a very important tool to analyze and summarise a large chunk of data. Conditional formatting makes it more appealing to the users. Like for example in a company performance sheet if we have data for various employees regarding their work performance and the data is in a pivot table.
Instead of letting the user go ahead and use a filter to check the top and bottom performer we can use conditional formatting in the same pivot table to highlight the highest and lowest values which will therein show the highest and lowest performer
Rules of conditional formatting in the pivot table?
In the home Tab under the styles section, there is a button for Conditional Formatting. We can insert new rule as per our requirements.
There are various rules for conditional formatting. They are as follows:
- Format Cells Based on their values
- Format cells that only contain a specific data
- Format only top and bottom ranked values
- Format only values that are above or below average
- Use a formula to determine which cells to format.
Things to Remember About Excel Conditional Formatting in Pivot Table
- Select rules as per requirement.
- When the conditional formatting is applied to a block of cells then formatting is applied to only those specific cells.
- Any change in Data may represent the wrong illustration in conditional formatting in pivot tables.
You can download this Conditional Formatting in Pivot Table Excel template here – Conditional Formatting in Pivot Table Excel Template
This has been a guide to Conditional Formatting in Pivot Table. Here we discuss how to Apply Conditional Formatting to Excel Pivot Table along with practical examples and downloadable excel template. You may learn more about excel from the following articles –