Aesthetics 3: Conditional Formatting

Ever wish you could tell at a glance who your top performers are? Now you can!

Conditional formatting is an exciting aspect of Excel that allows you to change the way your spreadsheet LOOKS, based on the DATA entered.

Let’s have a look by starting with some data:

Here we have 12 colleagues and the total number of calls they have made from January to June.

Now, we will colour-code all the numbers to find out the top 3 and the bottom 3 in terms of performance each month.

Step 1: Select the ‘Home’ tab and highlight all the values in the ‘Jan’ column, like so.

Step 2: Click on ‘Conditional Formatting’ and then select ‘Top/Bottom Rules’ and then ‘Top 10 items…’. Remember to ensure that all the values in the ‘Jan’ column are still highlighted.

Step 3: An option box will appear. In the field that indicates ‘Format cells that rank in the TOP:’, enter the value 3. This will mean that the conditional formatting will affect only the top 3 numbers. If you enter 5, it will then affect the top 5.

Step 4: You can select how you want the cells to be formatted by selecting one of the options in the dropdown box on the right. Here, we will choose ‘Light Red Fill with Dark Red Text’.