Support: 024 7668 7042

General: 024 7668 7333

HomeBlogTips and TricksExcel - Sparklines Introduction

Excel - Sparklines Introduction

  • Print
  • E-mail

The spreadsheet is the one of first applications ever developed that made a computer a serious business tool. Used correctly it can enable to us to control complex sets of calculations and enable us to make important business decisions based on the information.

The latest version of Excel has a new feature that enables to quickly visualise data trends where we would have used a chart in the past.

So let's imagine I'm the owner at Widgets Ltd. I've got ten different types of widget and every month I record how many widgets I've managed to sell.

Image 1 - data

This table contains tons of information about my business but it's locked away as at first glance I have no idea how any of products are performing. I could could study the table and try and find some trends but I've got a computer to do that for me!

The obvious solution is to use a chart so we'll select the table and click the insert chart button 

Image 2 - Chart

We now have a chart that shows us our product sales trends!

Unfortunately the chart is so busy it's not an awful lot of use.

This is where sparklines can help!

So once again I'm going to select my table but this time on the insert tab I'm going to select a line based Sparkline

Image 3 - insert spark line

Excel will then ask my where I want to put my Sparklines so I'll put them at the end of the table (I could put them anywhere, next to the product name for example)

Image 4 - insert spark line

We now get our Sparklines at the end of the table.

Image 5 - spark lines

At a glance we can see the trends associated with each product.

I'll just set a couple of options to make this a bit more useful.

First I'm going to change the vertical axis so they are the same across all our SparkLines. This is so that we can see how each product performs compared the others. (note the difference between the two sparklines for the "Round Widget" as an example)

Image 6 - axis options

Next I'm going to add a bit of colour and add some marks to show the high and low points.

Image 7 - marker options

Our completed table looks like this

Image 8 - final table

Looking at some of the trends,

Our "Wiz Bang Widget" has shown a steady decline through out the year. The "Round Widget" remained generally steady while the "Big Widget" showed huge growth.

As well as the line based Sparkline we can also have a traditional bar graph

Image 9 - bar graph

and a win/lose graph which is used to analyse "binary" style data such as yes/no or profit vs loss

 This final table shows examples of all three

A catering company wants records the last five events they ran, what it cost, how much they were able to bill the client and what the difference was (profit/loss)

Image 10 - all types

The three different sparkline types are used to visual what happened

The trend spark simply shows if we made a profit over time

The bar/column type shows which event was the most profitable (green), which events we lost money on and how we performed on the others (the length of the bar)

Finally the Win/Loss type shows us which events were profitable and which events were not.

I hope this introduction to Sparklines was useful.

If you'd like to know more about Office 2010 please get in touch