What are Excel sparklines and how to insert them to analyze your data
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!
What is a sparkline in Excel?
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.
Why should you use sparklines?
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.
How to insert sparklines in Excel 2016 - 2010
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.
- Select the cells that will serve as the source data for your mini chart.
- Go to the Sparklines group on the INSERT tab and choose the desired type.
The Create Sparklines dialog box appears on the screen. You see that the selected data range automatically displays in the Data Range field.
- Select the cell where you want to place the sparkline. You can just click on the necessary cell or use the Select Range button to specify the location for your mini chart. The cell reference will appear in the Location Range field.
- Click OK.
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.
Customize Excel mini charts
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.
- Show points
- Change the sparkline type
- Modify the style of a sparkline
- Customize Axis settings
- Show empty and hidden cells
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.
Show points
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:
- Click on the cell that contains the sparkline.
- Go to the Show group on the DESIGN tab.
- Check the High Point and Low Point options.
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.
Change the sparkline type
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.
Modify the style of sparklines
Let's go on and give a different style to the mini charts.
- Select the sparkline(s) you want to change.
- Click on the More down arrow in the Style group on the DESIGN tab.
- Choose one of the predefined styles in the drop-down menu.
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.
Customize Axis settings
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:
- Select the sparklines you want to modify.
- Go to DESIGN -> Group and click the Axis command.
- Select Same for All Sparklines in the Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options sections.
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.
Show empty and hidden cells
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.
- Click on a mini chart to make the DESIGN tab visible.
- Go to the Sparklines group and open the Edit Data drop-down menu.
- Choose the Hidden & Empty Cells… option.
- Select the way for a sparkline to show empty cells in the Hidden and Empty Cell Settings dialog box.
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.
Remove sparklines from Excel cells
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:
- Right-click on the mini chart and select Sparklines -> Clear Selected Sparklines from the context menu.
Note. If you want to delete the entire group, choose the Clear the Selected Sparkline Groups option. - Alternatively, select the cell with a sparkline, go to DESIGN -> Group and click the down arrow next to Clear. You will see the same options as in the right-click menu. Choose the option you need.
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!
War in Ukraine. 