Part of the free Module 7: Conditional Formatting · Lesson 12 of 12 · Full Excel course
Manage Rules and Clear Rules are the two housekeeping commands for conditional formatting in Excel. The Conditional Formatting Rules Manager lists every rule on a selection, sheet or table so you can edit, reorder, retarget or delete it, while Clear Rules removes formatting from selected cells or the whole sheet. In this lesson you will learn both.
Why rule management matters
A range can carry many rules at once, and Excel evaluates them from the top of the list downwards. When two rules set the same property, such as fill colour, the higher rule wins unless you tick Stop If True. Copy-and-paste operations also duplicate rules silently, so a busy workbook can end up with dozens of overlapping fragments that slow recalculation and produce surprising colours. The Rules Manager is where you clean that up. Its layout has not changed between Excel 2016 and Microsoft 365.
Step by step: open and use the Rules Manager
- Select the range whose rules you want to review, then go to Home > Conditional Formatting > Manage Rules. The legacy key sequence Alt, O, D still opens the same dialog; see the Excel shortcut keys lesson for more.
- The Conditional Formatting Rules Manager opens showing each rule, its format preview and its Applies To range.
- To remove a rule, select it and click Delete Rule.
- To change a rule’s condition or format, select it and click Edit Rule.
- To change priority, select a rule and use the Move Up and Move Down arrows. Rules at the top are evaluated first.
- Tick Stop If True on a rule if lower rules should be skipped once it matches.
See the rules for the whole worksheet
By default the manager shows rules for the current selection only. Open the Show formatting rules for drop-down at the top and choose This Worksheet to list every rule on the sheet, or pick another sheet name to inspect its rules without switching tabs. This is the fastest way to find forgotten rules that are slowing a workbook.
Change the range a rule applies to
Click inside the Applies to box for a rule and type a new range, or click the range selector and drag on the sheet. Use this to extend a rule to new rows or to merge several fragmented ranges, for example =$B$2:$G$9, into one.
Clear Rules
- Select the cells to clean, or any cell if you want to clear the whole sheet.
- Go to Home > Conditional Formatting > Clear Rules.
- Choose Clear Rules from Selected Cells, Clear Rules from Entire Sheet, or, when the selection is inside a table or PivotTable, the extra options for that object.
Tips and common mistakes
- Changing a cell’s fill colour manually does not remove the rule. Always use Clear Rules or Delete Rule.
- Inserting rows inside a formatted range extends the rule; inserting rows below it does not. Check Applies To after adding data.
- Use Paste Special > Formats or the Format Painter to copy rules, and then check the manager for duplicates.
- Conditional formats survive Clear Contents but are removed by Clear All.
- If colours look wrong, reorder the rules or add Stop If True rather than adding yet another rule.
Related lessons
- Highlight Cells Rules in Excel
- Use a formula in conditional formatting
- Conditional Formatting course hub
Frequently asked questions
How do I find every conditional format on a sheet?
Open Manage Rules and set Show formatting rules for to This Worksheet. You can also press F5, click Special and choose Conditional formats to select every cell that has a rule.
What does Stop If True do?
When the rule matches a cell, Excel does not evaluate the rules below it for that cell. Use it to keep a lower rule from overriding a higher one.
Why does the sheet slow down when I have many rules?
Every rule is recalculated on each change. Merge fragmented Applies To ranges, delete unused rules and avoid whole-column references such as A:A inside rule formulas.
Want the finished version? Ready-made Excel dashboards and KPI templates are available at NextGenTemplates.com.