Sparklines and Bullet Graphs in Excel: How to Make Advanced Charts to Track Your Company’s Performance
×

Browse Story IQ courses specific to your location

Inquire Now

Sparklines and Bullet Graphs in Excel: How to Make Advanced Charts to Track Your Company’s Performance

You can create advanced, richly informative, and highly functional charts like sparklines and bullet graphs likebelowto track your companys performance. You probably didnt realize you could create these in Excel.Iwillshow you howin this blog post. 

 

Sparklines

First let’s talk about sparklines.Sparklines are small chartsminimally 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 termsparklinewas coined by data visualization thought leaderEdward Tufteto 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

Abullet 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 metersfound in many dashboards. Bullet graphs are not a standard feature in Excel, but in thisblog post, I will teach you how to create it using theBFEM, or theBrute Force Excel Method. 

 

 

How to use sparklines and bullet graphs to visualize company performance

Let’s say your company’s most importantkey performance indicators or KPIsareRevenue, Profit, New Customers, Customer Satisfaction, and Market Share, in that order. Your dashboard could look like this. 

 

The sparklines on the left show your companys 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 Excelon an Office 365 licenseright 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 chartsof varying lengths,overlapping one another.

 
First, prepare your data inthe 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.Youvejust 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.

"It's not the tool, it's what you do with the tool"