Quick Excel Tip of the Day: Conditional Formatting

Hi Everyone, 

Looking to move your Excel reports from impressive to brilliant? Follow these quick tips that separate the experts from the rookies.

Have you ever seen an excel report that has trending arrows or colour coding that make the data look like a heat map? Its brings a whole new dimension to communicating analytics and it's actually much easier than you may think. People that don't know how to do this will think you are a whiz and it only takes an extra 30 seconds.

Here's how to do it:

For example, let's say I have 10 rows of sales data and I want to format low performers compared to high performers.

Excel 2003

  1. Highlight more than one cell of numerical data
  2. Click Format in the main tool bar
  3. Select the option Conditional Formatting
  4. For the conditions, enter greater than $100,000 and format cells to green and select OK
  5. Repeat steps 2-4 but enter the conditions less than $10,000 and format to red and select OK
  6. The stronger selling records are now highlighted in green and the lower selling records are now in red

Excel 2007 and Beyond

  1. Highlight more than one cell of numerical data
  2. On the Home tab of the Ribbon (large row of icons at the top of the new excel versions), select the Conditional Formatting drop down menu
  3. Select colour scales and choose any of the options within the color scales menu
  4. The data is now dynamically set to a color that depends on how widespread your data - or sales records are. So if they are very close the numbers will be slighly different shades and if they are very different the numbers will be also very different bold colours. 

I encourage using these in reports where the data is already sorted and adding colour draws people's attention to other conclusions. So for example, if you had already sorted your sales team by sales performance and then wanted to highlight the fact that the best performers were from District 5, you could easily sort by sales and conditional format the district. 

The conditional formatting options within Excel 2007 are pretty extensive so for now just experiment with the options and I will review them further in my next post.

Thanks and talk to you tomorrow