The range of possibilities of conditional formatting has been significantly expanded since Excel 2010. Conditional formatting helps you view important data at a glance and makes your data easier to read. It is one of the most powerful and important functionalities in Excel.
We present an overview of a few of the possibilities:
1. Highlight empty cells in less than 10 seconds
If you receive a file or download it from a database, it is sometimes necessary to conduct several basic checks on the data. One of these checks is to find and highlight empty cells. There are many reasons for empty cells to occur in a dataset:
– Data are not available
– A formula results in an empty cell
If you have a large dataset with hundreds of rows and columns, manually checking it would be inefficient and prone to errors.
There are two ways to highlight empty cells:
– Conditional formatting (dynamic)
– Go to (static)
Step by step
– Select the data
– Go to the ‘Home’ tab in the ribbon, choose ‘Conditional Formatting’, and choose ‘New Rule’ in the dropdown list
– Select the option ‘Format only cells that contain’ in the dialog box.
– Select ‘Blanks’ in the dropdown list
– In ‘Format’, choose the format or colour that you want to use to highlight the blank cells. In our example we have chosen green.
– Click on OK. This will highlight in green all the empty cells in the dataset.
Be aware that conditional formatting is dynamic. This means that if you clear a cell, that cell will automatically be highlighted.
Step by step
– Select the data.
– Press the F5 key. This opens the ‘Go To’ dialog box.
– Click on the ‘Special’ button in the dialog box.
– Select Blanks in the ‘Go to special’ dialog box.
– Click OK. This selects all the empty cells in the selected list.
– When all the empty cells have been selected, highlight them by giving them a cell colour via the ‘Fill’ option in Font.
As indicated, this method is handy if you want to quickly select and highlight all the empty cells. You can also use the same steps to select all the empty cells and then enter 0, NA or other relevant text.
Note that this method, unlike conditional formatting, is not dynamic. If you do this and then accidentally delete a data point, the cell will not be highlighted.
2. Highlight negative numbers in red
If you use many numbers in Excel, it is a good practice to highlight negative numbers in red. This makes it easier to read the data. There are various techniques you can use to highlight negative numbers in red in Excel:
– Use conditional formatting
– Use the standard number format
– Use a modified number format
Here we opt to illustrate the first technique.
Step by step
– Select the cells in which you want to highlight the negative numbers in red.
– Go to the ‘Home’ tab in the ribbon, choose ‘Conditional Formatting’, and choose ‘Highlight Cells Rules’ and then ‘Less Than’ in the dropdown list.
– Enter the value below which the format should be applied in the dialog box. If you want to use formatting other than that proposed (red letters, red shading), choose another option in the dropdown list.
– Click OK. All the cells with a value less than 0 are highlighted in a light red colour with dark red text inside.
3. How to add a checkmark ✓ symbol in Excel
You can use conditional formatting to insert a checkmark or cross on the basis of the cell value. Suppose that you have a list like that used in our example and you want to insert a checkmark if the turnover is greater than €50 000 and a cross if it is less than €50 000.
Step by step
– Enter in cell C2 = B2 and then copy this formula to the rest of the cells. This ensures that you now have the same value in the adjacent cell. If you change the value in column B, it is automatically changed in column C.
– Select all the cells in column C (in which you want to insert the checkmark).
– Go to the ‘Home’ tab, choose ‘Conditional Formatting’, and choose ‘New Rule’ in the dropdown list.
– Click on the ‘Format Style’ dropdown menu in the ‘New Formatting Rule’ dialog box and click on ‘Icon Sets’.
– In the ‘Icon Style’ dropdown list, select the style with the checkmark and cross symbol.
– Tick the ‘Show Icon only’ checkbox. This ensures that the symbols alone are visible and the figures are hidden.
– Change the ‘Percent’ to ‘Number’ in type, and make the settings shown again below.
– Click OK.
The steps above will insert a green checkmark when the value is greater than or equal to 50 000 and a red cross when the value is below 50 000.
In this case I have only used these two icons, but you can also use the yellow exclamation point if you want.
4. Hiding zero values
Conditional formatting is most often used for this. However, there are also alternatives to this (such as changing the Excel settings so that all zero values are hidden). But adjusting the settings often has drawbacks:
– If you send the file to someone else who has not changed his/her Excel settings, they see the zero values.
– You cannot decide whether you want to hide only the zeros in certain parts of your workbook or spreadsheets. It’s all or nothing.
Fortunately, there is a simple alternative via conditional formatting.
Step by step
– Select the range in which you want to hide the zero values.
– Go to the ‘Home’ tab in the ribbon, choose ‘Conditional Formatting’, and choose ‘Equal To’ in the picklist.
– Put 0 in the conditions in the dialog box, and choose ‘Custom Format’ in the dropdown list.
– In the ‘Format Cells’ dialog box, go to the ‘Font’ tab and choose the colour white.
– Now all the zeros disappear from the list, because the font colour of all the zeros is actually white. Now you can do calculations and operations with the list without problems, without the zero values being displayed.