What is a Sparkline?
A sparkline, also known as an ‘inline chart’, is a very small chart that can fit inside a single cell. Sparklines are drawn without axes or coordinates to give an immediate visual representation of trends in data. The idea behind this representation is to place the visualization ‘in-line with other data’, therefore they are best used next to the actual data. Common uses include tracking changes in stock prices, periodic sales numbers, or other similar tabular data presentations.
Excel first introduced sparkline support in 2010. This built-in tool allows you to choose from 3 types of Excel sparklines with further customization options. Let’s jump in and see how you can do this to get a more compelling presentation. You can download our sample workbook below.
Creating Excel Sparklines
Assume that you have a data table like shown below.
To create Excel sparklines, begin by selecting the set of values to be used in the visualization.
Next, go to the INSERT tab in the Ribbon and select from the 3 available sparkline types under the Sparklines section.
- Line
- Column
- Win/Loss
Clicking one of these options will open the Create Sparklines dialog. Note that you can change the sparkline type later if you’re not happy with your selection.
Here, you will need to enter range for data (Data Range) and a range for the sparklines (Location Range). If you have already selected your data, your only need to select where the visualizations are to be placed. We recommend placing the sparklines next to the data.
Click OK to add sparklines into the specified range. You can now see an overview of the correlation between numbers at a glance. Let’s continue with how you can further customize Excel sparklines for your taste.
Sparkline Tools
If you select a cell or range containing sparklines, Excel will display a new tab in the Ribbon named SPARKLINE TOOLS. Go to this tab to see available options to customize the sparklines.
These options are grouped under 5 sections:
|
Sparkline |
Edit data and sparkline ranges. Choosing the method to handle gaps or empty cells. |
|
Type |
Change the type of the sparklines. |
|
Show |
Place indicators on or highlight specified values. |
|
Style |
Change color schemes of the sparklines. Detailed options of the features in the Show section. |
|
Group |
Add/remove/modify axes. Group or ungroup sparklines in a range. |
Let's modify the sparklines to match the color scheme of our data table, and also add coloring for high and low values. To do this, start with selecting the range of sparklines.
Select a color of your liking from the Style selector.
Go to Marker Color > High Point to select a color for high values from the palette, and then repeat the same thing for the low values. In this example, we are using green for High and red for Low values.
If your data is similar to ours, you might be having some trouble seeing the low values. The reason behind this is how Excel automatically creates the axes. To enter a custom range, go to Axis > Custom Value under the Vertical Axis Minimum Value Options section and set a value. We used “0” in our example.
And voila! This representation provides an excellent overview of the big picture at a glance, as you no longer have to add and subtract each column or row in your head.
