Excel Guides

Removing Conditional Formats but Not the Effects in Excel

If you have applied conditional formatting to your data in Excel, you may want to remove the conditional formatting but keep the effects. For example, you may have applied a conditional format that changes the background color of cells based on their value. If you remove the conditional format, the background color will stay the same.

There are a few ways to remove conditional formatting but keep the effects. One way is to use the Clear Rules feature. To do this, select the cells that have the conditional formatting that you want to remove. Then, go to Home > Styles > Clear Rules. Select Clear Rules from Selected Cells.

Another way to remove conditional formatting but keep the effects is to use a macro. The following macro will remove all conditional formatting from the selected cells while keeping the effects:

Sub RemoveConditionalFormatting()

    Dim rng As Range
    Set rng = Selection

    Dim cf As FormatCondition
    For Each cf In rng.FormatConditions
        cf.Delete
    Next cf
    
End Sub

To use this macro, select the cells that have the conditional formatting that you want to remove. Then, run the macro. The conditional formatting will be removed but the effects will remain.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.