Format like a pro in Excel

Use conditional formatting to gain insights.

Microsoft Excel

Turn data into insights


Microsoft Excel can easily handle spreadsheets with huge data models and thousands of cells. But how do you analyse all that data? The app offers an often-overlooked tool for just that: conditional formatting, which automatically changes the appearance of a cell based on its value, making the most important info stand out.

Consider this simple spreadsheet showing the points per game for a 15-game basketball season:

What if you wanted to highlight the top three scores in the data set? You could look through all the cells, keeping track of the highest values then manually highlight those cells. But conditional formatting can do the work for you.

First select the data section of the table (cells C4 to Q12) and choose Format > Conditional Formatting. In the Manage Rules dialog that appears, click the + button to add a rule. This takes you to the New Formatting Rule dialog, which we filled in as shown:

After you choose “Custom Format” in the “Format with” menu, the Format Cells window pops up. Let’s bold the font, create a red border and fill the background with yellow – this will make the data pop. Click OK to save the rule, then click OK again to exit. The worksheet now looks like this, with the top scorers clearly called out:

What if you instead want to call out anyone who scored 19 or more points, as well as those who scored zero or one? Go back to the New Formatting Rule dialog and choose Icon Sets for style, then configure the options as shown below. (Notice that we’ve chosen no icon for anyone who scored two to 18 points, as we don’t want to call out those scores.)

Save this rule (with the previous formatting rule removed) and here’s how the data looks:

Now it’s easy to see not only the top performers, but also those who may need some additional coaching – at least on the scoring side. And if you update any cells, the conditional formatting is automatically applied to the new data.

This is a very simple example of what you can do with conditional formatting, but the next time you’re buried in data, it may make finding the answers you seek a lot easier.

    Microsoft Office 365