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.
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.
To create a sparkline in Excel, perform these steps:
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.
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:
Microsoft Excel provides three types of sparklines: Line, Column, and Win/Loss.
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:
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.
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:
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.
To quickly change the type of an existing sparkline, do the following:
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:
To change the appearance of your sparklines, use the style and color options residing on the Sparkline tab, in the Style group:
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.
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:
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.
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:
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:
Done!
To ungroup sparklines, select them and click the Ungroup button.
Tips and notes:
As Excel sparklines are background images in cells, they are resized automatically to fit the cell:
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:
Tip. If you've accidentally deleted a wrong sparkline, press Ctrl + Z to get it back.
As you already know, creating sparklines in Excel is easy and straightforward. The following tips will help you use them professionally:
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