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:

**Â**

`=$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.**

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

`=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 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:

`=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 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.

**<< Go Back to Pivot Table FormattingÂ |Â Pivot Table in ExcelÂ |Â Learn Excel**