PRO TIP
Get Insight in Excel
Microsoft Excel
Spreadsheets and Data Analysis
Microsoft Excel can easily handle spreadsheets with huge data models and thousands of cells. But how do you analyze 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 dialogue that appears, click the plus-sign button to add a rule. This takes you to the New Formatting Rule dialogue; configure it as shown here:
In the “Format with” menu, choose Custom Format. To make things pop, choose a bold font on a yellow background with a red cell border. Click OK to save the rule, then click OK again to exit Manage Rules. 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 dialogue 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—making this tweak changes the Icon pop-up menu to Custom.)
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.