Excel tips – analyse trends using Sparklines

Excel is packed with tons of useful and powerful features, interesting charts, libraries of formulas and awesome data analysis tools.

With such a vast array of functionality it can sometimes be hard to pinpoint features that are easy to use, and quickly add value to your work. What I like to call, quick wins! Let’s explore one of these useful features, Sparklines.

Sparklines (Insert > Sparklines)

Sparklines are mini-charts contained within a single cell that provide simple visualisation representations of trends across a row of data. Sparklines are ridiculously simple to use and can be extremely insightful particularly on spreadsheets crowded with data. As the saying goes, good things come in small packages and many people are yet to capitalise on the potential of these tiny charts.
To create your own Sparklines, select the data range and on the ribbon, click Insert and then select the Sparklines type — Line, Column or Win/Loss. In this example, I have selected Line.

The highlighted row will show in the Data Range field. The Location Rangeis the cell where the Sparkline will be displayed. Just click on the cell and Excel will populate this field with the absolute cell reference. Click OK.
Hey Presto! You’ve added your first Sparkline! The Sparklines contextual ribbon will appear which provides further options to customise your Sparkline such as marking High and Low Points, changing the Style, changing the Sparkline and Marker Colour.

Best of all, if you have many Sparklines to add, you can utilise Excel’s Auto-fill capabilities and just drag the Sparkline down to complete a whole column of mini-charts.

Line But wait! What if you decide you would like to see the trend represented in columns instead of lines? No problem! Highlight the Sparklines and click the Column button.

Column The third option is Win/Loss. This is useful if you have negative data and want to show an accurate representation of this using columns.

These tips were provided by filtered.

AAT students and professional members can access a wide range of Excel resources and training.

Filtered provides algorithmically personalised Excel courses written by subject matter expert.

Related articles