Pivot Table Conditional Formatting Based on Another Column

Similar to the Excel Table, the Pivot table is an effective tool for organizing 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 Conditional Formatting Based on Another Column: 8 Easy Ways

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 conditionally 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 the 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 colors; 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.

Method 2: Based on Another Cell

We have the sale Quantity of the Products and we want to compare them 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 the Minimum value and Cell Reference C9 as the 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.

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.

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, and 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 a 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.

Method 5: Using a Condition (Greater Than a Value)

If we seek to conditionally 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 that 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:

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.

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.

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.

In the formula,

The IF function offers a logical_test ($C4>50) to be True for the cells in Column C to conditionally format the entire Pivot Table.

Step 2: Click OK, it conditionally 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:

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:

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 in the following image.

Dataset for Download

Conclusion

In this article, we use Excel’s Conditional Formatting feature to conditionally format Pivot Table. We take two approaches to conditionally formatting the Pivot Table; one is cell-based and the other’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 clarification or have something to add.