Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)

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.

Pivot Table-Pivot Table Conditional Formatting Based on Another Column

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.

Single cell-Pivot Table Conditional Formatting Based on Another Column

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.

New formatting rule

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.

Single cell fill color

 Step 4: Again Click OK.

Single cell edit formatting rule

All the steps result in formatting the top 5 cells in the Quantity column.

Single cell final result-Pivot Table Conditional Formatting Based on Another 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.

another cell-Pivot Table Conditional Formatting Based on Another Column

The executions come down to a result similar to the image below.

another cell final result-Pivot Table Conditional Formatting Based on Another Column

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.

based on grand total-Pivot Table Conditional Formatting Based on Another Column

The Icon Sets appear in the Pivot Table representing per percentage values they are set to refer to.

based on grand total final result-Pivot Table Conditional Formatting Based on Another Column

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.

based on blank cells-Pivot Table Conditional Formatting Based on Another Column

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.

New formatting rule

All the execution of Steps formats the Blanks in the Pivot Table similar to the picture below.

based on blank cells final result-Pivot Table Conditional Formatting Based on Another Column


Similar Readings:


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).

Greater than-Pivot Table Conditional Formatting Based on Another Column

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>50

In 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.

New formatting rule

Step 3: The Format Cells window appears. From the Format Cells window, Choose a favorable Fill Color then Click OK.

Fill color

Step 4: After returning to the Formatting Rule window, again click OK.

Edit formatting rule

Following all the steps brings up a formatted Pivot Table based on its Quantity Column.

Greater than final result-Pivot Table Conditional Formatting Based on Another 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.

IF Function-Pivot Table Conditional Formatting Based on Another Column

Step 2: Click OK, it conditional formats the entire Pivot Table based on values greater than 50 in the Quantity column.

IF Function final result-Pivot Table Conditional Formatting Based on Another 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.

AND function-Pivot Table Conditional Formatting Based on Another Column

Step 2: The formula formats all the rows that satisfy both of the arguments similar to the image below.

AND function final result-Pivot Table Conditional Formatting Based on Another Column

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.

OR Function-Pivot Table Conditional Formatting Based on Another Column

Step 2: All the steps result in the image depicted as the following image.

OR Function final result-Pivot Table Conditional Formatting Based on Another Column

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.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo