Conditional formatting in Excel: highlight cells and rows with rules and formulas

Conditional formatting colors cells for you when they meet a rule. A sale over target turns green, an overdue invoice turns red, and a duplicate customer ID lights up before it ends up in a report. The data stays the same. Only the formatting changes, and it updates by itself every time the numbers do.

Most people start with the menu rules, and those cover a lot. The real power is a rule written as a formula. Then you can color a whole row from one column, compare against a limit in another cell, flag dates that have passed and catch combinations no menu offers. Below you start with the ready-made rules, then move to formula rules, the $ signs that make or break them, and the patterns you will use again and again.

What conditional formatting does

A conditional formatting rule has two parts: a condition and a format. Excel checks the condition for every cell in the range you selected. Where the condition is true, the cell gets the format: a fill color, a font color, a border or a number format. Where it is false, the cell keeps its normal look.

Because the rule is checked again every time the sheet recalculates, the colors are never out of date. Type a new sale, and it is colored or not the moment you press Enter. That is the big difference from coloring cells by hand, which is out of date the minute the data changes.

You find it all on the Home tab under Conditional Formatting.

The ready-made rules

Before you write a formula, check whether a menu rule already does the job. They are quick and easy for colleagues to understand.

• Highlight Cells Rules: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring and Duplicate Values. Each one colors the cells in the selection that match.
• Top/Bottom Rules: the top or bottom 10 items, the top or bottom 10%, and above or below average. You can change 10 to any number.
• Data Bars: a bar inside each cell, as long as its value. Good for comparing a column of numbers at a glance when a full chart would be too much.
• Color Scales: a fill from one color to another across the range, for example red for low and green for high.
• Icon Sets: arrows, traffic lights or flags in three to five groups.

A typical use: select the sales column, choose Highlight Cells Rules > Greater Than, type 10000 and pick a green fill. Done.

The menu rules have one limit that sends most people looking for formulas. They only color the cells they test. Ask a menu rule to color sales over 10,000, and it colors the sales cell, never the rep's name next to it. Want to color the whole row, test one column and color another, or combine two conditions? Then you need a formula rule.

Your first formula rule

A formula rule is a test that returns TRUE or FALSE. Excel colors the cell when the test is TRUE. That's all there is to it:

To use it as a rule:

1. Select the cells to color, starting with the top-left cell. Select from the top, not from the bottom.
2. Choose Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. In Format values where this formula is true, type the test, for example =B2>10000.
5. Click Format, pick a fill color and click OK twice.

The formula is written for the top-left cell of the selection. Excel then moves it to every other cell in the range, exactly the way a formula moves when you copy it. If you selected B2:B20, the rule for B3 reads =B3>10000, the one for B4 reads =B4>10000, and so on.

That is the whole secret, and also the source of nearly every rule that doesn't work. Think of the rule as a formula you type in the top-left cell and copy over the whole selection. If that copied formula would test the right cell everywhere, the rule works.

You don't need IF. A rule like =IF(B2>10000,"Yes","No") returns text, and text is never TRUE, so nothing turns color. The test on its own is enough. Practice exactly that in a rule is a test.

Highlight the whole row: the $ before the column

This is the question most people come to conditional formatting with: how do I color the whole row when one cell meets the condition?

Select the whole table without the headers, say A2:C20, starting from A2. Then write the test for row 2, with a $ before the column:

The $ locks the column, so A2, B2 and C2 all test C2. The row number has no $, so row 3 tests C3, row 4 tests C4, and so on. Every cell in a row asks the same question about the same sales figure, and the whole row turns the same color.

Leave out the $, and the test slides right along with the cell: A2 tests C2, B2 tests D2 and C2 tests E2. Only the first column is right. Lock both, $C$2, and every row tests the sales in row 2, so the whole table turns color at once or not at all. Here are the four ways to write it, as seen from a rule over A2:C20:

When you need the rule to color a single column from its own values, the plain =C2>10000 is fine. As soon as the rule colors more columns than the one it tests, put a $ before the column. Read more about the $ signs in absolute and relative references, and try it in highlight the whole row with $.

Conditional formatting based on another cell

Rules often depend on a number that changes: this month's target, a minimum stock level, a credit limit. Don't type it into the rule. Put it in a cell and point the rule at that cell with both $ signs:

