How to use sparklines in excel 2010

Andrew Gehman
How to use sparklines in excel 2010Andrew Gehman
Writer

One of the cool features of Excel 2010 is the addition of Sparklines. A Sparkline is basically a little chart displayed in a cell representing your selected data set that allows you to quickly and easily spot trends at a glance.

Inserting Sparklines on your Spreadsheet

You will find the Sparklines group located on the Insert tab.

How to use sparklines in excel 2010

Select the cell or cells where you wish to display your Sparklines.

How to use sparklines in excel 2010

Select the type of Sparkline you’d like to add to your spreadsheet. You’ll notice there are three types of Sparklines, Line, Column, and Win/Loss. We’ll select Line for our example.

How to use sparklines in excel 2010

A Create Sparklines pops up and will prompt you to enter a Data Range you are using to create the Sparklines. You’ll notice that the location range (the range where the Sparklines will appear) is already filled in.

You can type in the data range manually, or click and drag with your mouse across to select the data range. This will auto-fill the data range for you. Click OK when you are finished.

How to use sparklines in excel 2010

You will see your Sparklines appear in the desired cells.

How to use sparklines in excel 2010

Customizing Sparklines

Select the one of more of the Sparklines to reveal the Design tab. You can display certain value points like high and low points, negative points, and first and last points by selecting the corresponding options from the Show group. You can also mark all value points by selecting Markers.

How to use sparklines in excel 2010

Select your desired Sparklines and click one of the included styles from the Style group on the Design tab.

How to use sparklines in excel 2010

Click the down arrow on the lower right corner of the box to display additional pre-defined styles…

How to use sparklines in excel 2010

or select Sparkline Color or Marker Color options to fully customize your Sparklines.

How to use sparklines in excel 2010

The Axis options allow additional options such as Date Axis Type, Plotting Data Left to Right, and displaying an axis point to represent the zero line in your data with Show Axis.

How to use sparklines in excel 2010

Column Sparklines

Column Sparklines display your data in individual columns as opposed to the Line view we’ve been using for our examples.

How to use sparklines in excel 2010

Win/Loss Sparklines

Win/Loss shows a basic positive or negative representation of your data set.

How to use sparklines in excel 2010

You can easily switch between different Sparkline types by simply selecting the current cells (individually or the entire group), and then clicking the desired type on the Design tab.

How to use sparklines in excel 2010

For those that may be more visually oriented, Sparklines can be a wonderful addition to any spreadsheet.

Are you just getting started with Office 2010? Check out some of our other great Excel posts such as how to copy worksheets, print only selected areas of a spreadsheet, and how to share data with Excel in Office 2010.

A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Position a sparkline near its data for greatest impact.

How to use sparklines in excel 2010

Add a Sparkline

Select a blank cell at the end of a row of data.

Select Insert and pick Sparkline type, like Line, or Column.

Select cells in the row and OK in menu.

More rows of data? Drag handle to add a Sparkline for each row.

Format a Sparkline chart

Select the Sparkline chart.

Select Sparkline and then select an option.

Select Line, Column, or Win/Loss to change the chart type.

Check Markers to highlight individual values in the Sparkline chart.

Select a Style for the Sparkline.

Select Sparkline Color and the color.

Select Sparkline Color > Weight to select the width of the Sparkline.

Select Marker Color to change the color of the markers.

If the data has positive and negative values, select Axis to show the axis.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

If you’ve ever tried making sense of a lot of trend data in Excel you’ve usually found that plotting the data using a line chart or bar graph helps you make sense of it all. However what happens when you have more than one data set and all you need is a quick preview? Rather than generating a line chart for each data set or plotting all of the data sets with one chart you can use one Excel’s newest features, a Sparkline. Sparklines are tiny charts in a worksheet cell that provide you with a visual representation of data in a worksheet.

How to use sparklines in excel 2010

As you can see in the screenshot above, I have a monthly data set for about 100 keywords. I used Sparklines to give me a visual representation of keyword’s trend over the past 6 months (a small chart per keyword). To learn how to use Sparklines in Excel 2010, please follow the instructions below.

