Sparklines in Excel: how to create, use and change

In this tutorial, you will find everything you need to know about sparkline charts: how to add sparklines in Excel, modify them as desired, and delete when no longer needed.

Looking for a way to visualize a large volume of data in a little space? Sparklines are a quick and elegant solution. These micro-charts are specially designed to show data trends inside a single cell.

What is a sparkline chart in Excel?

A sparkline is a tiny graph that resides in a single cell. The idea is to place a visual near the original data without taking too much space, therefore sparklines are sometimes called "in-line charts".

Sparklines can be used with any numerical data in a tabular format. Typical uses include visualizing fluctuations in temperature, stock prices, periodic sales figures, and any other variations over time. You insert sparklines next to the rows or columns of data and get a clear graphical presentation of a trend in each individual row or column.

Sparklines were introduced in Excel 2010 and are available in all later versions of Excel 2013, Excel 2016, Excel 2019, and Excel for Office 365.
Sparklines in Excel

How to insert sparklines in Excel

To create a sparkline in Excel, perform these steps:

  1. Select a blank cell where you want to add a sparkline, typically at the end of a row of data.
  2. On the Insert tab, in the Sparklines group, choose the desired type: Line, Column or Win/Loss.
  3. In the Create Sparklines dialog window, put the cursor in the Data Range box and select the range of cells to be included in a sparkline chart.
  4. Click OK.

Inserting sparklines in Excel

Voilà - your very first mini chart appears in the selected cell. Want to see in which way the data is trending in other rows? Just drag down the fill handle to instantly create a similar sparkline for each row in your table.
Copying the sparkline to other rows

How to add sparklines to multiple cells

From the previous example, you already know one way to insert sparklines in multiple cells – add it to the first cell and copy down. Alternatively, you can create sparklines for all cells in one go. The steps are exactly the same as described above except that you select the entire range instead of a single cell.

Here are the detailed instructions to insert sparklines in multiple cells:

  1. Select all the cells where you want to insert mini-charts.
  2. Go to the Insert tab and pick the desired sparkline type.
  3. In the Create Sparklines dialog box, select all the source cells for Data Range.
  4. Make sure Excel displays the correct Location Range where your sparkline are to appear.
  5. Click OK.

Inserting sparklines in multiple cells

Sparkline types

Microsoft Excel provides three types of sparklines: Line, Column, and Win/Loss.

Line sparkline in Excel

These sparklines look very much like small simple lines. Similar to a traditional Excel line chart, they can be drawn with or without markers. You are free to change the line style as well as the color of the line and markers. We will discuss how to do all this a bit later, and in the meanwhile just show you an example of line sparklines with markers:
Line sparklines in Excel

Column sparkline in Excel

These tiny charts appear in the form of vertical bars. As with a classic column chart, positive data points are lying above the x-axis and negative data points below the x-axis. Zero values are not displayed – an empty space is left at a zero data point. You can set any color you want for positive and negative mini columns as well as highlight the largest and smallest points.
Column sparklines in Excel

Win/Loss sparkline in Excel

This type is very much like a column sparkline, except that it does not show the magnitude of a data point – all bars are of the same size regardless of the original value. Positive values (wins) are plotted above the x-axis and negative values (losses) below the x-axis.

You can think of a win/loss sparkline as a binary micro-chart, which is best to be used with values that can only have two states such as True/False or 1/-1. For example, it works perfectly for displaying game results where 1's represent wins and -1's defeats:
Win/Loss sparklines in Excel

How to change sparklines in Excel

After you have created a micro graph in Excel, what is the next thing you would usually want to do? Customize it to your liking! All customizations are done on the Sparkline tab that appears as soon as you select any existing sparkline in a sheet.

Change sparkline type

To quickly change the type of an existing sparkline, do the following:

  1. Select one or more sparklines in your worksheet.
  2. Switch to the Sparkline tab.
  3. In the Type group, pick the one you want.

Changing the sparkline type

Show markers and highlight specific data points

To make the most important points in sparklines more noticeable, you can highlight them in a different color. Additionally, you can add markers for each data point. For this, simply select the desired options on the Sparkline tab, in the Show group:
Show markers and highlight specific data points in Excel sparklines

Here is a brief overview of the available options:

  1. High Point – highlights the maximum value in a sparkline.
  2. Low Point – highlights the minimum value in a sparkline.
  3. Negative Points - highlights all negative data points.
  4. First Point – shades the first data point in a different color.
  5. Last Point – changes the color of the last data point.
  6. Markers – adds markers at each data point. This option is only available for line sparklines.

Change sparkline color, style and line width

