Similar to Excel Table, the Pivot table is an effective tool to organize and analyze the data. Conditional formatting of a pivot table enhances this effectiveness ten times. In this article, we use multiple Excel Features as well as multiple functions such as IF, OR, and AND to conditional format the Pivot Table based on any cell in any column.
Suppose, we have a Pivot Table dataset containing Products with their sold Quantity, Unit Price, and Total Price.
Dataset for Download
8 Easy Ways to Conditional Formatting a Pivot Table Based on Another Column
We consider two approaches; based on a cell existing in any column and based on entirely a column to apply conditional formatting to a Pivot Table. Excel’s conditional formatting in-built features do the job based on Cell formatting. But we need to insert formulas to conditional format a Pivot Table based on an entire column.
Method 1: Based on Single Cell
From the Pivot Table, we want the top 5 Quantity values of the Products. We’ll use a single selected cell to apply conditional formatting to the entire Quantity column of the Pivot Table.
Step 1: Select any single cell (i.e., C4). Then Go to Home Tab > Select Conditional Formatting (in Styles section) > Choose New Rule.
Step 2: New Formatting Rule window opens up. In the window, select the 3rd option in the Apply Rule to and Select a Rule Type command box.
Type 5 below the Edit Rule Description box. Keep the option all values within of the selected range for the dialog box.
Step 3: Click on Format. Format Cells window pops up. In the Format Cells window, Choose any of the Fill Color after that Click OK.
Step 4: Again Click OK.
All the steps result in formatting the top 5 cells in the Quantity column.
You can choose Top/Bottom 10 or whatever number you desire to conditionally format the pivot table.
Read more: Conditional Formatting Entire Column Based on Another Column
Method 2: Based on Another Cell
We have the sale Quantity of the Products and we want to compare among the products to the most sold Product. In this case, Milk is the most sold Product in the Pivot Table and we want to compare other products using the data bar.
Step 1: Repeat Step 1 from Method 1. The New Formatting Rule window sneaks up. In the New Formatting Rule window, Select the 3rd and 1st options from Apply Rule to and Select a Rule Type command box respectively.
In Edit the Rule Description dialog box,
Choose Data Bar as Format Style.
Choose Number (Minimum and Maximum) as Type. Insert 0 as Minimum value and Cell Reference C9 as Maximum value (i.e.,149).
Use a favorable color as Fill then Click OK.
The executions come down to a result similar to the image below.
To compare the entries, you can select other options available to the Format Style such as 2-Colors Scale, 3-Colors Scale, and Icon Sets.
Read more: How to Compare Two Columns Using Conditional Formatting in Excel
Method 3: Based on Grand Total
Now we want to represent the Quantity by the percentage of the highest value available. In this case, the highest value in the Quantity column is 149. We set Icon Sets to show 3-Color Icons of the representation of 40, 20, and below 20 percentages.
Step 1: Follow Step 1 in Method 1. In a moment, the New Formatting Rule window opens up. Inside the New Formatting Rule window, Choose the 3rd and 1st options from Apply Rule to and Select a Rule Type command box respectively.
In Edit the Rule Description dialog box,
Select Icon Sets as Format Style.
In Display each icon according to these rules dialog box,
Insert 40 and 20 as percent Type; to represent Green and Yellow Icon Set respectively.
It’ll automatically select the Red Icon Set for values less than 20 percent.
Then click OK.
The Icon Sets appear in the Pivot Table representing per percentage values they are set to refer to.
Read more: How to Compare Two Columns in Excel For Finding Differences
Method 4: Based on Blank Cells
We have entries in all the cells. Let’s say we encounter zero sales for some products. We can conditionally format the entire Pivot Table depending on the blanks.
Step 1: Repeat Step 1 of Method 1 then the New Formatting Rule window will open.
Here in the New Formatting Rule window, Select the 3rd and 2nd options from Apply Rule to and Select a Rule Type command box respectively.
Inside Edit the Rule Description dialog box,
Choose Blanks from Format only cells with drop-down selection box.
Click on Format.
Step 2: The Format Cells command window appears. Follow Step 3 of Method 1 to come up with a result like the following picture. Click OK.
All the execution of Steps formats the Blanks in the Pivot Table similar to the picture below.
Similar Readings:
- Excel Conditional Formatting on Multiple Columns
- Conditional Formatting for Blank Cells in Excel (2 Methods)
- Conditional Formatting with Formula for Multiple Conditions in Excel
- How to Highlight Highest Value in Excel (3 Quick Ways)
Method 5: Using a Condition (Greater Than a Value)
If we seek to conditional format the entire Pivot Table’s rows based on values in another column, we can achieve it by multiple means. Using a condition is one of the easiest of those means. In this case, we use greater than (>) to impose a condition that we format the rows which have sales Quantity greater than 50 (>50).
Step 1: Select the Entire Pivot Table (i.e., B4:E17). Afterward, Go to Home Tab > Select Conditional Formatting (in the Styles section) > Choose New Rule (from the options).
Step 2: Clicking OK brings up the New Formatting Rule window. In the window, select Use a formula to determine which cells to format.
Inside the Edit the Rule Description command box, Paste the following formula.
=$C4>50In the formula, the Hashtag ($) before the Cell Reference (C4) ensures locking the Column Quantity (i.e., C column) in order to conditional format the whole Table.
Click on Format.
Step 3: The Format Cells window appears. From the Format Cells window, Choose a favorable Fill Color then Click OK.
Step 4: After returning to the Formatting Rule window, again click OK.
Following all the steps brings up a formatted Pivot Table based on its Quantity Column.
Read more: How to Do Conditional Formatting for Multiple Conditions
Method 6: Using IF Function
Using the IF function, we can achieve the same outcomes as Method 5 for a similar condition.
Step 1: Do what we did in Steps 1 to 3 of Method 5. Just Replace the Formula in Step 2 with the following Formula.
=IF($C4>50, TRUE, FALSE)In the formula,
The IF function offers a logical_test ($C4>50) to be True for the cells in Column C to conditional format the entire Pivot Table.
Step 2: Click OK, it conditional formats the entire Pivot Table based on values greater than 50 in the Quantity column.
Method 7: Using AND Function
To make the condition bidimensional, we can use conditions as logical_test to conditionally format the Pivot Table based on the same column Quantity (i.e., Column C). In this case, we want quantities greater than 50 (>50) but less than 120(<120).
Step 1: Repeat Steps 1 to 3 of Method 5. Then Replace the Formula in Step 2 with the following Formula.
=AND($C4>50,$C4<120)In the formula,
The AND function offers two logical arguments as logical_tests ($C4>50,$C4<120) are considered to be True for Column C to conditional format the entire Pivot Table.
Step 2: The formula formats all the rows that satisfy both of the arguments similar to the image below.
Method 8: Using OR Function
The OR function satisfies either of the conditions in it. We can use the OR function for the same conditions but declare the upper value equal to 120.
Step 1: Follow Steps 1 to 3 in Method 5. In this case, just Replace the Formula in Step 2 with the following Formula.
=OR($C4>50,$C4=120)The OR function satisfies either $C4>50 or $C4=120 for Column C to format the whole Pivot Table.
Step 2: All the steps result in the image depicted as the following image.
Conclusion
In this article, we use Excel’s Conditional Formatting feature to conditionally format Pivot Table. We take two approaches to conditionally format the Pivot Table; one is cell-based and the other one’s entire column-based. For cell-based formatting, we use various options offered in the Conditional Formatting feature. The other approach requires functions such as IF, AND, and OR as well as simple Condition Signs (Greater Than or Less Than) to conditionally format the entire Pivot Table. Hope these methods do the work you are looking for. Comment, if you need further clarifications or have something to add.