If you have any comments or questions please use the comments form at the end of the article.

Note: I used Excel 2010 for this guide.

How to use sparklines in excel 2010

1. Click on the cell you would like to insert the Sparkline into.

2. Click on the Insert tab on the Office Ribbon.

3. Click on one of the Sparklines listed. In this guide I plan on using the Line Sparkline because it will show me a linear trend which is what I’m looking for.

You will have the option to use one of the three following options:

Line

Column

Win/Loss

Use the one that best suits your data set.

How to use sparklines in excel 2010

4. Click on the Data Range button to select the data range.

A. Return to the spreadsheet to select the data you wish to plot with the Sparkline.

5. Click OK to save the range.

How to use sparklines in excel 2010

A. You will notice that the Sparkline you created will appear in the selected cell.

B. You can edit the Sparkline’s colors, format, and more using the Sparklines > Design Tab on the Office Ribbon.

How to use sparklines in excel 2010

6. If you wish to copy the Sparkline to other cells using the same formatting simply place the mouse cursor on the corners edge of the cell containing the Sparkline and click.

a. While holding the left mouse button drag down to fill the other cells. Let go of the mouse button when you are done.

A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Position a sparkline near its data for greatest impact.

How to use sparklines in excel 2010

Add a Sparkline

Select a blank cell at the end of a row of data.

Select Insert and pick Sparkline type, like Line, or Column.

Select cells in the row and OK in menu.

More rows of data? Drag handle to add a Sparkline for each row.

Format a Sparkline chart

Select the Sparkline chart.

Select Sparkline and then select an option.

Select Line, Column, or Win/Loss to change the chart type.

Check Markers to highlight individual values in the Sparkline chart.

Select a Style for the Sparkline.

Select Sparkline Color and the color.

Select Sparkline Color > Weight to select the width of the Sparkline.

Select Marker Color to change the color of the markers.

If the data has positive and negative values, select Axis to show the axis.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Forum Contributor Join Date 11-28-2008 Location Reykjav�k, Iceland MS-Off Ver Any of: 2003 & 2007 Posts 412

Sparklines (Excel 2010)

I couldn’t figure out how to display Sparkline from non-consecutive cells.

I want to display it from two apart ranges: A1:C1 and F1: J1

If it can’t be done within the worksheet – is there a VBA code to workaround – such as filling-in an Array from the non-consecutive and using that Array as the Sparkline source ?

  • View Profile
  • View Forum Posts
  • Visit Homepage

Forum Guru Join Date 05-10-2004 Location Essex, UK MS-Off Ver O365 Posts 19,934

Re: Sparklines (Excel 2010)

  • View Profile
  • View Forum Posts

Forum Contributor Join Date 11-28-2008 Location Reykjav�k, Iceland MS-Off Ver Any of: 2003 & 2007 Posts 412

Re: Sparklines (Excel 2010)

I will adopt your suggestion if this will be the last possibility.

Assume Range A1:D1 and F1:K1.
I assume I could fill a VBA contiguous Array with those non-contiguous ranges but I could not figure out how to implement that Array to be the source for a Sparkline to be displayed in cell L1.
(All the above mentioned procedure through code)

Until now Excel charts have been graphic objects that are generally positioned and sized independently of worksheet cells. In Excel 2010 Microsoft introduced the Sparkline, a new type of chart that actually lives in a worksheet cell. They can be particularly useful when you are short of space, or when you need to give a simple graphical message without a lot of detail or artistic elaboration.

Look at this worksheet of sales data for 2011.
How to use sparklines in excel 2010

The monthly sales data for each member of a team is shown. We can look at the numbers for each team member but there is no simple visual snapshot of how each of them is doing, or of how the two sales regions are doing overall.

Let’s insert a new column before the January one and insert Sparklines to see how they look. First, select Column B and right click to see the shortcut menu.
How to use sparklines in excel 2010

