Pivot Table Conditional Formatting Based on Another Column

Suppose we have a Pivot Table dataset containing Products with their sold Quantity, Unit Price, and Total Price. We’ll format the pivot table.

Pivot Table-Pivot Table Conditional Formatting Based on Another Column


Pivot Table Conditional Formatting Based on Another Column: 8 Easy Ways

Method 1 – Based on a Single Cell

We want the top 5 Quantity values.

Steps:

  • Select any single cell in the column you want to format by (i.e., C4).
  • Go to the Home tab and select Conditional Formatting (in the Styles section).
  • Choose New Rule.

Single cell-Pivot Table Conditional Formatting Based on Another Column

  • A New Formatting Rule window opens up. Select the third 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 the last box in that row.

New formatting rule

  • Click on Format. The Format Cells window pops up.
  • In the Format Cells window, choose any of the Fill colors.
  • Click OK.

Single cell fill color

  • Click OK.

Single cell edit formatting rule

  • This formats the top 5 cells in the Quantity column.

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


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.

Steps:

  • Insert a New Formatting Rule.
  • 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 the Edit the Rule Description dialog box, choose Data Bar as Format Style.
  • Choose Number (both for Minimum and Maximum) for Type.
  • Insert 0 as the Minimum value and Cell Reference C9 as the Maximum value (you can also input a formula to calculate the MAX of the range).
  • Pick a Fill color then click OK.

another cell-Pivot Table Conditional Formatting Based on Another Column

  • Here’s what you’ll get as the result, with partial bars to represent the sales against the maximum value.

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


Method 3 – Based on the Grand Total

Let’s format the Quantity by their values. In this case, the highest value in the Quantity column is 149. We set Icon Sets to show 3-Color Icons for the representation of 40, 20, and below 20.

Steps:

  • Create a New Formatting Rule.
  • 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 the 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 in Percent Type for Green and Yellow icons, respectively.
  • Excel will automatically select the Red Icon Set for values less than 20 percent.
  • 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


Method 4 – Based on Blank Cells

Let’s say we encounter zero sales for some products. We can conditionally format the entire Pivot Table depending on the blanks.

Steps:

  • Insert a New Formatting Rule.
  • 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 the Edit the Rule Description dialog box, choose Blanks from Format only cells with a drop-down selection box.
  • Click on Format.

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

  • The Format Cells command window appears. Choose a Fill color.
  • Click OK.

New formatting rule

  • All blank cells will be formatted.

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


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

We’ll format the rows that have sales Quantity greater than 50 (>50).

Steps:

  • Select the entire Pivot Table (i.e., B4:E17).
  • Go to Conditional Formatting and insert a New Rule.

Greater than-Pivot Table Conditional Formatting Based on Another Column

  • This brings up the New Formatting Rule 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

The Hashtag ($) before the Cell Reference (C4) ensures locking the Column Quantity (i.e., C column) to format the whole table based on the column.

  • Click on Format.

New formatting rule

  • From the Format Cells window, choose a Fill color, then click OK.

Fill color

  • After returning to the Formatting Rule window, click OK.

Edit formatting rule

  • Here’s the formatted table.

Greater than final result-Pivot Table Conditional Formatting Based on Another Column


Method 6 – Using the IF Function

Steps:

  • Follow the previous method, but replace the conditional formatting formula with the following:
=IF($C4>50, TRUE, FALSE)

The IF function offers a logical_test ($C4>50) to conditionally format the entire Pivot Table based on the value in the row’s column C.

IF Function-Pivot Table Conditional Formatting Based on Another Column

  • Here’s the result.

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


Method 7 – Using the AND Function

Let’s format quantities greater than 50 (>50) but lower than 120(<120).

Steps:

  • Repeat Method 5, but replace the formula inside the Conditional Formatting New Rule with the following:
=AND($C4>50,$C4<120)

The AND function offers two logical arguments as logical_tests ($C4>50,$C4<120). If both are true, it will output TRUE and Excel will format the cells.

AND function-Pivot Table Conditional Formatting Based on Another Column

  • 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 the OR Function

If you need only one of the conditions to be true, use the OR function.

 Steps:

  • Follow Method 5 and insert this formula for the formatting rule:
=OR($C4>50,$C4=120)

The OR function checks whether $C4>50 or $C4=120 for Column C to format the whole Pivot Table.

OR Function-Pivot Table Conditional Formatting Based on Another Column

  • This results in the following image.

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


Dataset for Download


Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

2 Comments
  1. Hi, for Method 6, when you refresh the pivot table, it will remove the formatting for all the columns except the far left. How do you prevent this?

    • Hello J,

      Thanks for your comment and for noticing the fact! You are right about losing conditional formatting when the pivot table is refreshed.

      When a pivot table is refreshed, custom formatting will reset because the data structure may change; for example, new rows or columns might be added. Excel may not reapply the formatting rules applied to specific cells if the layout of the pivot table changes. So, in this case, you need to manually reapply the conditional formatting rules each time the pivot table is refreshed.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo