How to Use Conditional Formatting in Excel: Rules Made Easy

A spreadsheet full of numbers is hard to read at a glance. Conditional formatting fixes that by letting Excel colour cells for you based on what's inside them. Overdue dates turn red, top performers stand out, duplicates get flagged. You set the rule once and it keeps working as the numbers change.

If you run a small business, it's one of the quickest ways to make a sales sheet, task list or stock check easier to scan. Here's how it works, following Microsoft's help pages.

Where conditional formatting lives in Excel

Select the cells you want to format first. Then, on the Home tab, find the Styles group and choose Conditional Formatting. Every rule type sits in that one menu, so once you know where it is, the rest is choosing the right option.

The main choices are Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets, plus New Rule for anything custom.

Highlighting cells that meet a simple condition in Excel

Start here if you just want to spot certain values. Choose Conditional Formatting, then Highlight Cells Rules. Microsoft lists commands such as Between, Equal To, Text that Contains, A Date Occurring and Duplicate Values.

1. Select the cells to check, for example a column of due dates.
2. On the Home tab, choose Conditional Formatting, then Highlight Cells Rules.
3. Pick a command, such as A Date Occurring or Text that Contains.
4. Enter the value or choose the option the dialog asks for.
5. Choose the format you want, then select OK.

Try Duplicate Values on a contact list. It's a fast way to see who you've entered twice.

Finding the best and worst with Top/Bottom Rules in Excel

When you want the leaders or laggards in a column, use Conditional Formatting and then Top/Bottom Rules. Microsoft names Top 10 Items, Bottom 10%, Above Average and Below Average. Choose one, set the number or percentage if asked, pick a format, and confirm.

This suits a sheet of campaign results or product sales, where you want the strongest and weakest rows to jump out without sorting the list.

Data bars, colour scales and icon sets in Excel

These three show the size of a value visually, so you can read a column in a second.

• Data Bars: choose Conditional Formatting, then Data Bars, and pick a bar style. Longer bars mean larger values.
• Color Scales: choose Conditional Formatting, then Color Scales, and pick a two-colour or three-colour scale. Hover over each icon to see which is which.
• Icon Sets: choose Conditional Formatting, then Icon Set, and pick a set of symbols.

Use them sparingly. One visual style per column is usually enough. If you pile on bars, colours and icons together, the sheet gets harder to read, not easier.

Writing your own conditional formatting rule with a formula

For anything the built-in options don't cover, choose Conditional Formatting, then New Rule. Select the formula rule type and enter a formula that starts with an equals sign and returns TRUE or FALSE. Then click Format, choose how matching cells should look, and select OK.

A typical use is shading a whole row when one cell in it meets a condition, such as a status column that says Late. Microsoft adds a useful warning: formulas that return errors don't get formatted, so wrap them in IFERROR or one of the IS functions.

Tricky formulas can be easier with help. If you'd like an assistant inside your workbook, I've covered Claude for Excel, how to install it and prompts to try.

Managing, changing and clearing Excel conditional formatting rules

When a sheet has several rules, the order matters. Choose Conditional Formatting, then Manage Rules, to open the Conditional Formatting Rules Manager. Use Move Up and Move Down to change which rule takes precedence. Microsoft also mentions Stop If True, which halts evaluation at a rule, but it can't be used with data bars, colour scales or icon sets.

To remove formatting, choose Conditional Formatting, then Clear Rules, and pick Clear Rules from Selected Cells or Clear Rules from Entire Sheet. Check which one you've chosen before you click, because the second wipes every rule on the sheet.

Using conditional formatting in Excel for the web or on a Mac

Excel for the web has the same Conditional Formatting menu in the Styles group on the Home tab. Choose New Rule, check the Apply to range box, pick a rule type, and select Done. For a formula, set the Rule Type to Formula. Manage Rules opens a task pane, where the pencil icon edits a rule, the trash icon deletes one, and there's a Delete all rules option.

Microsoft's page doesn't give Mac-specific steps. The menu names above are the closest match, though menus vary by version on Mac.

Copying conditional formatting to other cells in Excel

You don't have to rebuild a rule for every column. Microsoft says Format Painter copies conditional formatting to other cells. Afterwards, check any formula references, as they may need adjusting for the new location.

Job	Menu path
Spot duplicates or dates	Conditional Formatting, Highlight Cells Rules
Find the best and worst	Conditional Formatting, Top/Bottom Rules
Show size at a glance	Data Bars, Color Scales or Icon Sets
Custom rule	Conditional Formatting, New Rule
Change order of rules	Manage Rules, Move Up or Move Down
Remove rules	Clear Rules

If your team shares the sheet in Google Sheets as well, my guide on sorting a list in Google Sheets is a handy companion.

Frequently asked questions

How do I use conditional formatting in Excel?

Select your cells, open the Home tab, choose Conditional Formatting in the Styles group, then pick a rule type such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales or Icon Sets.

How do I use a formula in conditional formatting?

Choose Conditional Formatting, then New Rule, and select the formula rule type. Enter a formula that starts with = and returns TRUE or FALSE, click Format, choose the look, and select OK.

How do I remove conditional formatting in Excel?

Choose Conditional Formatting, then Clear Rules, and pick Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

Why isn't my conditional formatting working on cells with errors?

Microsoft says formulas that return errors don't get formatted. Wrap them in IFERROR or an IS function so the rule can apply.

Can I copy conditional formatting to other cells?

Yes. Format Painter copies conditional formatting to other cells, but check any formula references afterwards as they may need adjusting.