Select Insert, and Excel 2010 creates a new empty column.
How to use sparklines in excel 2010

Select cell B3, then select the Insert tab. You should see the Sparklines group on the ribbon.
How to use sparklines in excel 2010

Click Line in the Sparklines group.
How to use sparklines in excel 2010

You use the Create Sparklines dialog first to choose the data to be reported on. There is no need to type into the Data Range box, although you can if you want to. Just select the cells to be reported on using the mouse as shown here.
How to use sparklines in excel 2010

The selected cells are surrounded by the “marching ants” selection box and the cell range is automatically shown in the Data Range box in the Create Sparklines dialog. In this case the range is “C3:N3”.

You can place the Sparkline in any worksheet cell, but Excel 2010 defaults to placing it in the cell you previously selected, which in this case is B3. That selection is shown in the Location Range box in the Create Sparklines dialog.

Click OK and the Sparkline is created.
How to use sparklines in excel 2010

Notice that in this case the Sparkline shows a slight deterioration in the sales person’s performance with strong seasonal variations as well.

You don’t need to repeat this process for each of the other cells in Column B. You can copy and paste, or even use Fill Down. For example, select B3 then drag down with the mouse to B8. Then use the keyboard shortcut Ctrl-D to fill down.
How to use sparklines in excel 2010

Sparklines are created for each of the selected cells. (The Sparklines themselves are treated as a group in Excel, and if you want to work on them individually you will need to ungroup them.)

It’s easy to change the formatting of one or more Sparkline. Here I’ve selected the Sparklines for the two regional totals and the Grand Total.
How to use sparklines in excel 2010

Now, on the Design tab, in the Type group, select Column. The three selected Sparklines are changed.
How to use sparklines in excel 2010

There are various other ways of formatting Sparklines.

For example, under Sparkline Tools on the Design tab when you have a Sparkline selected you can choose from a gallery of possible designs.
How to use sparklines in excel 2010

Tip: Note that a Sparkline is actually part of the background of a cell, and you can still place text in the cell if you want to!

Simon C

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course – the rest, as they say, is history!

Excel Sparklines are a neat feature that was introduced back in Excel’s 2010 version. Excel Sparklines gives you a small chart that represents a set of data that can be inserted in a cell for a quick graphical representation of the data. While a Sparkline can be placed anywhere in a spreadsheet it tends to have the most impact if it is placed closer to the data it represents.

What Are Excel Sparklines?

You might be thinking to yourself, why not just use a regular chart? While you certainly still can use a chart to display the same data, sometimes it is a bit overkill to have a large chart, or even several large charts, next to your data.

Sparklines are also not objects like a chart is. They appear in the background of a cell so you can type text over it if you wanted to.

In addition to the default line graph, Sparklines also can be displayed as columns or in a win/loss format.

Why Use Sparklines?

When you have a set of data in a table, it is useful for organization purposes, but data in that format makes it hard to find patterns or trends with a quick glance. Sparklines provides a nice easy way to get a high-level overview of the data without spending much time analyzing the numbers behind it.

Take the data in the image below that shows quarterly divisional sales for an imaginary company. By itself, it might be hard to visualize how well any one division did in the second quarter. With the Sparklines in column F you can easily see which division did well and which did poorly compared to the other quarters.

How to use sparklines in excel 2010

How to Create a Sparkline in Excel

Creating a Sparkline is really simple assuming you already have a set of data to work with. Let’s take the example above and show how we got to the Sparklines in column F. As you can see it is the same data but we don’t have our Sparklines in column F yet.

How to use sparklines in excel 2010

Highlight cells F2:F6 where we want our Sparklines to appear.

How to use sparklines in excel 2010

Next, click the Insert tab and select the type of Excel Sparkline you want to use. In the original example I chose a line chart, but you could also choose column or Win/Loss depending on what makes the most sense for the data you are working with.

