Conditional Formatting

Excel Guides / Dashboard Building

Dashboard Building

Let the data colour itself: flag thresholds, add bars and icons, and highlight whole rows with a formula.

Updated 12 Oct 2026 · 7 min read · By Sky Dashboards

Conditional formatting changes how a cell looks based on its value: red when an invoice is overdue, green when a target is hit, a bar that grows with sales. Done well, it points the reader at the two numbers that matter on a page of fifty. Done badly, it turns a sheet into confetti. This guide covers the built-in rules, formula rules and how to keep a growing list of rules under control.

Where conditional formatting lives

Select the cells first, then open Home > Conditional Formatting. The menu has five families of ready-made rules and three commands for managing them:

• Highlight Cells Rules: compare each cell with a value, a date or some text.
• Top/Bottom Rules: the top or bottom items or percentages, or values above or below average.
• Data Bars, Color Scales and Icon Sets: graphics that scale with the value.
• New Rule..., Clear Rules and Manage Rules...

Highlight Cells and Top/Bottom rules

These cover most everyday needs in a few clicks. To flag every sale under $500:

1. Select the values.
2. Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than...
3. Type 500, or click a cell that holds the threshold so you can change it later without editing the rule.
4. Pick a format from the with list, such as Light Red Fill with Dark Red Text, or Custom Format... for your own, and click OK.

The same submenu has Between..., Equal To..., Text that Contains..., A Date Occurring... (yesterday, last week, next month and so on) and Duplicate Values.... Top/Bottom Rules offers Top 10 Items..., Top 10%..., their Bottom equivalents, Above Average... and Below Average.... Ten is only the default; change it to 3 for a top-three highlight.

Data Bars, Color Scales and Icon Sets

• Data Bars draw a bar inside each cell, scaled from the smallest to the largest value. Good for sales by product.
• Color Scales shade cells along a two- or three-colour gradient. Good for heat maps, such as bookings by weekday and hour.
• Icon Sets add arrows, shapes or traffic lights based on thresholds. Good for status columns.

The presets are a starting point. Open Manage Rules..., select the rule and click Edit Rule... to fine-tune it. Three settings are worth knowing:

• Show Bar Only and Show Icon Only hide the number, which helps when the value is already shown in the next column.
• Icon thresholds default to Percent, meaning a percentage of the way from the lowest to the highest value in the range, not the value itself. For fixed targets, such as green at 100% of budget, change Type to Number and type the cut-offs.
• For a colour scale that should turn at zero or at a target, set Midpoint to Type: Number instead of the default Percentile of 50.

Formula rules: highlight a whole row

The built-in rules only look at the cell they format. A formula rule can look anywhere, which is how you colour a whole invoice row based on its status column.

1. Select the data rows, A2:E7, starting from the top-left so that A2 is the active cell.
2. Choose Home > Conditional Formatting > New Rule...
3. Pick Use a formula to determine which cells to format.
4. In Format values where this formula is true, type the formula below.
5. Click Format..., choose a colour on the Fill tab, then click OK twice.

The anchoring is what makes it work. Excel treats the formula as if you had written it in the active cell and copied it across the range:

• $E locks the column, so every cell from A to E checks column E.
• 2 has no dollar sign, so the row moves: row 3 checks E3, row 4 checks E4, and so on.
• =$E$2 would lock the row too, and every row would follow the status of INV-101.
• =E2 would let the column drift: column B would check F, column C would check G, and the rule breaks.
• =$E1 with a selection that starts at row 2 shifts everything by one, so each row is coloured by the status of the row above.

To flag unpaid invoices by date instead of by a typed status, use =AND($E2<>"Paid",$C2<TODAY()). More on the dollar signs in absolute and relative references.

Manage Rules, order and Stop If True

Open Home > Conditional Formatting > Manage Rules... to see your rules. Set Show formatting rules for to This Worksheet; the default, Current Selection, hides rules on other cells.

• Order. Rules are applied in the order shown. When two rules set the same thing, such as fill colour, the higher rule wins. Use the up and down arrows to reorder them. Rules that set different things, say a fill and a bold font, both apply.
• Applies to. Edit the range here rather than reapplying the rule. It is also where you spot ranges that have been split into fragments.
• Stop If True. Tick it on a rule and, for any cell where that rule is true, the rules below it are skipped.

Stop If True is most useful with icon sets, which cannot be limited by a formula on their own. To show icons only on unpaid rows, add a formula rule above the icon set such as =$E2="Paid", give it no format and tick Stop If True. Paid rows match the empty rule and never reach the icons.

Conditional formatting in Tables

Apply a rule to a whole column of an Excel Table and it extends to new rows as they are added. That makes Tables the natural home for trackers and lists that grow.

One catch: conditional formatting formulas do not accept structured references such as [@Status]. Write the rule with normal references, =$E2="Overdue", and it still adjusts row by row inside the Table.

Keep rules fast and tidy

A handful of rules costs nothing. Hundreds of them, which build up quietly through copying and pasting, can make scrolling and typing sluggish.

• Paste values, not formats. Copying formatted cells copies their rules, and pasting into the middle of a range splits one rule into many. Press Ctrl + Alt + V, then V, then Enter to paste values only.
• Merge duplicates. In Manage Rules with This Worksheet selected, delete the copies and give the remaining rule a single Applies to range.
• Go easy on volatile functions. Rules using TODAY(), NOW(), OFFSET() or INDIRECT() recalculate constantly. One date rule is fine; one per column on 50,000 rows is not.
• Start again when it is a mess. Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet, then rebuild the two or three rules you actually need.

Frequently asked questions

How do I highlight an entire row based on one cell?

Select the whole data range, create a New Rule with Use a formula to determine which cells to format, and lock the column but not the row, for example =$E2="Overdue".

Why is my formula rule highlighting the wrong rows?

The formula is written for a different row from the active cell. If the selection starts in row 2, the formula must refer to row 2. Also check that the column has a $ and the row does not.

Which rule wins when two conditional formats conflict?

The one higher up in the Conditional Formatting Rules Manager. Use the arrows to move rules up or down.

Can I copy conditional formatting to other cells?

Yes, with Format Painter or Paste Special > Formats. For large ranges it is tidier to widen the Applies to range in Manage Rules, so you do not end up with duplicate rules.

Related guides

All 32 Excel guides