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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

To create Excel sparklines, begin by selecting the set of values to be used in the visualization.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

 

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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

Select a color of your liking from the Style selector.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables

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.

How to Use Excel Sparklines for Better Looking Tables