The dialog box that opens will ask you to fill in what data the Excel Sparklines should use. Since we already highlighted cells F2:F6, the dialog box is smart enough to fill in the Location Range for us with $F$2:$F$6, which is what we want it to do. Note: It will fill in whatever the active cell(s) on your worksheet in the Location Range if it detects that the cells are blank. It will fill in the Data Range with the active cell(s) if it detects that those cells that contain data.

You will need to tell it what data to use now in the Data Range box. Simply click in that box and highlight cells B2:E6 with your mouse and it will populate the cells in that box for you. You can also manually type the cells if you wish. Click OK when you’re done.

How to use sparklines in excel 2010

This will give you Sparklines in cells F2:F6 that represent the data in columns B:E for each individual row.

How to use sparklines in excel 2010

Ah, but I don’t like how that looks. I prefer it if there are points on the line to make the values stand out a bit more than this. Luckily we can customize how the Excel Sparklines look. To customize it, simply click anywhere in the set of Sparklines and you will notice a Design tab show up. Clicking on that tab will allow us to change the style of our Excel Sparklines, what data is shown, and how to highlight the important data like high or low points, individual data markers, negative points, etc.

To get the markers to show up like I want them to, I can click the Markers checkbox in the Show group. Clicking Markers will get me something like this.

How to use sparklines in excel 2010

I’m still not happy though. I want the markers to be a different color than the lines. Luckily we can use the Marker Color option in the Style group to change the markers to the dark red color I had in the original example. You can also use this menu to change the color of your negative points, high point, low point, first point, and last point. If you need to change the sparkline color, you can simply click the Sparkline Color menu from the Style group and change the color and the weight of the line that’s used.

How to use sparklines in excel 2010

Now, because we highlighted cells F2:F6 when we first set up our Sparklines, any style changes we make will be applied to all five of the Sparklines. This is great if we don’t want to spend time formatting each exactly the same, but what if we wanted each line to be formatted differently? Maybe you recognize divisions in your company by color and want the divisions to have the correct coloring.

If you click the Ungroup button at the far right side of the Design tab, it will allow you to make style changes to each individual Sparkline on your worksheet. You will need to click on the Sparkline you want to edit first and then make the appropriate changes.

Excel Sparklines was introduced in the version of Excel 2010. It is a small line chart that can be easily embedded with text and produces excellent results that appear more comfortable to describe when aligned alongside tabular data. They are only supported in Excel 2010 and above. Although the spark’s design is not limited to the lines, they can also be represented as poles or brakes. The following example illustrates how the lines look:

When using:

Generally, you will have one sparkline per row, but you can create as many as you like in any position you want. Like formulas, it is often easiest to automatically create a single rectifier and automatically use the fill handle to generate lightning lines for the remaining rows automatically.

How to use Sparklines:

Here are the steps listed that give a clear understanding of how to use Sparkline excel:

Insert Sparklines

To insert a line, perform the following steps.

1. Select the cells where you want sparklines to appear. In this example, we choose the range G2: G4.

2. On the Insert tab, in the Sparklines group, click Line .

3. Click in the Data Range box and select the range A2: F4.

5. Change the value of cell F3 to 0 .

Result. Excel automatically updates the sparkline.

Customize Sparklines

To customize the lightning ways, follow the steps below.

1. Select the seams.

2. On the Design tab, in the Show group, check High Point and Low Point .

A set of free User Defined Functions for Microsoft Excel® to create Sparklines :
the simple, intense, word-sized graphics invented by Edward Tufte & implemented by Fabrice Rimlinger.

Sparklines For Excel vs. Excel 2010 Sparklines

5 comments:

I for one will stick with using sfe to the bitter end.
I am pretty underwhelmed by what XL2010 is including, and not really surprised by it either. It seems to be the microsoft way sometimes, include a very basic (watered down if you will) copy of something, make it look similar at a glance, and claim you've got it as a native feature. This will work in fooling the general masses, but as you say, those of us in dashboard design know it needs a context, relevance, not just a line for the sake of having a line.

i am currently using sfe to design a couple of dashboards in my workplace, and though i started out doing some very simple stuff with them to get the hang of it, i now have nested formulae several levels deep, auto-changing scales and target lines, data filtering to relevant selections, and plotting it all against a defined target. Lets see a simple little squiggle do all that!
This functionality within sfe has allowed me to put what used to be approx 14 screen of graphs, tables, text etc (on a rolling display screen) onto just one page, and include a historical and forward target context also.

I am currently working on a second page to capture all sorts of data we never even used to display as it was too complex to view at a glance, and make it understandable to managers and production workers at the same time.

A big thanks for your effort on this fantastic tool, those of us who know the difference can truly appreciate a great tool when we see it 🙂

Badger : thanks for you enthusiasm ! I am curious to see your dashboard. I agree there is a learning curve w/ SfE but if you already master nested formulas, the perspectives are huge.
SfE are like Lego bricks. basic. The art is in putting the brick together and build something efficient.

Enjoy using SFE.. quality work!. Made some mods to greatly increase stability and performance for our particular application. SFE Traps calculation events as did our application. Once we modified to use the calculation event handler in our application all of our crashes went away and performance increased because our handler was more efficient for triggering spark updates.

Suggestion: If possible remove trapping of calculate events and hook conditional format rules.

I hope you are still a little into this project! It is a wonderful tool. I am getting used to it and am working on a little how-to document to make jumping in a little easier.

With regards to the original list of comparison to "the native" Excel, I would observe that anyone who complains that you can't blow a sparkline up to four times scale has not read Tufte and has no clue how to use a sparkline.

after all these years I feel sparklines for Excel deserves our loyalty – I don't mind the 2010 sparklines – but I'll be sticking with SfE.

If you have a certain kind of brain, you may be able to look at rows of numbers, spot trends and visualise the information they convey. For the rest of us, a little assistance is welcome. Excel 2010 includes a new facility, Sparklines, which helps a lot in seeing the ‘shapes’ of the numbers.

Put simply, Sparklines are miniature graphs and charts, which fit within single cells of a spreadsheet. They don’t have the sophistication of Excel’s full charts and graphs, but they’re not nearly as involved to put together, either. Not much harder than using AutoSum, in fact.

Take this sample spreadsheet, which breaks down the sales in a fictional company, by region. How easy do you find it to look at the trends in any region over the last five years? To make it easier, try this, on the row for Northern sales. Click in a cell at the end of the row, G3 in the example, open the Insert tab and click Line in the Sparklines group. A dialog opens and if you drag across cells B3 to F3 and click OK, a simple Sparkline, an unadorned line graph, is drawn in G3. At the basic level, that’s all there is to it.

It’s easy to make the Sparkline clearer to read and more attractive to look at. In the Sparkline Tools Design tab, start by clicking on Markers, High Point and Low Point. This puts blobs at each node on the line and marks the highest and lowest node. To make them a bit clearer, click on Marker Color and select High Point and Low Point in turn, to give the nodes colours.

You can replicate Sparklines in the same way you can formulas. Drag down from cell G3 to cover the sales rows from the other regions and values from the relative cells are automatically used to create new Sparklines.

The second type of Sparkline is the Column, and this can be created in exactly the same way as the line variant. Select the cell to take it – G17 in the screenshot, as we added some blank rows to avoid the ribbon pull-down hiding it – and Insert Column Sparkline. The result is a small column graph, using a default colour scheme.

There’s a selector in the ribbon for colour styles, which highlights the high and low points, as in the Line version. This makes it very quick and easy to select a colour scheme, which helps these features stand out.

The third Sparkline is the Win Loss, which shows up positive and negative values very clearly, using blocks either above or below the axis in the graph. If you go to the Axis options menu in the Sparkline Tools Design tab, you can choose to show the axis, to accentuate this plus/minus distinction.

Also in this menu is an option for Date Axis. This is useful if you have time-based data, where some days, months or years have no corresponding data value. With Date Axis selected these ’empty’ periods will be shown as spaces, giving a more faithful view of the information.