How to Add Conditional Formatting Rules in Excel

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to add additional conditional formatting rules in Microsoft Excel, beyond the default ones that come with the program. You will learn how to create your own rules based on cell values, text, dates, or formulas, and how to manage multiple rules on the same range. This lets you highlight trends, spot outliers, or flag errors automatically as your data changes.

What You’ll Need

A computer with Microsoft Excel installed (2016, 2019, 2021, or Microsoft 365). The steps are similar across these versions.
A worksheet with data you want to format. The data can be numbers, text, or dates.
Basic familiarity with selecting cells and navigating the Ribbon menu.

Step-by-Step

Select the cells or range that you want to apply the new rule to. Click and drag over the cells, or click a single cell if you want the rule to apply to an entire column or table. For a whole column, click the column letter.
Open the Conditional Formatting menu. Go to the Home tab on the Ribbon. In the Styles group, click Conditional Formatting. A dropdown menu appears.
Choose a rule type. From the dropdown, you can pick a preset like Highlight Cells Rules or Top/Bottom Rules. For a fully custom rule, click New Rule at the bottom of the menu.
Select a rule type in the dialog box. If you clicked New Rule, the New Formatting Rule dialog opens. Choose one of the options, such as Format only cells that contain or Use a formula to determine which cells to format. For most simple rules, the first option is easiest.
Set the rule condition. In the lower part of the dialog, define what triggers the formatting. For example, if you chose Format only cells that contain, select Cell Value from the first dropdown, then choose an operator like greater than, and type a value like 100 in the box.
Click the Format button. This opens the Format Cells dialog. Go to the Fill tab to pick a background color, or the Font tab to change text color or make it bold. Click OK when done.
Review the preview and click OK. The dialog shows a preview of your formatting. If it looks correct, click OK to add the rule. Your selected cells now show the formatting when the condition is met.
Add another rule on the same range. With the same cells still selected, repeat steps 2 through 7. Excel applies the new rule on top of the existing one.
Manage rule priority. If you have multiple rules, go to Conditional Formatting > Manage Rules. In the dialog, use the up and down arrows to change the order. Rules higher in the list take priority if two rules conflict.
Edit or delete a rule. In the Manage Rules dialog, select a rule and click Edit Rule to change it, or Delete Rule to remove it. Click OK to apply changes.

Common Mistakes

Applying the rule to the wrong range. Double-check the range shown in the Applies to box in the Manage Rules dialog. A common error is selecting only a few cells when you meant the whole column.
Using absolute vs. relative references in formulas. When using a formula-based rule, remember that Excel treats the formula as relative to the active cell. If you write =A1>100 and the active cell is B2, Excel checks B2 against A2, not A1. Use $A$1 for an absolute reference if needed.
Forgetting to set a fill color. If you click OK without setting a format, the rule does nothing visible. Always click Format and choose at least a fill or font color.
Overlapping rules with conflicting formats. If two rules apply to the same cell, only the higher-priority rule shows. This can hide important highlights. Check the rule order in Manage Rules.
Typing values with units. In the rule condition, enter raw numbers like 100, not 100 USD or 100%. Percentages should be entered as decimals (e.g., 0.25 for 25%) unless you use the percentage format in the cell itself.

Tips

Use the Quick Analysis tool. After selecting data, a small icon appears at the bottom right. Click it to access common conditional formatting options without going through the Ribbon.
Use data bars, color scales, and icon sets. These preset rules in the Conditional Formatting menu are great for visualizing trends without writing formulas. They are especially useful for large datasets.
Test with a small sample first. Before applying a rule to a huge dataset, try it on a few rows to confirm the logic works as expected.
Name your rules. In the Manage Rules dialog, you can rename a rule by selecting it and typing in the rule description box. This helps when you have many rules.
Use the Stop If True checkbox. In Manage Rules, select a rule and check Stop If True to prevent lower-priority rules from applying to cells that match this rule. This is useful for mutually exclusive conditions.
Copy formatting rules with Format Painter. Select a cell with the rule you want, click the Format Painter icon in the Home tab, then click the target range. This copies the conditional formatting rules along with the cell formatting.

Troubleshooting

Rule does not apply to new rows added later. If you format a range like A1:A10 and later add data in A11, the rule does not cover it. Convert your data to an Excel Table (Ctrl+T) or extend the range in the Manage Rules dialog. Tables automatically apply rules to new rows.

Formatting disappears after sorting or filtering. Conditional formatting is tied to the cells, not the values. When you sort, the formatting moves with the cells, which is usually fine. If it looks wrong, check that the rule’s Applies to range still covers the correct area after sorting.

Rule works but shows the wrong color. Another rule with higher priority may be overriding yours. Open Manage Rules, select your rule, and move it up using the arrow buttons. Also check if the cell has manual formatting that conflicts.

Formula-based rule returns an error. If your formula has a mistake, Excel may show a warning when you click OK. Check for typos, missing parentheses, or incorrect cell references. Test the formula in a blank cell first to see if it returns TRUE or FALSE.

Conditional formatting is grayed out. This happens when the worksheet is protected or in a shared workbook. Unprotect the sheet under Review > Unprotect Sheet, or remove the sharing mode.

Performance slows down with many rules. Hundreds of rules on a large sheet can make Excel sluggish. Consolidate rules where possible, or use a single formula-based rule that covers multiple conditions with OR or AND functions.

Frequently Asked Questions

Can I apply different rules to different parts of the same column? Yes. Select the specific cells you want each rule to apply to, then add the rule. For example, select A1:A10 for one rule and A11:A20 for another. Each rule has its own range.

How do I remove all conditional formatting from a sheet? Select the entire sheet by clicking the triangle at the top-left corner, then go to Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Can I use text values in a rule? Yes. In the rule condition, choose Text from the first dropdown and then select options like contains or equal to. Type the text without quotes.

Does conditional formatting work with dates? Yes. You can highlight dates that are before, after, or between specific dates. Excel stores dates as numbers, so you can also use formulas like =A1>TODAY() to highlight future dates.

Can I copy a rule to another worksheet? Yes. Use Format Painter on a cell in the source sheet, then select the target range on the other sheet. The rule is copied along with the formatting.

Why does my rule only apply to the first cell? This usually happens with formula-based rules when the formula uses a relative reference incorrectly. Make sure the formula refers to the active cell (the first cell in your selection) without a dollar sign if you want it to adjust for each row, or with a dollar sign if you want it fixed.

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