To change the appearance of your sparklines, use the style and color options residing on the Sparkline tab, in the Style group:

  • To use one of the predefined sparkline styles, simply select it from the gallery. To see all the styles, click the More button in the bottom-right corner.
    Changing the sparkline style
  • If you don't like the default color of Excel sparkline, click the arrow next to Sparkline Color and pick any color of your choosing. To adjust the line width, click the Weight option and either choose from the list of predefined widths or set Custom Weight. The Weight option is only available for line sparklines.
    Changing the sparkline color and line width
  • To change the color of markers or some specific data points, click the arrow next to Marker Color, and pick the item of interest:
    Changing the color of sparkline markers and specific points

Customize sparkline's axis

Typically, Excel sparklines are drawn without axes and coordinates. However, you can show a horizontal axis if needed and do a few other customizations. The details follow below.

How to change the axis staring point

By default, Excel draws a sparkline chart in this way - the smallest data point at the bottom and all other points relative to it. In some situations, however, this may cause confusion making an impression that the lowest data point is close to zero and the variation between data points is bigger than it actually is. To fix this, you can make the vertical axis start at 0 or any other value you find appropriate. For this, carry out these steps:

  1. Select your sparklines.
  2. On the Sparkline tab, click the Axis button.
  3. Under Vertical Axis Minimum Value Options, pick Custom Value…
  4. In the dialog box that appears, enter 0 or another minim value for the vertical axis that you see fit.
  5. Click OK.

Adjusting the vertical axis of a sparkline chart

The below image shows the result – by forcing the sparkline chart to start at 0, we got a more realistic picture of the variation between the data points:
Excel sparklines with the default and custom y-axis

Note. Please be very careful with axis customizations when your data contains negative numbers –setting the minimum y-axis value to 0 will cause all negative values to disappear from a sparkline.

How to show x-axis in a sparkline

To display a horizontal axis in your micro chart, select it, and then click Axis > Show Axis on the Sparkline tab.
Show a horizontal axis in a sparkline.

This works best when the data points fall on both sides on the x-axis, i.e. you have both positive and negative numbers:
A sparkline chart with a visible x-axis

How to group and upgroup sparklines

When you insert multiple sparklines in Excel, grouping them gives you a big advantage – you can edit the whole group at once.

To group sparklines, this is what you need to do:

  1. Select two or more mini charts.
  2. On the Sparkline tab, click the Group button.

Done!
Group sparklines to edit them all at once.

To ungroup sparklines, select them and click the Ungroup button.

Tips and notes:

  • When you insert sparklines in multiple cells, Excel groups them automatically.
  • Selecting any single sparkline in a group selects the entire group.
  • Grouped sparklines are of the same type. If you group different types, say Line and Column, they all will be made the same type.

How to resize sparklines

As Excel sparklines are background images in cells, they are resized automatically to fit the cell:

  • To change the sparklines width, make the column wider or narrower.
  • To change the sparklines height, make the row taller or shorter.

Changing the width and height of a sparkline

How to delete a sparkline in Excel

When you decide to remove a sparkline chart you no longer need, you may be surprised to find that hitting the Delete key has no effect.

Here are the steps to delete a sparkline in Excel:

  1. Select the sparkline(s) you want to delete.
  2. On the Sparkline tab, do one of the following:
    • To delete only the selected sparkline(s), click the Clear button.
    • To remove the entire group, click Clear > Clear Selected Sparkline Groups.

Deleting sparklines in Excel

Tip. If you've accidentally deleted a wrong sparkline, press Ctrl + Z to get it back.

Excel sparklines: tips and notes

As you already know, creating sparklines in Excel is easy and straightforward. The following tips will help you use them professionally:

  • Sparklines can only be used in Excel 2010 and later; in Excel 2007 and earlier, they are not shown.
  • Like full-blown charts, Excel sparklines are dynamic and automatically update when the data changes.
  • Sparklines only include numeric data; text and error values are ignored. If the source data set has blank cells, a sparkline chart has blanks too.
  • A sparkline size is dependent on the cell size. When you change the cell's height or width, the sparkline adjusts accordingly.
  • Unlike traditional Excel charts, sparklines are not objects, they are images in the background of a cell.
  • Having a sparkline in a cell does not prevent you from entering data or formulas in that cell. You can even use sparklines together with conditional formatting icons to enhance the visualization capability.
  • You can create sparklines for Excel tables and pivot tables too.
  • To copy your sparkline charts to another application such as Word or Power Point, paste them as pictures (Paste > Picture).
  • The sparkline feature is disabled when a workbook is opened in the compatibility mode.

That's how to add, change and use sparklines in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard