Pivot Tables are one of the best tools for summarizing and analyzing data. However, when applying conditional formatting, blank cells get formatted. This can be visually distracting and misleading. In this article, we will guide you through three effective methods to remove conditional formatting, specifically from blank cells within the Pivot Table.
To remove conditional formatting from blank cells in Excel Pivot Table, here is one simple solution by adding a new rule in the Conditional Formatting feature.
➤ First, select the cells in your Pivot Table that contain the formatted values and go to Home > Conditional Formatting > New Rule.
➤ Select the option Format only cells that contain.
➤ From the Edit the Rule Description section, choose Blanks from the dropdown menu and click OK.
➤ Go to Home > Conditional Formatting > Manage Rules.
➤ Checkmark the Stop If True box next to your new “Cell contains a blank value” rule.
➤ Click Apply and then OK to remove conditional formatting from blank cells in your Pivot Table.
Adding New Rule to Remove Conditional Formatting from Blank Cells
Adding a new rule targeting blank cells and instructing Excel not to format them is the simplest solution. Here, we will create a Pivot Table, then remove conditional formatting from the blank cells in the Pivot Table.
Suppose we have a sample dataset containing marks and grades for some students.
➤ Select your entire data range and go to the Insert tab on the ribbon and click PivotTable.
➤ In the new dialog box, ensure New Worksheet is selected and click OK.
➤ Now, build your Pivot Table by dragging the Student and Subject fields to the Rows area, and Marks to the Values area.
Imagine we have conditional formatting applied to all the cells in the Sum of Marks column, including blank cells.
To remove conditional formatting from blank cells.
➤ First, select the cells in your Pivot Table that contain the formatted values.
➤ Then, navigate to the Home tab, click Conditional Formatting, and select New Rule from the dropdown menu.
➤ In the New Formatting Rule dialog box, select the option Format only cells that contain.
➤ From the Edit the Rule Description section, choose Blanks from the dropdown menu.
Crucially, do not click the Format button. Since we want to remove the existing formatting, leaving the format description as “No Format Set” is exactly what we need.
➤ Then, click OK.
Next, we must ensure this “No Format Set” rule takes precedence over the existing color scale.
➤ With the same cells selected, go back to the Home tab, click Conditional Formatting, and select Manage Rules.
The Conditional Formatting Rules Manager window will appear, showing all rules applied to your selected range.
➤ Checkmark the Stop If True box next to your new “Cell contains a blank value” rule.
This instructs Excel to stop processing any further rules (like the color scale) for any cell that meets this blank cell criteria.
➤ Click Apply and then OK.
As a result, the conditional formatting is successfully removed from the blank cells in Pivot Table, leaving them clearly empty and unformatted.
Using Formula to Delete Conditional Formatting from Blank Cells
You can also use the ISBLANK function inside the Conditional Formatting feature to clear formatting from blank cells in your Pivot Table.
➤ Again, select the formatted values in your Pivot Table and go to the Home tab, click Conditional Formatting, and select New Rule.
➤ This time, in the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
➤ In the formula box, enter the formula.
=ISBLANK(C4)=TRUE
Here, C4 should be the address of the very first data cell in your selection (the cell with the first Sum of Marks value). This formula checks if the cell is truly blank.
➤ Leave the format set to No Format Set and click OK.
Next, we need to manage the rule’s priority.
➤ Go to the Home tab, click Conditional Formatting, and select Manage Rules.
In the Conditional Formatting Rules Manager, you will see the new formula-based rule listed.
➤ Checkmark the Stop If True box next to your new formula rule.
➤ Hit Apply and then OK.
This way, we have successfully removed the conditional formatting from the blank cells for the Pivot Table in Excel.
Applying VBA to Remove Conditional Formatting from Blank Cells
For a more permanent solution that can be applied to any range quickly, you can use a small VBA macro. This method will permanently delete the conditional formatting rules from only the blank cells within your selection.
➤ First, select the range of cells in your Pivot Table and go to the Developer tab and click Visual Basic to open the VBA editor.
➤ In the VBA editor, insert a new module, paste the following code, and hit Run.
Sub RemoveCFfromBlanks()
Dim cell As Range
For Each cell In Selection
If IsEmpty(cell.Value) Then
cell.FormatConditions.Delete
End If
Next cell
End Sub
After running the macro, the conditional formatting is instantly deleted from the blank cells, leaving the remaining cells with their conditional formatting intact.
Frequently Asked Questions
My conditional formatting is a Data Bar. When a cell is blank, it shows a tiny, faint bar. How do I stop this?
This is because Excel views the blank cell as a zero (0), and the Data Bar interprets zero as the minimum value, applying a very small amount of color. Ensure this “No Format” rule is at the top of your Manage Rules list and has Stop If True checked.
Can I prevent Excel from automatically copying conditional formatting when I refresh the Pivot Table?
Yes. Go to PivotTable Options > Layout & Format > Clear conditional formatting rules when updating the table (or disable “Preserve cell formatting on update”). This ensures blank cells don’t inherit the formatting on refresh.
Will filtering or hiding rows in a Pivot Table affect conditional formatting on blank cells?
No, conditional formatting is applied to the cell itself, not visibility. But using “Applies to” with dynamic ranges can help ensure only visible, non-blank cells are formatted.
Concluding Words
Above, we have explored several ways to remove conditional formatting from blank cells in Excel Pivot Table. Using the Blanks option in New Rule and checkmarking Stop If True is the quickest method for removing conditional formatting from blank cells. The VBA code offers permanent removal of formatting from blank cells. You can use any methods from above that best suit your needs to ensure your Pivot Tables are perfect. If you have any questions, please don’t hesitate to share them in the comments section below.
