Do you know what Excel sparklines are and how helpful they can be? Read this article and find the answers to these questions. You will learn how to insert sparklines in Excel 2010 - 2016; change their type, style and color; highlight the most important points and remove sparklines from cells.
Imagine you are working on a spreadsheet that includes a number of different salespersons and the amount they sold each month. You want to see how each person's sales are progressing over time. You can just put this information in one big chart, but it will be hard to pinpoint a trend for an individual person. Luckily Excel provides you with a special feature that can help you give each salesperson a separate mini chart that fits into a single cell. Please welcome… Excel Sparklines!
First let's define what a sparkline is.
Excel sparkline is a small chart placed in a single cell. It gives you a visual representation of the selected data set. Sparklines allow you to quickly view and analyze relationships and trends for multiple data series at the same time. Excel versions 2010 and 2013 offer three types of sparklines: Line, Column and Win/Loss.
The Line type appears as a simple line chart within a cell. Column sparklines take the form of simple bar charts reflecting the relative size of the value. Win/Loss shows a basic positive or negative representation of your data set.
Compared to traditional charts, sparklines offer a very different approach to visualizing your data. The key benefit of using them in your worksheet is that they are compact and don't take much space. Sparklines are ideal for situations when you need a clear overview of the data at a glance and when you don't need all features of a full chart. They also make it easy to view and analyze trends on a more individual basis, and they can really help you manage your worksheet data.
Excel sparklines are usually inserted in cells next to the data source. It doesn't matter which of the three sparklines you want to create, the steps are the same for all types.
The Create Sparklines dialog box appears on the screen. You see that the selected data range automatically displays in the Data Range field.
You see the first sparkline in the cell right next to your source data. If you'd like to apply it for all other rows of data, you can easily do it by holding and dragging the fill handle.
When you release the mouse button, the sparklines will be inserted in the adjacent cells.
If you have Excel 2016, you can use the Quick Analysis tool to quickly add sparklines to your data. All you have to do is select the cells in the worksheet and click on the Quick Analysis icon in the bottom-right corner of the selection. Then switch to the SPARKLINES tab and pick one of the types.
Once you create sparklines, you may notice that an additional tab appears on the Ribbon. Here you can find a wide range of tools that'll let you customize your mini charts. So let's make sparklines stand out a bit.
Before you start modifying your mini charts, I should mention that when you create a range of sparklines, Excel puts them in a group. As a result it is not necessary to highlight all of the sparklines when you want to modify them. Change one and you will change them all.
Since sparklines are so small, the default formatting applied to them by Excel makes it difficult to identify which values are the highest and lowest points, especially in the line mini chart. Follow the simple steps described below to see how to emphasize the highs and lows of Excel sparklines:
The sparklines look more readable with the high and low points marked, don't they?
You can also select First Point and Last Point to clearly mark the beginning and the end of the line. If you check the box next to Markers, every point will be highlighted, which can make your sparklines look a bit cluttered. Showing Negative Points will be useful in case you have negative values in your data.
I've already mentioned that there are three different types of sparklines. You can easily switch between Line, Column and Win/Loss charts. Just click on a sparkline to activate the DESIGN tab and select the desired type in the Type group.
Let's go on and give a different style to the mini charts.
If you want to add a specific style to a sparkline, use the Sparkline Color and the Marker Color commands in the Style group. The first option allows you to change the color of lines or bars in your mini chart. The second one makes it possible to highlight the most important points using different colors.
One more fact you should know about sparklines is that by default they are scaled to fit the cell size. It means when you adjust row-height or column-width of the cell containing a sparkline, the size of the sparkline changes too. It sounds good, but it can be misleading. Let me explain why.
For example, look at the sparklines in cells G3 and G4 on the screenshot below. You see that their maximum and minimum values look the same. If you turn to the source data, you will see that the numbers are different.
Luckily there is a solution to this problem:
Now it is easy to compare one sparkline to another.
You can find some more useful options in the Axis drop-down menu. If you click on the Custom Value option, it will let you set your own minimum and maximum values for the vertical axis of a sparkline or a sparkline group. You can also use the Plot Data Right-to-Left option to change the direction in which data are plotted in a mini chart. If there are negative values in your data, choose Show Axis to display the horizontal zero line in your sparkline. You can select Date Axis Type to format the shape of the chart in a sparkline to reflect any irregular time periods in the source data.
As far as you already know sparklines are usually used for displaying numeric values. What if you have empty cells in the data source? Don't worry and see below how to control the way a sparkline displays blanks.
Here you can also check the box next to Show data in hidden rows and columns to display such values in your sparkline.
The Edit Data option also allows you to change the location and data source for a sparkline group or a single mini chart.
We examined in depth how you can customize your mini charts and the time has come to know how to delete sparklines from your Excel worksheet.
If you want to get rid of a sparkline, the Delete button won't help with this task. You should use one of the following methods:
In this article I tried to cover all the bases of creating and customizing sparklines in Excel 2010 - 2016. Now you know how to insert a sparkline; change its type, style and color; highlight the most important points and remove sparklines from Excel cells. If I've forgotten to say anything about this useful feature, let me know in the comments. Thank you for reading!
10 responses to "What are Excel sparklines and how to insert them to analyze your data"
Please change title name to What are Excel sparklines and how to insert them to analyze your data
Thank you for pointing the misprint out. We've updated the title.
Could you tell how can we add or can use this Sparkline feature in Excel 2007.
Regards
Manoj
I have 2000 stores and a sparkline trend over 12 months for each of these stores. Is there a way using sparklines you can group similar trends for the stores together? Or is sparklines not capable of doing this?
Thanks
I need to be able to create and edit a sparkline in one worksheet and have it appear in a second worksheet. Is this possible? Copy/Paste does not work. Thanks.
Hi,
I can use copy and paste the chart generated by excel to powerpoint without problem, but when I do this with minichart sparkline, then the sparkline shown in powerpoint looks fuzzy or blurred. This does not apply to column.
Can you help ?
Hi - how do I pick up the data ranges from a different worksheet, to create the sparkline in a dashboard at the front of my document?
Thanks
~
Is there a way to include a data point label? Not the min / max, or title, but the actual number drives the respective data point in the chart? Thanks!
Thanks for good narration
I want to plot Sparkline's (line version) for the data across a year. I would like the current/most recent month to have the Last point marker on it but still plot the full year of data so the future months show no line as there is no data for them. I can use the show missing data as gaps feature. However the last point marker is in DEC and hence missing. I would like the Last point marker to be at the end of the visible line for the last/most recent month as the Sparkline develops through the year. Possible?