How to Apply Conditional Formatting to a Pivot Table in Your Excel Worksheet

Conditional formatting and pivot table are two highly effective features in Excel. In this post, we will show you how to correctly apply conditional formatting to a pivot table.

When you need to highlight cells based upon certain criteria, you might use the conditional formatting. Besides, you can also use this feature in a pivot table. On the other hand, in a pivot table, the rule of conditional formatting will be a little different. Now, please read on this article and see how to properly combine the two features.

Problem of Using Conditional Formatting in a Pivot Table

In the image below, there is a pivot table with the sales volume of the first half of the year.Example

The rule for the column is “Greater Than 350”. Thus, those cells that satisfy this criterion will be highlighted with a different background color.

And the next time you will add the data in the second half of the year. Naturally you will refresh this pivot table. However, even if some numbers are greater than 350, those cells will not be highlighted.Refresh Table

To solve this problem, you can refer to the following part.

Modify Existing Conditional Formatting

If the rule of conditional formatting is already applied in your worksheet, you can follow the steps below.

  1. Click the button “Conditional Formatting” in the toolbar.
  2. And then choose the option “Manage Rules”.Manage Rules
  3. In the new window, click the rule that you need to change.
  4. And then click the button “Edit Rule” in the window. Besides, you can also double click the rule directly.Edit Rule
  5. In the “Edit Formatting Rule” window, choose the third option in the “Apply Rule To” area.Choose Third Option
  6. Next click the button “OK” in the window.
  7. And then still click “OK” in the rules manager window.

Now you will come back to the worksheet. At this time, you will find that all the cells that satisfy the rule will be highlighted. You also need to notice that if you choose the second option in step 5, the cell for the “Grand Total” will also be highlighted.Result

And the next time if you add now data into the pivot table, the conditional formatting rule will take effect automatically.

Set Conditional Formatting When Creating it

In this part, you will set the conditional formatting when you are creating it. Now there is no conditional formatting in the pivot table.

  1. Click the target range in the worksheet.
  2. And then click the “Conditional Formatting” in the toolbar.
  3. After that, move your cursor on the option “Highlight Cells Rules”.
  4. In the sub menu, choose the option “Greater Than”.Greater Than
  5. In the “Greater Than” window, set the criteria and the format according to your need.Click OK
  6. After that, click the button “OK” to save the setting.
  7. Now in the worksheet, you will see an icon. In this step, you need to click it.
  8. In the sub menu, you will also see three options. Here still choose the third option.Choose Option

Now you have finished the setting.

On the other hand, you may choose the option “New Rule” in step 3. And you will see the “New Formatting Rule” window. In this window, you can also choose the option for the rules. In addition, other settings for the new rule are the same as usual.

New Formatting Rule

From the above analysis, you must be clear about applying conditional formatting to a pivot table. And the problem will never exist in your worksheet.

Figure out the Reason for Excel Corruption

Excel will certainly corrupt due to multiple reasons. Before you repair it, you must have to figure out the reason for the data disaster. If you utilize the wrong methods, you may cause even worse damage to your files. If you desire to get back your data safely, you can apply a formidable tool. This Excel recovery tool can analyze the problem and repair corrupt Excel xlsx file with ease.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupt docx data and outlook repair software products. For more information visit

Comments are closed.