Whilst most of my software-specific consulting activities revolve around the luscious SharePoint, I’m always on the lookout to bring Excel to the party. Excel 2007 introduces some new visualization capabilities to make it easier and quicker to analyze large data sets without requiring specialist tools. If you haven’t seen what Excel can do, here’s a brief overview of some of the new features. They can be found on the Home tab in the ribbon – Conditional Formatting.
A simple example – some data about product sales. Here’s a traditional approach: a table of data and a chart visualising the results.
Now let’s see what we can do with Excel 2007.
Data bars are great for showing quick comparisons between data without needing to create a separate bar chart. In the example above, we can see that we are selling more nuggets than anything else despite the budget suggesting widgets would be the best seller. To set up data bars, simply select the columns and, from the Home tab in the Office ribbon, select Conditional Formatting and choose Data Bars. Then select the color you want to use.
Icon sets are key performance indicators (KPIs). Great for showing if the data represents good, bad or indifferent results.
Tip: The default behavior for icon sets is to display green when the value is above 66%, amber when it is above 33% otherwise red. In just about every case, you will want to change the behaviour. To do so, in the ribbon go to Conditional Formatting and select Manage Rules. For the icon set you want to modify, click Edit rules and make your changes. In this example, I want a green light if the sales variance is 1 or above (i.e. met or exceeded target), amber if sales are within 20% of budget, otherwise it’s a red light for the sales team.
Icon sets have two major shortcomings. First, there are only 3 indicators of performance. In the above example, widget sales are far closer to hitting the amber mark than gizmos, and gadgets only just reached amber. An alternative feature to use is called ‘Color Scales’. Not as pretty, but gives a better range of indicators:
Here you can see gadgets are amber, but widgets are nearly there too. Gizmos are deep red – beyond saving.
Back to relying on charts
The second shortcoming with icon sets as key performance indicators is that they give no indication about trends. Sales of nuggets look great, but is it expected or a surprise? In the image below, I have create line charts for each product and stripped away all the chart gumpf to convert them into ‘spark lines’. Now I can see that widgets have had a sudden drop whilst nuggets have had a sudden spike. And gizmos seem to be heading to oblivion.
Compare this information to the original example – It contains exactly the same data but you can instantly interpret a lot more from the information and it takes up less screen space.
For more information about designing useful visualisations, including spark lines and bullet graphs, see previous blog post: Designing Dashboards
Related blog posts:
Filed in library under: Microsoft Office; Microsoft BI
Technorati tags: Excel