Categories
Application Data Visualisation Excel Training Microsoft

How to Use Sparklines in Excel to Visualise Data Trends

Sparklines are a powerful tool in Excel that allow you to visualise data trends in a condensed chart format. Unlike normal Excel charts, Sparklines are embedded in a single cell, making them ideal for displaying trends in sales figures, website hits, and other data over a period of time.

In this blog post, we’ll show you how to create and customise Sparklines in Excel, and provide some examples of how they can be used in different business roles and industries.

For instance, sales managers can use Sparklines to track sales figures over time, while marketing professionals can use them to monitor website traffic. Sparklines can also be used in finance to track stock prices or in healthcare to monitor patient vitals.

There are 3 types of Sparkline available, “line”, “column” and “Win/Loss”:

How to Use Sparklines in Excel to Visualise Data Trends

In the following example we see how a sales manager can quickly create Sparklines to easily analyse and compare performance across multiple sales regions.

Creating a Sparkline

Unlike a normal Excel chart that ‘floats’ on top of the spreadsheet, a Sparkline is embedded in a single cell. Create a Sparkline for Region A figures Jan-Oct as follows:

  1. Select a cell to the right of the data you want to visualise – see below:

How to Use Sparklines in Excel to Visualise Data Trends

2. Go to INSERT > SPARKLINES > LINE

3. In the ‘Data Range’ field, select all the cells containing numbers to the left of the selected cell. Click OK to display the following Sparkline

How to Use Sparklines in Excel to Visualise Data Trends

Editing a Sparkline

  1. To see each point of the Sparkline more clearly, go to SPARKLINE > SHOW > HIGH POINT.

Note the same values of 28 for both March and July give rise to 2 red markers on the Sparkline.

How to Use Sparklines in Excel to Visualise Data Trends

Reducing the July figure to 26 would result in just one marker for March – see below:

How to Use Sparklines in Excel to Visualise Data Trends

2. Reducing the July figure to 26 would result in just one marker for March – see below:

How to Use Sparklines in Excel to Visualise Data Trends

3. Now change the August figure to -5 and then change the type to WIN/LOSS

4. Untick ‘High Point’ and tick ‘Negative Point’

5. Select a different Sparkline style to reflect RAG status colours (Red, Amber, Green) i.e. Red (negative values) and Green (positive values)

Note the main purpose of the WIN/LOSS Sparkline is to show which values are positive or negative – see below.

How to Use Sparklines in Excel to Visualise Data Trends

6. Change the type back to the default ‘Sparkline’ and set the following:

a. Tick ‘High point’ and ‘Low point’

b. Untick ‘Negative points’

c. Sparkline colour to blue.

Copying a Sparkline

Once you have created a Sparkline, all its attributes can be copied down to represent other rows of data:

How to Use Sparklines in Excel to Visualise Data Trends

The result is shown below:

How to Use Sparklines in Excel to Visualise Data Trends

2. To edit the Sparklines, select any one within a single cell, do the edits and all of them will be changed as a group

3. If you want to change one specific Sparkline without affecting the others, go to SPARKLINE > GROUP > UNGROUP

Deleting Sparklines

Unlike for normal charts and data generally, the delete button will not work for deleting Sparklines.

Select the Sparklines and go to SPARKLINES > GROUP > CLEAR > CLEAR SELECTED SPARKLINES

Conclusion

In conclusion, Sparklines are a great way to visualise data trends in Excel, and can be used in a variety of business roles and industries. By following the steps outlined in this blog post, you can create and customise Sparklines to better understand your data and make more informed decisions.

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.