Top Nav

How-To Use Sparklines Mini-Charts in Excel 2010

imageExcel 2010 introduces us to a groovy new feature called Sparklines.  A Sparkline is essentially just a tiny chart that sits in a single cell of Excel.  The charts are drawn using basic Excel functions and once you know how to use them they are an incredibly efficient way of communicating a lot of information in a single cell of Microsoft Excel.  Being built into Excel 2010 is exciting because formerly you were required to use 3rd party software to get the same effect.  

 

Article Map

  1. How To Create Sparklines in Excel 2010
  2. How To Customize Excel 2010 Sparkline Charts
  3. What is Win/Loss Sparklines and How do I use them?

Sparklines have been integrated right into Excel 2010! They are REALLY effective for communicating a ton of data in a single cell.  Let’s take a look.

 

First, you’ll need an Excel 2010 spreadsheet with some data on it that you want to map out.

World Gas Price comparison chart in Excel 2010

How To Create Sparklines in Excel 2010

Step 1

In Excel 2010, Select the cell(s) where you want your Sparkline(s) to be. Remember that for each row or column of data you’ll only be able to use one cell.

One Row or Column = 1 Cell for Sparklines

In the example, below I’m going to create three, so I’ve used three cells.

Select the cells for your excel 2010 sparklines

Step 2

Next, Click the Insert ribbon and Select a Sparkline type.  I chose the Line style.

sparklines in the excel 2010 ribbon

Step 3

Now all that is left to do is Select the cells that contain the data you want to be graphed by Sparklines.  You can also manually enter the coordinates, but it’s entirely optional.  Once you’ve done that, Click OK.

select the data range for your excel 2010 sparkline

Step 4

Now using the Design tab on the ribbon, you can completely customize and “pimp out” your Sparklines to match whatever visual style you need.  For Lines, Markers tend to be particularly helpful.

customize your excel 2010 sparklines

Step 5

To customize the Axis option under the Design ribbon.  Here you can set a lot of groovy features that will help make your Sparklines look even more professional.

change the sparklines axis in excel 2010

 

You can also decide if you want to highlight certain pieces of information regarding your Sparkline such as the high points or low points.  This highlight makes it even easier to pick out vital information from a single cell.  Add a little color, and you can really impress those in your audience!

How-To Customize an Excel 2010 Sparkline Chart with Color

Step 1

Click a Sparkline cell, Click Design Tab, then Select the additional points of data to highlight.

How-to select what features are used on Excel 2010 Sparklines

 

Now let’s change the Color a bit of our Sparkline Trending Line

Step 2

Click the Sparkline Cell, Click Design Tab, Click Marker Color, Click High Point (or whatever you want) and Click the Color.

How-To Change the Colors to Excel 2010 Sparklines

 

In my example, you can see I changed the top point of my trending Sparkline to green to show that was my best month for sales.  I then selected the low point and changed it to red for added emphasis.  The result gives me an excellent summary that my executive team can just see at a glance.

Excel 2010 Sparkline Example

 

Now you can add color to Sparkline Trend or Lines, Columns and even the Win/Loss area.  Let’s take a look at the Win/Loss portion of Sparklines.

What is Win/Loss Sparklines and How do I use them?

Excel 2010 Win / Loss Sparkline Example

Simply put, the Win/Loss Sparkline displays Profit Vs. Loss or Positive Vs. Negative.  You can also customize the look of the Win/Loss Sparkline by following steps 6 and 7 above.

In my example above, my Win/Loss Sparkline Cell is showing 6 months of Sales data and comparing it with the Goal for each month.  My DIFF column is a comparison of how my sales compared to my goals.  Then in the Win/Loss Sparkline, I configured it to show the Top and Bottom months and changed the colors to Red (bad) and Black (Good) and Darker Red for Worst and Green for Best.

 

This scenario is just an example of some of the things you can do with the new Sparkline Feature in Excel 2010.  At work, I’ve already created some cool Sparklines with the plan to run my entire department from a single sheet in Excel!  I’ll post it later in the forum once I have it ready.  If anyone else out there has any cool example of Sparklines, please post them up in our forum or drop a note in the comments with a link!

 

Now granted, I realize not everyone has upgraded to Office 2010 yet so if you’re still running Excel 2003 or Excel 2007, here are a few alternatives you should check out if you want to play around with Sparklines. My recommendation, however – go get a copy of Office 2010.  ; )

More Reading:

, , ,

4 Responses to How-To Use Sparklines Mini-Charts in Excel 2010

  1. Oscar Gonzalez May 21, 2010 at 7:00 pm #

    Nice write up and great tips. Thanks. Haven’t used MS Office in a while, I might check it out with this feature.

    • MrGroove May 21, 2010 at 8:29 pm #

      Yo! Oscar NotAGrouch!

      I’m glad you like the tutorial! Sparklines are pretty sweet! One can definitely waste a few hours playing with those and they are REALLY useful for sure!

  2. Peter Majgaard August 13, 2013 at 12:37 am #

    Dear Steve,

    Did you ever manage to prepare the sheet for running your department with the assistance of sparklines and micro graphs?

    If so, have you posted it anywhere?

    I would love to have a look as I am trying to prepare a similar dashboard for my business.

    Yours sincerely
    Peter M.

  3. Lisa March 2, 2015 at 12:08 pm #

    When creating sparklines, I have run into two obstacles.

    1. I am wanting to graph a trendline for 2 different revenue trends together to show the differences, is that possible in a sparkline?

    2. In a win/loss, I need to set the parameter of “loss” at 100%. So in demonstrating quota attainment, anything below 100% is a loss and anything above 100% is a win (above quota).

    Any help would be greatly appreciated.

Leave a Reply