Excel: conditional formatting basics
Conditional formatting changes how a cell looks based on what is in it. You can make overdue dates turn red, highlight sales over a target, or add small bars to show how big numbers are. It updates itself as the numbers change.
Highlight cells above a number
- Select the cells you want to check, for example a column of sales.
- Click the Home tab, then Conditional Formatting.
- Choose Highlight Cells Rules, then Greater Than.
- Type the number, such as 1000, and choose a colour from the drop-down list.
- Click OK.
Any cell above 1000 now changes colour. Other choices in the same menu include Less Than, Between, Equal To, Text that Contains and A Date Occurring.
Find the biggest or smallest
Choose Top/Bottom Rules and pick something like Top 10 Items or Below Average. You can change the 10 to any number.
Data bars and colour scales
- Data Bars put a coloured bar in each cell, longer for bigger numbers.
- Color Scales shade cells from one colour to another, like a heat map.
- Icon Sets add traffic lights or arrows.
Pick one from the menu and see how it looks. If you do not like it, you can undo it with Ctrl+Z.
Colour a whole row
This needs a formula, so it is a little harder. Select the rows, choose Conditional Formatting, then New Rule, then Use a formula to determine which cells to format. For example =$D2="Overdue" colours every row where column D says Overdue. The dollar sign locks the column.
Edit or remove rules
Click Conditional Formatting, then Manage Rules. Choose to show rules for This Worksheet and you can edit or delete any of them. To remove all rules, choose Clear Rules, then Clear Rules from Entire Sheet.
Common problems
- Colours stay when the cell no longer qualifies: the colour may have been added by hand rather than by a rule.
Mac users find the same options on the Home tab.
When to get more help
If a spreadsheet has become slow or full of conflicting rules, get in touch and we can tidy it up for you.