Now the target lives in F1. Change it to 15,000, and the colors update across the whole table. Nobody has to open Manage Rules to find a number hidden inside a formula. Label the cell clearly, for example with "Target" in E1, so the next person knows what it does.

$F$1 needs both $ signs. Each cell in the range moves the rule a step right or down, and the limit must stay in F1 no matter where the rule is.

Dates: overdue, due soon and weekends

Dates are numbers in Excel, counted in days. That makes date rules simple: "before" is less than, "after" is greater than, and "within a week" is a plus 7. Put today's date in a cell, or use TODAY() straight in the rule.

An overdue deadline is a date before today:

In your own workbook, write =$B2<TODAY() and the rule always uses today's date. A deadline turns red the day after it passes, without anyone touching the file. Practice it in overdue deadlines.

"Due within the next 7 days" is two tests: on or after today, and no later than 7 days from now. AND puts them together:

With two rules on the same range, overdue in red and due soon in yellow, you have a deadline list that sorts itself visually.

Weekends are a WEEKDAY test. With 2 as the second argument, Monday is 1 and Sunday is 7, so anything over 5 is Saturday or Sunday:

That rule is handy in shift schedules, timesheets and calendars where weekends should stand out. The dates guide explains why dates are numbers and how WEEKDAY, EDATE and NETWORKDAYS work, and WEEKDAY has its own page with more examples.

The menu also has Highlight Cells Rules > A Date Occurring, with options like Yesterday, Today, Last 7 days and Next month. It is fine for a single date column, but it cannot color the whole row and it has no "overdue" option, so most deadline lists end up with a formula.

Two conditions: AND and OR

Real rules often have two conditions. An invoice is a problem when it is overdue and unpaid. A paid invoice from last month is fine, and so is an unpaid one that isn't due yet. AND returns TRUE only when every test inside it is TRUE:

When someone types Yes in the Paid column, the second test turns FALSE and the color disappears on its own. Try it in overdue and unpaid.

OR is the other half: one true test is enough. =OR($C2="Urgent",$D2>7) colors a ticket that is marked urgent or has been open for more than a week. The IF formulas guide covers AND and OR in more depth. Everything there works the same inside a rule, just without the IF.

Text: cells that contain a word

Highlight Cells Rules > Text that Contains colors the cells in the selection that contain a word. To color the whole row, or test a different column than the one you color, combine SEARCH with ISNUMBER:

SEARCH returns the position of "refund" inside the subject, or an error if the word isn't there. ISNUMBER turns a position into TRUE and an error into FALSE. SEARCH ignores upper and lower case, which is usually what you want. Use FIND if case matters.

For exact text, a plain comparison is enough: =$C2="Canceled". Excel ignores case here too, so CANCELED and canceled are also colored.

Blank cells and missing values

Empty cells in a list are often the thing you most need to see: an order with no ship date, a timesheet with a missing day, a customer without an email. A rule that tests for "" finds them:

The first test, $A2<>"", keeps the rule from coloring the empty rows below your data. Without it, every unused row in the range would light up. That's the most common surprise with blank rules.

$C2="" is TRUE both for a truly empty cell and for a formula that returns "". ISBLANK is TRUE only for a truly empty cell, so in a column full of formulas, the "" test is usually the right one.

Duplicates

Highlight Cells Rules > Duplicate Values colors every value that appears more than once in the selected column. It's the fastest way to check one column. For whole rows, or for a check that uses one column and colors the row, use COUNTIF:

COUNTIF counts how many times the ID in the current row appears in the whole list. More than 1 means a duplicate. The list must be locked with $ on both ends, so it stays the same for every cell, while $A2 moves down with the row.

Want to color only the second and later copies, so the first one stays plain? Let the list grow with the row: =COUNTIF($A$2:$A2,$A2)>1. In row 2 it looks only at A2, in row 10 at A2:A10. The first time an ID appears the count is 1, so only the repeats are colored. Practice the standard version in find duplicates, and see the clean data guide for how to remove duplicates once you've found them. COUNTIF has its own page with more examples.

The top 3, and other rankings

