How to highlight cells with conditional formatting

Steps in Excel for Windows

Shortcut: Alt, H, L

1. Select the cells to format. Drag across the column of numbers, for example B2:B5. Leave the header out so it is not tested as data.
2. Open Conditional Formatting. On the Home tab, in the Styles group, click Conditional Formatting. The keyboard route is Alt, H, L.
3. Choose a ready-made rule. Point to Highlight Cells Rules, then pick Greater Than. Other choices include Less Than, Between, Equal To, Text that Contains and Duplicate Values.
4. Enter the value and the look. Type 500 in the box, pick a preset such as Light Red Fill with Dark Red Text, and click OK. The format appears at once and updates whenever a value changes.
5. For a whole row, use a formula rule. Select the whole table body, choose Conditional Formatting, New Rule, then Use a formula to determine which cells to format. Enter =$B2>500, click Format to choose a fill, and click OK.
6. Review or remove rules. Conditional Formatting, Manage Rules lists every rule for the selection or the sheet. Clear Rules removes them from the selected cells or the whole sheet.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

Common mistakes

• Forgetting the dollar sign in a row rule. Without $ in front of the column letter, a formula such as =B2>500 shifts to C2, D2 and so on as it moves across, and the highlighting stops lining up with the data.
• Writing the formula for the wrong cell. A formula rule is relative to the active cell when you open the dialog. If the selection starts at A2 but you wrote the formula as if you were in A1, every row is off by one.
• Numbers stored as text. A number saved as text is not compared as a number, so a Greater Than rule can miss cells that qualify or catch ones that do not. Left-aligned figures in a numeric column are the sign. Convert them first.
• Stacking rules that fight. Rules are applied in the order listed in Manage Rules. A higher rule can override the fill from a lower one, so review the order when two rules touch the same cells.
• Coloring cells by hand. A fill applied from the Home tab is permanent and will not change when the data does. A rule updates automatically, which is almost always what a task wants.

On Excel job tests

Related guides

• COUNTIFCounts the cells in a range that meet one condition.
• IFReturns one value if a condition is true and another if it is false.
• ANDReturns TRUE only if every condition is true.
• filter data by a condition in ExcelShow only the rows that meet a condition, such as region equals East or amount over 500, with the Filter button or the FILTER function.
• remove duplicates in ExcelDelete repeated rows with the Remove Duplicates command, choose which columns decide what counts as a repeat, and check the result before you trust it.

Questions

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

Select the whole table, add a New Rule using a formula, and lock the column with a dollar sign, for example =$C2="Late". Do not lock the row number.

How do I remove conditional formatting?

Select the cells, then choose Conditional Formatting, Clear Rules, Clear Rules from Selected Cells. Clear Rules from Entire Sheet removes every rule.

Does conditional formatting change the values?

No. Only the appearance changes. Sorting and filtering by color are available from the filter menu.

What is the keyboard shortcut for conditional formatting?

On Windows, press Alt, H, L to open the Conditional Formatting menu from the Home tab.