Conditional Formatting in Excel: Highlight What Matters

Conditional formatting colors cells automatically when they meet a rule, like sales over 5,000 or a duplicate ID. You pick the cells, a condition and a color from Home > Conditional Formatting, and Excel keeps the colors up to date as the data changes. This conditional formatting Excel guide is for beginners, and it ends with the formula rules that color whole rows.

The examples use an order book in A1:E11, with the columns Order, Rep, Region, Units and Sales.

What is conditional formatting?#

It is formatting that depends on a cell's value. Each rule has three parts:

1. Which cells it checks, such as E2:E11 (Excel calls this Applies to).
2. A condition, a test like "greater than 5000".
3. A style, the fill and text color to use when the test is true.

Rules are live. If you color cells by hand, the colors go stale when a number changes. A rule checks again every time, so a cell turns red by itself the moment it passes 5,000.

How to highlight cells greater than a value#

The built-in Highlight Cells Rules need no formulas:

1. Select the sales figures, E2:E11.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
3. Type 5000 in the box (no $ or comma).
4. Pick a style, such as Light Red Fill with Dark Red Text, and click OK.

Four orders light up: 6,100, 5,300, 7,400 and 5,050. The same menu has Less Than, Between, Equal To, Text that Contains and A Date Occurring. Instead of typing 5000, you can click a cell that holds the limit, so changing that one cell moves the rule.

How to highlight duplicates#

Each order number should appear once, but one was typed twice. To find it:

1. Select A2:A11.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Keep Duplicate, choose Yellow Fill with Dark Yellow Text, and click OK.

Both cells with 1003 turn yellow. Excel colors every copy, including the first one, so you can decide which to fix. Switch the drop-down to Unique to color values that appear only once.

How to highlight the top 3 values#

Top/Bottom Rules color the largest or smallest values in a range:

1. Select the units, D2:D11.
2. Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
3. Change 10 to 3, choose Green Fill with Dark Green Text, and click OK.

The three biggest orders by units turn green: 61, 55 and 47. The same menu has Bottom 10 Items, Top 10% and Above Average.

Conditional formatting Excel formula rules: color a whole row#

A formula rule colors a cell when your formula returns TRUE. It is the way to color a whole row based on one column:

1. Select the whole table body, A2:E11.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type =$E2>5000.
5. Click Format, pick a fill color, and click OK twice.

The formula is written for the first cell of the selection, A2. Excel adjusts it for every other cell, just like a copied formula. That is why the $ matters:

Lock the column with $E, but not the row, so each row still checks its own sales. The same pattern flags small orders for a follow-up call: =$D2<20 colors the rows where Units is under 20. A range can have several rules at once. Formula rules can also point at cells on other worksheets of the same file; see how to reference another sheet in Excel.

Common mistakes with formula rules#

• The formula doesn't match the first selected cell. If you select from row 1 (the headers) but write the formula for row 2, every color lands one row off. Select A2:E11 and write the test for row 2.
• Missing quotes around text. To color North orders, write =$C2="North", with the text in double quotes.
• Typing a value as text. =$E2>"5000" compares with text, not a number. Leave numbers without quotes.
• A hard-coded limit. Put the limit in a cell, like H1, and use =$E2>$H$1. Lock both parts of H1, because every cell should check the same limit.

To test a rule before you add it, type the formula in a spare cell next to row 2. If it shows TRUE where you expect color, the rule will work.

Data bars, color scales and icon sets#

Three more rule types show size at a glance instead of yes-or-no:

• Data Bars draw a bar in each cell, longer for bigger numbers.
• Color Scales shade cells from one color to another, like red for low and green for high.
• Icon Sets add arrows, flags or traffic lights.

Find them under Home > Conditional Formatting. They work well on pivot table values too; see our pivot tables beginner's guide.

Manage, edit and clear rules#

All rules on a sheet are listed in Home > Conditional Formatting > Manage Rules. Choose This Worksheet in Show formatting rules for to see them all. From there you can:

• Edit Rule to change the condition, the style or the Applies to range.
• Delete Rule to remove one rule.
• Use the up and down arrows to change the order. When two rules color the same cell, the rule higher in the list wins.

To remove everything, use Clear Rules > Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

Colors are also useful with filters: Filter by Color shows only the highlighted rows, and a total with the SUBTOTAL function then adds up just those rows.

Practise it#

Formula rules are where most people get stuck, so it helps to see the $ mistake happen. ExcelReady's conditional formatting lesson uses the order book from this guide: highlight big sales, duplicates and the top 3, watch a rule react to a changed value, fix a coworker's broken row rule, and write your own $D2<20 rule, all in a live spreadsheet. Practise conditional formatting in the lesson.