Top/Bottom Rules > Top 10 Items colors the largest values in a column. To color the whole row for the top 3, compare each value with the third-largest value in the list:

LARGE returns the third-largest sale. Every sale that is at least as large is in the top 3. Ties are included, so with two sales sharing third place, four rows are colored. SMALL does the same from the bottom, and =$B2>AVERAGE($B$2:$B$6) colors everything above average.

Banded rows

Striped rows make wide tables easier to read. MOD and ROW give you a rule that colors every other row:

ROW() returns the row number, and MOD(…,2) is 0 for even rows. The stripes stay correct when you sort or insert rows, because the rule looks at the row number, not at the data.

An Excel table (Ctrl+T) has banded rows built in, and they're usually the easier choice. The rule is useful when you can't or won't use a table, or when you want bands in groups, for example =MOD(ROW(),6)<3 for blocks of three.

Managing rules: order, conflicts and Applies to

Once a sheet has more than one rule, Home > Conditional Formatting > Manage Rules is where you keep control. Set Show formatting rules for to This Worksheet to see every rule on the sheet.

• Applies to is the range the rule covers. When a rule colors the wrong cells, check here first. The formula is always read from the top-left cell of this range.
• Order matters. Excel checks the rules from the top down. When two rules set the same format, for example both set a fill, the higher rule wins. Put the most important rule at the top with the arrow buttons.
• Stop If True stops Excel from checking the rules below once this one is TRUE. It's useful when a red "overdue" color should never be mixed with a yellow "due soon".
• Edit Rule opens the formula. Delete Rule removes it.

Copying and pasting cells copies their rules too. After a few weeks of pasting rows, a sheet can have the same rule twenty times, each with a small Applies to range. It works, but it slows the file down and makes it hard to change. Clean it up by deleting the copies and setting Applies to on one rule to cover the whole table, for example =$A$2:$D$500.

To remove everything, use Clear Rules > Clear Rules from Entire Sheet. The Format Painter also copies conditional formatting, which is handy when you want a new column to follow the same rule.

Mistakes that keep a rule from working

Most broken rules come down to a handful of mistakes:

• The wrong $ signs. Color more columns than you test? Then put a $ before the column. Use a limit in another cell? Then put $ on both column and row.
• Written for the wrong row. The formula must be written for the top-left cell of Applies to. If the range starts in row 2 and the formula says =$C1>10000, every row looks one row up.
• Selecting from the bottom. If you drag from A20 up to A2, the active cell is A20, and Excel reads your formula as written for row 20.
• A formula that returns text. IF(…,"Yes","No") never colors anything. The rule needs TRUE or FALSE.
• Numbers stored as text. Imported numbers often sit in cells as text. Text is never greater than 10,000, so the rule stays off. Convert them to numbers first.
• Blank rows that light up. A rule like =$C2="" colors every empty row below the data. Add a test that the row has data.
• Rules multiplied by pasting. Check Manage Rules now and then, and merge the copies.

Conditional formatting in Google Sheets

Google Sheets has the same feature under Format > Conditional formatting. Pick the range, and under Format rules choose Custom formula is. The formula works exactly as in Excel: it is written for the first cell of the range, it must return TRUE or FALSE, and the $ signs decide what moves. =$C2>10000 colors whole rows in Sheets too. Sheets also has color scales, but no data bars or icon sets.

Conditional formatting in Excel tests

Employer Excel tests like to include a conditional formatting task, because it shows whether you understand references. The usual forms are "highlight sales over target", "color the rows where the status is Overdue" and "mark duplicates". Read whether the task asks you to color the cell or the whole row. That decides whether you need the $ before the column. Then check two rows, one that should be colored and one that shouldn't, before you move on.

In short

Conditional formatting colors cells when a condition is TRUE and updates by itself. Start with the ready-made rules when they fit. Switch to a formula rule when you want to color whole rows, compare with another cell or combine conditions. The formula is a test written for the top-left cell of the selection, and the $ signs decide what moves when Excel applies it to the other cells: a $ before the column colors the whole row, and $ on both parts locks a fixed cell.

The fastest way to get it into your fingers is to write the rules yourself. The five exercises below take you from a single test to whole rows, overdue dates, two conditions and duplicates. Try the first one for free and write the rule yourself.