Conditional formatting is one of Excel’s most practical features — and one that’s often used only at surface level. At its most basic, it lets you colour-code cells based on their values. At its most advanced, it allows you to build dynamic dashboards where important information jumps out automatically, without anyone having to highlight a thing.
This guide walks through conditional formatting from the fundamentals to the more powerful techniques, using the version of Excel included in Office 2024 Professional Plus.
What Is Conditional Formatting?
Conditional formatting applies formatting — colour fills, font colours, borders, icons, or data bars — to cells automatically based on rules you define. The formatting updates dynamically as data changes, which means your visual indicators stay accurate without any manual intervention.
Getting Started: Highlight Cells Rules
The quickest way to get started is with the preset rules under Home > Conditional Formatting > Highlight Cells Rules.
Highlighting Values Greater Than a Threshold
- Select the range of cells you want to format (e.g., a column of sales figures).
- Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
- Enter your threshold value (e.g., 10000 for sales over €10,000).
- Choose a format from the dropdown — or select Custom Format for more control.
- Click OK.
Any cell in the selected range containing a value above €10,000 will now be formatted automatically. As the data changes, the formatting updates.
Highlighting Duplicates
Select a column, go to Highlight Cells Rules > Duplicate Values, and choose a format. This immediately identifies repeated entries — useful for catching duplicate invoice numbers, duplicate customer records, or repeated order IDs.
Highlighting Dates
The A Date Occurring option lets you highlight cells containing dates that fall in relative ranges: yesterday, today, tomorrow, last week, this week, this month, and so on. For project trackers or task lists with due dates, this makes overdue or upcoming items immediately visible.
Top/Bottom Rules
Under Conditional Formatting > Top/Bottom Rules, you can highlight the top or bottom N items or percentages in a range. Useful applications include:
- Highlighting your top 10 best-selling products in a report
- Flagging the bottom 10% of performers in a dataset
- Identifying values above or below average
Colour Scales: The Traffic Light Overview
Colour scales apply a gradient fill across a range — typically green for high values shading to red for low ones, or vice versa. This gives you an instant heat map view of your data.
Applying a Colour Scale
- Select your data range.
- Go to Conditional Formatting > Colour Scales.
- Hover over the preset options to preview them on your data.
- Click a scale to apply it.
For sales performance tables, financial dashboards, or any dataset where relative performance matters, colour scales provide an immediate visual read that no amount of manual formatting can match.
Data Bars: Visual Proportions Within Cells
Data bars display a bar within each cell whose length is proportional to the cell’s value relative to the range. They work like a miniature bar chart embedded directly in the spreadsheet.
Select your range, go to Conditional Formatting > Data Bars, and choose a style. Solid bars are cleaner for formal reports; gradient bars work well in dashboards. Data bars are particularly effective for quantities, scores, or percentages where the visual comparison between rows is useful.
Icon Sets: Symbols for Categorical Data
Icon sets apply icons — arrows, traffic lights, stars, flags — to cells based on threshold rules. They’re useful for:
- Status indicators (green tick, amber warning, red alert)
- Trend arrows (up, flat, down)
- Priority ratings (one to five stars)
Select your range, go to Conditional Formatting > Icon Sets, and choose a set. You can customise the thresholds by going to Manage Rules > Edit Rule.
Custom Formula-Based Rules: The Most Powerful Option
Formula-based rules allow you to define conditional formatting based on any formula that returns TRUE or FALSE. This unlocks the full power of Excel’s formula language for formatting purposes.
Example 1: Highlight Entire Rows Based on a Column Value
Suppose you want to highlight an entire row of a project tracker when the status column contains “Overdue”:
- Select the entire data range (e.g., A2:F50).
- Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter the formula:
=$F2="Overdue"(note the $ before F to lock the column reference). - Set your format (e.g., red fill).
- Click OK.
The entire row for any project with “Overdue” in column F will now be highlighted red, regardless of what’s in the other columns.
Example 2: Highlight Weekend Dates
To highlight dates that fall on a Saturday or Sunday in a scheduling spreadsheet:
Formula: =WEEKDAY(A2,2)>=6
This uses WEEKDAY with mode 2 (where Monday = 1 and Sunday = 7), and highlights any cell where the result is 6 (Saturday) or 7 (Sunday).
Example 3: Comparing to a Target
To highlight sales figures below a target stored in cell I1:
Formula: =B2<$I$1
The dollar signs make I1 an absolute reference, so the same target value is used for every row in the comparison.
Managing Your Rules
As conditional formatting rules accumulate, the Manage Rules dialogue becomes essential. Access it via Conditional Formatting > Manage Rules.
Here you can see all rules applied to the current selection or the entire sheet, edit or delete individual rules, and — crucially — change their priority order. Rules are applied from top to bottom; if two rules conflict, the higher rule takes precedence. Tick the Stop If True checkbox next to a rule to prevent lower rules from applying when a condition is met — useful for tiered traffic-light systems.
Practical Business Applications
- Budget tracking — Red when spending exceeds budget, amber within 90%, green under 80%
- Sales dashboards — Top performers in green, below-target in red
- Project trackers — Overdue tasks highlighted, upcoming deadlines in amber
- Stock control — Low stock levels flagged automatically
- HR timesheets — Overtime hours highlighted for review
Conditional formatting works best when it's purposeful — use it to highlight the handful of things that genuinely need attention, not to colour every cell in the spreadsheet. Two or three well-designed rules on a dashboard are more useful than twenty overlapping ones.
All of these features are available in Excel as part of Office 2024 Professional Plus at €34.99 from GetRenewedTech. Mac users will find the same core conditional formatting tools in Office 2024 for Mac at €58.99 — with a few minor interface differences but identical functionality for the techniques covered in this guide.
