How to Use Sparklines in Excel to Show Trends (Fast & Easy)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In Excel, there are lots of graphs that you can add to your report (and Excel keeps on adding new charts).

But what if you want to put a mini chart into a cell? Actually, you might have already seen a few of them in action. 🤔

Those mini-charts are called sparklines.

In this article, you’ll learn what sparklines are and how you could use one on your spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

What are sparklines in Excel?

Sparklines are small charts in Excel that fit in a cell.

Unlike there large counterparts, sparklines are used to show variations or trends in the data over a period of time.

Kasper Langmann, Co-founder of Spreadsheeto

If you love reports, you may have seen some of them already.

Here’s an example of sparklines that show income per quarter on US states.

Sample application of a sparkline

Although sparklines don’t have the same functionalities as regular charts, they are great in adding a level of visual analysis into your report by showing trends.

In addition, sparklines make your report look better and more reader-friendly. 😉

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

How to add a sparkline?

In Excel, there are three types of sparklines you can add — line, column, and win/loss.

The different types of sparklines on Excel

Line and column sparklines are easy to understand since they are just tiny line and column charts.

On the other hand, a win/loss sparkline resembles a column chart and a box and whisker plot. This type of sparkline is good where the data is binary — win/loss, yes/no, true/false, 1/-1, etc…

The good news is, adding and formatting each one follows a similar process.

For this tutorial, let’s use the following dataset:

Sample dataset for Excel sparklines tutorial

To insert a sparkline in Excel, select the cell in which you want the sparkline to reside in.

Then, on the ‘Insert’ tab

insert option on the tab list

Click the type of sparkline (on the ‘Sparklines section) you want to add.

Let’s try adding a line sparkline in our example.

How to insert a line sparkline on Excel

After that, you will be asked for the data range.

Note that if you have selected beforehand the cell where you want the sparkline to reside in, you don’t have to change the ‘Location Range’ setting.

Kasper Langmann, Co-founder of Spreadsheeto

To choose the range, either type into the field the range or click the up arrow button and select the cells that contain the data.

How to choose the data for sparklines

After selecting the data range, press ‘Enter’ on your keyboard or click the arrow down button on the window and click ‘OK’.

Choosing the data range for a sparkline in Excel

Nice and easy! 😊

Hidden and Empty Cells

It’s normal for some reports to contain hidden and empty cells.

If there’s such a cell in the data range used in creating a sparkline, you will see a noticeable gap on the sparkline by default.

How a gap on the data range looks on a sparkline

If you would like to show the empty cells as zeroes or connect the data points with a line, all you have to do is:

  • Go to the ‘Design’ tab
  • Click the ‘Edit Data’ icon
  • Select ‘Hidden & Empty Cells…’ from the dropdown
How to manage hidden and empty cells when creating a sparkline

Then, select whether you want to treat a hidden or an empty cell as zero or else, connect the data points with a line.

How to show empty cells as zeroes when creating a sparkline

How to format a sparkline?

You can also format a sparkline’s style, color, and even the points to show.

All of these are in the ‘Design’ tab.

How to design an Excel sparkline

Markers correspond to the different points that can be shown on a sparkline such as:

  • High Point
  • Low Point
  • Negative Points
  • First Point
  • Last Point
  • Markers

To show any of them, just check their appropriate boxes on the ‘Show’ section.

How to show the points on an Excel sparkline

You can change their color from the ‘Marker Color’.

How to change the marker color of an Excel sparkline

How to delete a sparkline?

If you have noticed, you can’t delete a sparkline by pressing the ‘Delete’ or the ‘Backspace’ key on your keyboard.

That’s because sparklines aren’t objects. They are elements server as the background of that cell.

Kasper Langmann, Co-founder of Spreadsheeto

To delete a sparkline, all you have to do is select the cell where that sparkline resides.

Then, click the ‘Clear’ button found on either the ‘Home’ tab or on the ‘Design’ tab.

How to clear a sparkline on Excel

That’s it! The sparkline has been deleted. 😊

How to delete a sparkline on Excel

Wrapping things up…

Sparklines are like mini charts found on an Excel cell. Technically, they are serving as the background of those cells (which is the reason why you can’t delete them).

These elements are great in showing the trends and variations in the data all inside a single cell. Not only are they good for the eyes, but they also add some level of visual analysis to your reports. 😊

Kasper Langmann, Co-founder of Spreadsheeto