This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to add sparklines to Excel cells to show trends (and options for formatting)
Learn how to get individual Excel cells to contain sparklines to show trends, how to display column charts or win/loss charts instead, how to play about with grouped charts, how to change sparkline formatting and how to customise sparkline axes.

Posted by Andy Brown on 27 February 2023

In this blog

Creating Sparklines
Sparklines are Grouped by Default
Formatting (Including High and Low Points)
Changing the Scaling and Axis
Hidden and Empty Cells
Win/Loss Charts
Deleting Sparklines
Finally - Sparklines can Contain Text or Images

Excel Sparklines

Sparklines have been around in Excel for some time now, but are easily forgotten (not least by me!).

I thought I'd show how you can quickly apply them to cells to illustrate trends.

Skills assessment tests taken on the Wise Owl site in the last 5 complete years, showing the low (red) and high (green) point for each subject.

If you want to follow along you can easily do so - just download and unzip this Excel file.

Creating Sparklines

Start by selecting the cell or cells in which to put your sparklines:

An alternative strategy would be to create a sparkline for the top cell (Access 2016 in this case) and then copy it down to the cells beneath it.

Now choose to insert a sparkline:

You can add a line, column or win/loss sparkline (see later in this blog for what the a win/loss sparkline might look like).

Choose your data range, then select OK:

Here we've chosen to get data from the rows to the left of each sparkline cell.

You should now have some sparklines:

I chose to add column sparklines, so strictly speaking I guess I've got sparkbars.

Sparklines are Grouped by Default

When you add sparklines like this, they will be grouped:

If you want to format each sparkline separately, you can - you will first need to choose this option to ungroup them.

You'd be unlikely to want to ungroup these sparklines: it's convenient that you can change the formatting of one of them and have the others automatically update at the same time.

Formatting (Including High and Low Points)

The easiest way to change the colour of the maximum and minimum value in each sparkline is like this:

Choose the options shown to change the low point or high point colours.

Note that when you do this Excel will automatically tick the appropriate options:

These boxes have been ticked automatically.

It's now much easier to pick out trends:

You can clearly see that fewer people are taking our Access 2016 test, but many more the basic computing skills test.

Changing the Scaling and Axis

Excel will usually set the axis of a sparkline to start at 0, but sometimes this can give misleading results. To change this, go to the Axis dropdown in the dedicated sparkline ribbon:

Here I've chosen to show the axis, and am about to change the minimum value.

Now type in a new custom minimum value:

Values below 1,000 now won't be shown.

On this occasion this change hasn't made anything clearer!

The revised chart: values below 1,000 have disappeared.

Here's why we don't get an axis for our sparklines, even though we asked for one:

I THINK this makes sense!

Hidden and Empty Cells

Where you have gaps in your data, here's how to specify how they should be treated. First go to the relevant dialog box:

Choose this option to decide how to show empty and hidden cells.

You can then choose which option you want to apply:

See below to see the effect given by each of these options.

Here's an example of each option:

I've added in an extra value for Excel 365 tests for year 1. The first option fills in any gaps between this and the rest of the data, the second option displays missing points as zeros and the third option connects data points across the missing years.

Win/Loss Charts

Win/loss charts show binary day. For our example, I've changed the data to show a 1 where the number of tests done was more than the 5-year average, and a -1 otherwise:

The formula I used for the top left cells (Access 2016 tests for 2018) was =IF(B2>AVERAGE($B2:$F2),1,-1).

I've changed the sparklines to show win/loss data:

The chart type used in the screenshot above.

I suppose this does show clearly a trend of how you've succeeded or failed over time, but it does seem a bit niche ...

Just for fun, here is Preston North End's win/loss chart in the Championship for the season to date at the time of writing:

Although I've hidden most of the rows, I've configured the win/loss chart to display hidden rows. The chart shows no clear pattern (a bit like Preston, really).