You can create advanced, richly informative, and highly functional charts like sparklines and bullet graphs like below to track your company’s performance. You probably didn’t realize you could create these in Excel. I will show you how in this blog post.
Sparklines
First let’s talk about sparklines. Sparklines are small charts minimally laid-out without x- and y-axes and without labels, displayed alongside text or other chart elements, in order to provide context to metrics. The term sparkline was coined by data visualization thought leader Edward Tufte to refer to “data-intense, design-simple, word-sized graphics”. Sparklines have been available in Excel starting in version 2010, but very few Excel users have been taking advantage of this feature.
Bulletgraphs
A bullet graph, on the other hand, is a chart type that visualizes a numeric target, an actual value, and qualitative zones denoting good, satisfactory, and poor performance. They were invented by another data visualization thought leader, Stephen Few, as an apparent replacement to ineffective gauges and meters found in many dashboards. Bullet graphs are not a standard feature in Excel, but in this blog post, I will teach you how to create it using the BFEM, or the Brute Force Excel Method.
How to use sparklines and bullet graphs to visualize company performance
Let’s say your company’s most important key performance indicators or KPIs are Revenue, Profit, New Customers, Customer Satisfaction, and Market Share, in that order. Your dashboard could look like this.
The sparklines on the left show your company’s performance in each KPI for the past, say, 12 periods.
Each KPI has a bullet graph where the current period’s actual is represented as the black horizontal bar in the middle, and the target is shown as a vertical line. So you can clearly see which KPIs or metrics are not reaching their targets–and that’s Revenue, New Customers, Customer Satisfaction, and Market Share. You can also clearly see which KPI or metric is exceeding its target–and that’s Profit.
The dark gray shading shows the zone of poor performance. Medium gray shading denotes the zone of satisfactory performance. And the white zone denotes good performance. In this example, Revenue, Profit, and Customer Satisfaction are good, Market Share is satisfactory, and New Customers is poor.
You will also see a red dot on those KPIs that are not reaching the good zone, in this case New Customers and Market Share.
Finally you also see the current period’s actual values on the right.
How to create sparklines in Excel
Now that you know how to use sparklines and bullet graphs, let’s show you how to create them in Excel. I’m using Excel on an Office 365 license right now, but you can also do this in version 2010 or 2013.
The first step is to prepare your data. For your sparklines, simply create a table where you list each KPI or metric’s percentage achievement of target for the past 12 months. Next, simply go to the cells where you want to insert sparklines and go to the Insert – Sparkline menu.
How to create bullet graphs in Excel
Now that you got your sparklines, let us now create the bullet graphs. These bullet graphs are actually horizontal bar charts of varying lengths, overlapping one another.
First, prepare your data in the format below. List your KPIs or metrics from least important to most important, and their targets for the current period, their actuals, the percentage achievement of the target, and the thresholds for satisfactory and poor performance.
Then create horizontal bar charts, make them overlap one another, put the Actual bar on a secondary axis, then sort them in the Select Data Source window as follows: Target, % Achievement, Satisfactory %, and Poor %.
Then using conditional formatting, you can make red dots appear beside a metric whenever it’s % Achievement falls within the Satisfactory Level
It’s not the tool, it’s what you do with the tool
There you have it. You’ve just created sparklines and bullet graphs in Excel to report and track your company’s performance, using little-known Excel features, horizontal bar charts, conditional formatting, and the Brute Force Excel Method.
