Sparklines in Excel: how to create, use and change
by Svetlana Cheusheva, updated on
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.
How to insert sparklines in Excel
To create a sparkline in Excel, perform these steps:
- Select a blank cell where you want to add a sparkline, typically at the end of a row of data.
- On the Insert tab, in the Sparklines group, choose the desired type: Line, Column or Win/Loss.
- 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.
- Click OK.
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.
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:
- Select all the cells where you want to insert mini-charts.
- Go to the Insert tab and pick the desired sparkline type.
- In the Create Sparklines dialog box, select all the source cells for Data Range.
- Make sure Excel displays the correct Location Range where your sparkline are to appear.
- Click OK.
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:
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.
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:
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:
- Select one or more sparklines in your worksheet.
- Switch to the Sparkline tab.
- In the Type group, pick the one you want.
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:
Here is a brief overview of the available options:
- High Point – highlights the maximum value in a sparkline.
- Low Point – highlights the minimum value in a sparkline.
- Negative Points - highlights all negative data points.
- First Point – shades the first data point in a different color.
- Last Point – changes the color of the last data point.
- 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.
- 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.
- To change the color of markers or some specific data points, click the arrow next to Marker Color, and pick the item of interest:
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:
- Select your sparklines.
- On the Sparkline tab, click the Axis button.
- Under Vertical Axis Minimum Value Options, pick Custom Value…
- In the dialog box that appears, enter 0 or another minim value for the vertical axis that you see fit.
- Click OK.
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:
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.
This works best when the data points fall on both sides on the x-axis, i.e. you have both positive and negative numbers:
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:
- Select two or more mini charts.
- On the Sparkline tab, click the Group button.
Done!
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.
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:
- Select the sparkline(s) you want to delete.
- 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.
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!
Table of contents
