Revise a Conditional Formatting Rule in Excel

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to edit an existing conditional formatting rule in Microsoft Excel and Google Sheets, covering both the simple and the advanced rule editors. You will learn how to change the rule’s condition, its formatting style, or the cell range it applies to, and you will see what to do when the rule does not behave as expected.

What You’ll Need

A workbook with at least one conditional formatting rule already applied.
Access to Excel (2016 or later, or Microsoft 365) or Google Sheets.
Knowledge of the cell references or values the rule is based on, if you plan to change the condition.

Step-by-Step

In Microsoft Excel (Windows and Mac)

Select the range that contains the conditional formatting. If you are unsure which cells have rules, select any cell in the worksheet, then press Ctrl+A (Windows) or Cmd+A (Mac) to select all cells. This makes every existing rule visible in the next step.
Open the Conditional Formatting menu. On the Home tab, in the Styles group, click Conditional Formatting.
Choose “Manage Rules.” From the drop-down menu, click Manage Rules…. A dialog box opens.
Select the scope. At the top of the dialog, use the “Show formatting rules for” drop-down to choose Current Selection, This Worksheet, or a specific table or PivotTable. If you selected a large range in step 1, choose “This Worksheet” to see every rule.
Pick the rule to edit. In the list, click the rule you want to change. The rule’s details appear below the list.
Edit the condition. In the “Format only cells with” or “Edit the Rule Description” section, change the operator (e.g., “greater than”, “between”) and the value(s). You can type a number, a formula, or click the cell selector icon to point to a cell.
Edit the formatting. Click the Format… button. In the Format Cells dialog, change the font, fill color, border, or number format. Click OK to close it.
Change the applied range. In the “Applies to” field at the bottom of the rule list, edit the range directly (e.g., change =$A$1:$A$10 to =$A$1:$A$20), or click the collapse button to select a new range on the sheet.
Click “Apply” then “OK.” The rule is updated immediately. Check your cells to confirm the formatting changed as expected.

In Google Sheets

Select the range that has the rule. If you do not know which cells it covers, click any cell and then press Ctrl+A (Windows) or Cmd+A (Mac) to select the whole sheet.
Open the menu. Click Format > Conditional formatting. A panel opens on the right.
Find your rule. The panel lists all rules for the selected range. If you selected the whole sheet, you will see every rule in the file.
Click the rule you want to revise. Its settings expand in the panel.
Edit the condition. Under “Format cells if…”, choose a new condition from the drop-down (e.g., “Greater than”, “Text contains”) and enter the new value or formula.
Edit the formatting. Use the “Formatting style” section to change the fill color, text color, bold, italic, or strikethrough. The live preview updates as you change it.
Adjust the range. In the “Apply to range” box, type a new range or click the grid icon to select cells on the sheet.
Click “Done.” The rule is saved and applied immediately.

Common Mistakes

Editing the wrong rule. When multiple rules overlap, the list may show several similar entries. Check the “Applies to” range and the rule description carefully before changing anything.
Using relative vs. absolute references. In Excel, if your rule uses a formula, a relative reference like A1 applies to each cell in the range, while $A$1 always refers to that single cell. Mixing these up causes the rule to highlight the wrong cells.
Forgetting to change the “Applies to” range. You can edit the condition and formatting, but if the range stays the same, new data outside that range will not be formatted.
Overlapping rules with the wrong priority. In Excel, the rule higher in the list wins when two rules apply to the same cell. If your edit creates a conflict, reorder the rules using the up/down arrows.
Typing values with units. If the rule says “greater than 10”, do not type “10 kg” or “$10”. Enter the raw number only.

Tips

Use the “Stop If True” checkbox (Excel). When you want the edited rule to be the final word, check “Stop If True” so lower-priority rules do not override it.
Test on a copy. Before editing a complex rule, duplicate the sheet and test your changes there. This avoids losing the original formatting.
Use formulas for dynamic rules. Instead of a fixed value, write a formula like =A1>TODAY() to highlight dates after today. The rule updates automatically.
Name your rules (Excel). In the Manage Rules dialog, you can rename a rule by editing the text in the “Rule (applied in order shown)” column. This helps when you have many rules.
Check the “Applies to” box after editing. Excel sometimes changes the range when you edit a formula. Always verify it before clicking OK.

Troubleshooting

The rule does not change anything. First, confirm the rule is still enabled (the checkbox next to it is ticked). Then check the “Applies to” range – it may not include the cells you are looking at. Finally, verify the condition is true for at least one cell in that range.
The formatting looks wrong after editing. Undo the change (Ctrl+Z / Cmd+Z) and re-open the rule. Check that you did not accidentally change the operator from “greater than” to “less than”, or swap the fill color with the font color.
Only part of the range updates. This usually means the “Applies to” range is smaller than you intended, or the rule uses relative references that shift for each cell. Adjust the range, or add dollar signs to the formula references.
Another rule overrides yours. In Excel, open Manage Rules, select the rule, and use the up arrow to move it higher in priority. In Google Sheets, rules are applied in the order they appear; the first rule that matches wins, so reorder by dragging or deleting and re-adding.
The rule disappeared. It may have been deleted accidentally, or the range it applied to was deleted. Check the Manage Rules dialog and the “Applies to” field. If the range was deleted, the rule is removed automatically.
In Google Sheets, the panel does not show my rule. Make sure the correct range is selected. If the rule applies to a range outside your current selection, it will not appear until you select those cells or choose “All sheets” from the range filter.

Frequently Asked Questions

Can I edit a conditional formatting rule without selecting the cells first?
Yes. In Excel, open Manage Rules and choose “This Worksheet” from the drop-down to see every rule. In Google Sheets, select any cell, open Conditional formatting, and use the range filter to show all rules.

What is the difference between editing the rule and clearing it?
Editing keeps the rule and changes its condition or formatting. Clearing (or deleting) removes the rule entirely, so the cells revert to their normal formatting.

Can I change the range a rule applies to without editing the condition?
Yes. In both Excel and Google Sheets, the “Applies to” field is separate from the condition. You can change only that field and leave the rest of the rule untouched.

Why does my formula-based rule highlight the wrong cells?
Most likely a relative vs. absolute reference issue. If the formula is written for the top-left cell of the range (e.g., =A1>10), Excel and Sheets automatically adjust it for each cell. If you need the formula to always look at one specific cell, use dollar signs like $A$1.

Can I edit a rule that uses a color scale or data bar?
Yes. The same Manage Rules dialog (Excel) or Conditional formatting panel (Sheets) lets you change the minimum, midpoint, and maximum values, as well as the colors or bar appearance.

Is there a way to see all rules in a workbook at once?
In Excel, open Manage Rules and select “This Worksheet” to see rules for the active sheet. To see rules on other sheets, switch to that sheet and repeat. Google Sheets shows rules for the current sheet only; there is no workbook-wide view.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.