Applying conditional formatting allows you to change the format of the cells in your spreadsheet, depending on certain conditions. For example, you could highlight cells in a particular colour when the numbers in those cells match a certain value. This is set using a type of conditional formatting, “Highlight Cells Rules”.
Types of conditional formatting
Conditional formatting can be found on the Home tab of the ribbon. If you click on the conditional formatting button, you can then choose the type of conditional formatting to apply:
- Highlight Cells Rules
- Top/Bottom Rules
- Data Bars
- Colour Scales
- Icon Sets
- New Rule (create custom rules)
Highlight Cells Rules
Looks for cells containing data (numbers, text or dates) that you want to apply formatting, based on criteria, or can be used to find duplicate values
Numbers – Use the ‘Greater Than’, ‘Less Than’, ‘Between’ or ‘Equal To’ options to set the required values.
Text – Use the ‘Text That Contains’ option to type in required text.
Dates – Use the ‘A Date Occurring’ option to set a date comparison to the current date e.g. Yesterday or Next Week. Note; to set a specific date or an alternative comparison, you will need to set a custom rule (see ‘New Rule’ below)
Duplicates – Use the ‘Duplicate Values’ option to highlight all of the duplicate cell values in the selected range of cells.
Looks for highest and lowest values based on an amount, percentage or variance from average.
Applies formatting to all selected cells. The longer the bar, the higher the value.
This example uses the blue coloured bars to visually display the size of the cell values.
Applies formatting to all selected cells. Certain colours will represent higher values than other colours.
The Green-Yellow-Red option can be used to graphically display the size of the cell values e.g. Red is used to highlight the low values, going up to orange, then yellow, light green the dark green to display the highest values.
Applies formatting to all selected cells. Each icon set has between 3 and 5 categories, each representing low, middle or high values.
An example is the 3 arrow icon set. The red down arrow representing the lowest values, the orange right arrow for the middle values and the green up arrows for the highest values.
You can create customised rules, rather than using the pre-set rules, so you can use different colours/formatting options and also use formulas for your criteria.
Example – using a formula to highlight dates which are more than 1 week ago (where A2 is the first date):
- Go to Home tab >Conditional Formatting
- Select ‘Use a formula to determine…’
- Type: =A2
- Click on Format
- Select required formatting, then OK
The formula must always start with ‘=’. The cell reference ‘A2’ is the first cell in the selected range. ‘Today()-7’ represents today’s date less 7 days i.e. more than 1 week ago. If you click on the first cell in the range rather than typing the cell reference (in this example ‘A2’), it will insert ‘$’ symbols in the cell reference to read ‘$A$2’. It will only then compare the data in that one cell, rather than comparing each cell in the range.