Sparklines in Excel are a really handy way of visualizing our data in a compact and easy to understand way, without the need to create full blown charts.
In this simple guide we will cover how to easily create Sparklines in Excel. We will cover:
- What are Sparklines?
- Different types of charts
- How to create them
- Modifying Sparklines
To start with, let’s cover off what they are and how they differ from traditional charts.
What are Sparklines?
Put simply – Sparklines are mini charts. They sit entirely within a single cell, and in a very simplified way showcase trends in our data.
Compared to full blown visualizations such as line charts or bar charts, the amount of actual information inside them is very limited as they do not contain axis labels, headers, legends and so on – simply the data.
Let’s look at a Sparkline vs a line chart side by side to show what we mean. Let’s say we have the below dataset, simply displaying monthly sales values over the course of a year:
If we were to create a traditional line chart, it would look like this:
If we made a Sparkline however, the output would be as follows:
As we can see, while the line chart is a lot more detailed, the literal line itself is identical in what it is displaying. If we were working with a lot of different rows of data across multiple periods, it could be really useful to build out a series of Sparklines to keep our spreadsheets clean, rather than building out a whole set of charts.
Different Types of Sparklines in Excel
While the above example was mimicking a line chart, there are a few different types that we can create:
Line – Displays a single line chart based on the series of values selected
Column – Displays a series of bars based on the values selected
Win/Loss – Displays a positive or negative direction bar. Note that this does not display actual volume of each number, simply whether it was a negative or positive
Examples of all three are below:
The line and column Sparklines are using our above example, while the Win/Loss one is simply a random collection of 1 and -1 values purely for the purpose of a visual example, because our dataset above would end up turning this into one flat looking chart.
Now that we have covered off the types of Sparklines, let’s run through how to make them.
How to Create a Sparkline
Creating Sparklines in Excel is super simple – first we need to head into the Insert ribbon, and to the right we will see Sparklines. For this example click on Line.
From here, a box will pop up, asking us to choose the data itself, and where we want the sparkline to appear. We will select cells C3:C14, and then E3 for the Sparkline:
Finally, press OK and we are given our Sparkline!
That’s literally all there is do to in order to create a nice simple Sparkline. Unlike proper line charts and bar charts, we don’t have to worry about including additional information such as the name of each month in column B, or create chart titles, secondary axis or anything like that – we simply select the data points, and which cell we want the Sparkline to appear in!
Modifying our Sparklines
Finally, let’s take a quick look at how we can change the look and feel of these Sparklines.
If we click into the chart itself, a new option will appear up in the ribbon called Sparkline – if we click on this we are presented with a whole bunch of ways to edit the look and feel:
Running through some of the key options, our choices are:
Type – Just lets us change from a line to column to win/loss
High Point – Create a marker for the highest value
Low Point – Create a marker for the lowest value
Negative Points – Create markers for negative values
First Point – Create a marker for the first point in the Sparkline
Last Point – Create a marker for the last point in the Sparkline
Markers – Create a marker for every value
Style – Change the look and feel of the line/chart itself
We also have various options for colours.
Below are a few outputs working with different marker and colour settings:
Even simple changes such as a new colour scheme and markers can make quite a significant difference in readability! With the middle example for our column chart we can very easily see that the final month of the year was the top performer, while our weakest month was towards the start of the year.
This sums up our simple guide on how to create Sparklines in Excel. While they may not be as powerful and comprehensive as full blown charts, they definitely serve their purpose and can be super handy in the right situations. For more handy guides on working with Excel, be sure to check out our Excel Tips page.