Utilising Sparklines in Ms Excel 2010

Outline

As a Microsoft coach I can remember reviewing Sparklines way back in 2010 when they were new and anticipating how they may well become a widely used aid in Excel. In hindsight it seems apparant that I could very well have been a little quick in my opinion. I’ve as yet to find any one who actually believes them to be as advantageous as I implied, however possibly that’s because a lot of people still haven’t noticed them yet?

Generally speaking a Sparkline is really a mini-graph which takes up only one cell inside the spread sheet. They’re useful for spotting trends within data, in much the same way that a customary chart and chart option is. But, the essential advantages of Sparklines are their very small size and simplicity of use.

Making your Sparkline

This happens to be a straight forward process and something which you will probably become pretty comfortable with:

1. First click on the ‘Insert’ tab on the ribbon then choose the ‘Line’ tool inside the Sparklines group.

2. With the cursor flashing inside the ‘Data Range’ field, click and drag across the collection of cells that this Sparkline is actually to represent.

3. Next tab to the ‘Location Range’ field and click on in the individual cell in which the Sparkline is to be generated.

4. Finally just click ‘OK’ and the Sparkline will be formed just like a line chart.

It is certainly well worth observing that Sparklines can be placed anywhere inside spread sheet, and do not need to be in the same row or in the same column. Using this method, Sparklines may be grouped alongside one another to illustrate changes throughout an array of information at a glance.

Copying Sparklines

Sparklines operate exactly like any other formula because they they may be copied down a column or across a row to graph or chart many different data ranges. Just make your first Sparkline, after that press the AutoFill handle (the small black square to be found in the bottom right hand corner of each cell) and drag down a column or across a row. Further Sparklines will instantly be made which will represent data ranges in relevant rows or columns.

Formatting Sparklines

The default Sparkline chart format commonly is possibly not as effective as it might be. Owing to their very small size, Sparklines can benefit from a visual ‘boost’ by using and refining the format options available.

Combining Markers

Extra markers could be added that will feature data points on the Sparkline. This is accomplished basically by ticking the appropriate boxes inside the ‘Show’ group within the ‘Design’ tab of the ribbon. Checking the ‘Markers’ selection will choose all points within the Sparkline chart. Various predetermined styles is offered within the ‘Styles’ group and may be used by merely clicking the style required. The color scheme can be tailored by just selecting individual Sparkline and Marker colours from the tools located immediately on the right of the Styles group.

Switching Sparkline Charts

There are 3 kinds of Sparkline graphs offered: Line, Column and Win / Loss. The choice can be swapped by just highlighting the Sparkline cell and next simply clicking on the appropriate the type tool button.

Customising the Axis

There are a number of other possibilities which enable fine-tuning of the Sparkline axis settings. If you have dates within your data range you may choose the ‘Date Axis Type’ from the ‘Axis’ tool on the ribbon. This is particularly useful whenever you have fluctuating dates inside a range. The vertical axis minimum values can also be edited with this tool. The default value is actually ‘Automatic for every Sparkline’, although this can certainly be changed to ‘Same for each Sparkline’. One difficulty with Sparklines is that thanks to their limited size, broad fluctuations in data ranges from one Sparkline chart to another can result in misleading results. Applying this option, the scale may adapted to suit the complete range of data being charted.

Review of Sparklines

We can discover then that Sparklines actually are a very convenient means for showing data in a visible manner to ensure we can easily identify trends. To help get the very best from them, we ought to look past the default graph or chart view and be well-versed in the range of formatting choices.

For information on excel training courses click for our blog