Conditional formatting is one of the quickest ways to make a spreadsheet easier to read and act on. Instead of manually highlighting cells, you set rules that apply formatting automatically based on the values in your data — red cells for low stock, a green-to-red colour scale for performance, arrows pointing up or down for trends. It turns a wall of numbers into something that tells a story at a glance. This guide covers every main type of conditional formatting rule and how to manage them properly.
Why Conditional Formatting Is Worth Using
Conditional formatting saves time and reduces the chance of missing important information. Rather than scanning hundreds of rows to find problems, Excel highlights them for you. Common uses include:
- Spotting overdue dates or missed targets immediately
- Highlighting the highest and lowest values in a dataset
- Showing progress towards goals using data bars
- Flagging duplicate entries in a list
- Making reports and dashboards more visual without creating charts
The formatting updates automatically whenever the underlying data changes, so it stays accurate without any manual effort.
How to Access Conditional Formatting
All conditional formatting options are in the Home tab on the ribbon. Click the Conditional Formatting button to see the menu. You’ll apply it to a selected range of cells — always select the cells first, then set the rule.
Highlight Cell Rules
These are the most straightforward rules. They highlight individual cells that meet a condition.
Select your range, then go to Conditional Formatting > Highlight Cells Rules. Options include:
- Greater Than / Less Than — highlight cells above or below a value you specify
- Between — highlight cells whose value falls within a range you define
- Equal To — highlight cells that exactly match a specific value or text string
- Text That Contains — highlight cells containing a particular word or phrase
- A Date Occurring — highlight dates that fall in the last 7 days, this month, next week, and so on
- Duplicate Values — highlight any value that appears more than once in the selection (or highlight unique values instead)
For each rule, you choose the formatting to apply — Excel offers preset colour combinations or you can click “Custom Format” to set your own font colour, background, border, or bold/italic styling.
Top/Bottom Rules
Top/Bottom rules highlight the best or worst performers in a dataset relative to each other rather than against a fixed value.
Go to Conditional Formatting > Top/Bottom Rules:
- Top 10 Items — highlights the highest N values (you can change the number)
- Bottom 10 Items — highlights the lowest N values
- Top 10% — highlights the top percentage of values
- Bottom 10% — highlights the bottom percentage
- Above Average / Below Average — compares each cell to the average of the whole range
These rules are dynamic — if the data changes, the highlighted cells update automatically to reflect the new top or bottom performers.
Data Bars
Data bars add a coloured bar inside each cell, with the bar length proportional to the cell’s value relative to the others in the range. The effect is similar to a bar chart built directly into the cells.
Select your range, then go to Conditional Formatting > Data Bars and choose a colour. The cell with the highest value gets the longest bar; the cell with the lowest gets the shortest. Both the number and the bar are visible in the cell.
Data bars are particularly effective for showing sales figures, quantities, or scores at a glance without needing a separate chart.
Colour Scales
Colour scales apply a gradient of colour across a range, going from one colour at the lowest values to another at the highest. A common choice is red-yellow-green: low values appear red, mid-range values are yellow, and high values are green — giving an immediate visual sense of which values are good and which are problematic.
Go to Conditional Formatting > Color Scales and choose from the preset options. You can also customise the minimum, midpoint, and maximum colours if the defaults don’t suit your meaning (for example, if a high value is actually a bad thing, you might want high = red).
Icon Sets
Icon sets place a small icon — arrows, traffic lights, stars, flags — inside each cell to indicate its value relative to the range. For example, an upward green arrow for high values, a sideways yellow arrow for middle values, and a downward red arrow for low values.
Go to Conditional Formatting > Icon Sets and choose a set. By default, Excel divides the range into thirds (or quarters for four-icon sets), but you can customise the thresholds:
- Go to Conditional Formatting > Manage Rules
- Select the icon set rule and click Edit Rule
- Adjust the value thresholds for each icon
You can also choose to show only the icon and hide the number, which creates a clean dashboard-style view.
Creating a Custom Formula Rule
The most powerful type of conditional formatting uses a custom formula. This lets you do things that the built-in rules can’t — like highlighting an entire row based on the value in just one cell of that row.
Example: highlight the entire row in red when the Status column (column D) contains “Overdue”.
- Select the full data range — for example,
A2:E100 - Go to Conditional Formatting > New Rule
- Select “Use a formula to determine which cells to format”
- In the formula box, enter:
=$D2="Overdue" - Click Format, choose your highlighting colour, then click OK twice
The dollar sign before D ($D2) locks the formula to column D but allows the row number to change as Excel checks each row. Without the dollar sign before the column letter, the formula would shift columns as it moves across, which breaks the rule. The row number has no dollar sign so that it adjusts for each row.
This technique works for any condition — flagging rows where a date is past today (=$C2<TODAY()), where a value exceeds a target (=$E2>$F2), or where a cell is blank (=$B2="").
Managing Rules: Editing, Deleting and Priority
To see and manage all the rules applied to a range:
- Click anywhere in the formatted range
- Go to Conditional Formatting > Manage Rules
You’ll see a list of all rules applied to the current selection or sheet. From here you can:
- Edit a rule by selecting it and clicking Edit Rule
- Delete a rule by selecting it and clicking Delete Rule
- Reorder rules using the up and down arrows — rules are applied in order from top to bottom, and earlier rules take priority
- Tick the Stop If True checkbox to prevent lower rules from applying once a higher rule is met
Rule priority matters when two rules could apply to the same cell. For example, if you have a rule for “greater than 100” formatting in green and another for “top 10” formatting in gold, the one listed first in the Manage Rules panel wins.
Clearing Conditional Formatting
To remove conditional formatting:
- From selected cells: Select the cells, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells
- From the entire sheet: Go to Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet
Clearing rules removes the formatting but leaves your data and any manual formatting you’ve applied (like bold text or column widths) intact.
Conditional formatting is one of those features that takes minutes to set up but makes spreadsheets considerably easier to work with day to day. Start with a simple highlight rule on your next dataset and you’ll quickly find yourself using it on everything.
