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

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