Blog

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.  

 

If you want to learn these and other advanced Excel techniques in greater detail, join our two-day Excel Analytics Essentials course.