How to Use Conditional Formatting in Excel (With Real Examples)
Conditional formatting in Excel automatically changes how a cell looks based on its value. It is one of the most useful features in Excel for anyone who works with data regularly, because it turns a flat spreadsheet into something you can read at a glance.
This guide covers the five most useful conditional formatting rules, shows you how to apply each one step by step, and explains how to combine them with other Excel features to build spreadsheets that practically manage themselves.
In this post
What Is Conditional Formatting?
Conditional formatting is a rule you apply to a cell or range that changes the cell’s appearance when a condition is met. The condition could be a value threshold (highlight anything over 100), a comparison (mark cells lower than the cell above), a text match (flag any cell containing the word “overdue”), or a formula.
The formatting itself can be a background colour, font colour, border, icon, or data bar. The cell value does not change. Only the visual appearance does.
| Practical tip: Conditional formatting updates automatically when your data changes. You set the rule once and Excel applies it continuously. There is no need to reformat manually when values update. |
How to Open Conditional Formatting in Excel
Select the cells you want to format, then go to Home on the ribbon and click Conditional Formatting. A dropdown menu shows all available rule types. You can also reach it by right-clicking a selected range, though the ribbon route is faster.
Everything in this guide starts from that same menu. All five rules below are accessible from Home > Conditional Formatting.
Rule 1: Highlight Cells Based on Value
This is the most common use. You set a threshold and Excel highlights every cell that meets it.
Select your range. Go to Home > Conditional Formatting > Highlight Cells Rules. Choose Greater Than, Less Than, Between, or Equal To depending on your condition. Enter your value in the dialog box and choose a colour format from the dropdown on the right. Click OK.
Example: you have a column of monthly sales figures. Select the range, choose Greater Than, enter 10000, and pick Green Fill with Dark Green Text. Every cell above $10,000 turns green instantly.
| Practical tip: You can apply multiple highlight rules to the same range. Excel applies them in order from the Manage Rules dialog. Rules higher in the list take priority when conditions overlap. |
Rule 2: Highlight Duplicate Values
Finding duplicates manually in a large dataset is slow and error-prone. Conditional formatting does it in seconds.
Select your range. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose whether to highlight duplicates or unique values, then pick a colour format. Click OK. This pairs well with the Remove Duplicates tool: use conditional formatting first to see the duplicates visually, then remove them once you have confirmed which ones to delete.
| Note: Duplicate highlighting is case-insensitive. “Apple” and “apple” count as duplicates. If case matters in your data, you need a formula-based rule instead. |
Rule 3: Colour Scales
Colour scales apply a gradient across a range so that the highest values get one colour, the lowest get another, and everything in between shades proportionally. This is useful for heat maps, performance dashboards, and any data where relative magnitude matters more than exact values.
Select your range. Go to Home > Conditional Formatting > Color Scales. Choose a two-colour or three-colour scale from the submenu. Excel applies it immediately. The default red-yellow-green scale works well for performance data: low is red, mid is yellow, high is green.
| Our take: Colour scales work best on uniform numeric data. If your range contains text or blanks, those cells are excluded from the scale, which can skew the gradient. Clean your data first. |
Rule 4: Data Bars
Data bars add a horizontal bar inside each cell, sized proportionally to the cell’s value relative to the range. They turn a column of numbers into a simple in-cell bar chart, which is useful when you want to show magnitude without leaving the spreadsheet.
Select your range. Go to Home > Conditional Formatting > Data Bars. Choose a solid fill or gradient fill bar from the submenu. Excel applies the bars immediately.
Data bars work especially well alongside pivot tables. Select the values column in your pivot table and apply data bars to make comparisons visible at a glance without building a separate chart.
Rule 5: Formula-Based Rules
Formula-based rules are the most powerful type. Instead of choosing from a preset condition, you write an Excel formula. If the formula returns TRUE, the formatting applies. This unlocks conditional formatting based on values in other columns, relative comparisons between rows, or any logic you can express as a formula.
Select your range. Go to Home > Conditional Formatting > New Rule. Choose “Use a formula to determine which cells to format”. Enter your formula in the box. Click Format to choose your appearance, then click OK.
Example: you have a task list in columns A and B, where column B contains the status. You want to highlight the entire row in grey when the status is “Done”. Select the full table range (e.g. A2:B50), then enter the formula =$B2=”Done”. The dollar sign before B locks the column reference so the rule checks column B for every row, but evaluates each row independently.
Formula-based rules become especially powerful when combined with checkboxes in Excel. Link a checkbox to a cell, then write a conditional formatting rule that triggers when that linked cell is TRUE. Ticking the checkbox automatically applies the formatting.
| Practical tip: Always use a relative row reference (like $B2, not $B$2) in formula-based rules applied to a multi-row range. A fully locked reference like $B$2 checks only that one cell for the entire range, which is almost never what you want. |
How to Manage, Edit, and Delete Rules
To see all conditional formatting rules on a sheet, go to Home > Conditional Formatting > Manage Rules. The dialog shows every rule, its range, and its order of priority. You can edit any rule by selecting it and clicking Edit Rule. You can delete a rule by selecting it and clicking Delete Rule. You can also drag rules up or down to change the priority order.
If two rules conflict, the rule higher in the list wins. There is also a “Stop If True” checkbox next to each rule. Ticking this means Excel stops evaluating further rules for a cell once this rule’s condition is met. Use it when you have layered rules and want a clear hierarchy.
Combining Conditional Formatting with Data Validation
Conditional formatting and data validation in Excel work well together. Data validation restricts what can be entered in a cell. Conditional formatting highlights what has been entered. Used together, you can build input forms that both enforce clean data entry and visually flag anything that needs attention.
A practical example: apply data validation to a column to restrict entries to a list of approved values. Then apply a formula-based conditional formatting rule that highlights any cell not in that list in red. The two rules work independently, but together they create a self-enforcing tracker.
Clearing Conditional Formatting
To remove conditional formatting from a range, select the cells, go to Home > Conditional Formatting > Clear Rules, and choose Clear Rules from Selected Cells. To remove all conditional formatting from the entire sheet, choose Clear Rules from Entire Sheet.
Clearing rules does not affect the cell values or any other formatting (like font size or borders) that you applied manually. Only the conditional rules are removed.
FAQs
How many conditional formatting rules can I apply to one cell?
Excel does not set a hard limit on the number of rules per cell. In practice, applying more than five or six rules to the same range becomes hard to manage and can slow down large spreadsheets. Use the Manage Rules dialog to keep things organised.
Why is my conditional formatting not working?
The most common causes are a locked cell reference in a formula rule (use $B2, not $B$2 for row-by-row evaluation), a range mismatch where the rule applies to a different range than you intended, or a rule priority conflict. Check all three in the Manage Rules dialog.
Does conditional formatting slow down Excel?
It can, particularly on large datasets with many formula-based rules. Highlight rules based on simple values have negligible impact. Complex formula rules across tens of thousands of rows can increase recalculation time. If performance is an issue, limit formula-based rules to the range you actually need, rather than selecting entire columns.
Can I copy conditional formatting to another range?
Yes. Select a cell with the formatting you want to copy, click Format Painter on the Home tab, then click or drag across the destination range. The conditional formatting rules transfer along with any other formatting.
Does conditional formatting work in Excel for Mac?
Yes. All the rules covered in this guide work identically on Excel for Mac and Excel for Windows. The menu paths are the same.
