About us - Contact us  
deskdemon logo
DeskDemon US Click here for DeskDemon US 

By Paul Macready Brown

Sparklines are new to Excel 2010 and look set to become a very popular tool. Essentially a Sparkline is a mini-graph which occupies just one cell within the spreadsheet. They're useful for spotting trends in data, in much the same way that the familiar graph and chart facility is. However, the major advantages of Sparklines are their tiny size and ease of use.

Creating a Sparkline
This is a straight forward process and one which you will probably become quite familiar with:

   1. First select the 'Insert' tab on the ribbon then click the 'Line' tool within the Sparklines group.
   2. With the cursor flashing in the 'Data Range' field, click and drag over the range of cells which the Sparkline is to represent.
   3. Next tab to the 'Location Range' field and click in the single cell where the Sparkline is to be created.
   4. Finally click 'OK' and the Sparkline will be created as a line graph.

It is perhaps worth noting that Sparklines can be placed anywhere within the spreadsheet, and do not have to be on the same row or in the same column. In this way, Sparklines can be grouped together to show changes in a wide range of data at a glance.

Copying Sparklines
Sparklines behave like any other formula in that they can be copied down a column or across a row to chart multiple data ranges. Just create your first Sparkline, then click the AutoFill handle (the small black square situated in the bottom right hand corner of each cell) and drag down a column or across a row. Additional Sparklines will automatically be created which will represent data ranges in relevant rows or columns.

Formatting Sparklines
The default Sparkline graph format is often not as effective as it could be. Due to their small size, Sparklines can benefit from a visual 'boost' by adding and refining the formatting options available.

Adding Markers
Additional markers can be added to highlight data points along the Sparkline. This is achieved by ticking the relevant boxes within the 'Show' group on the 'Design' tab of the ribbon. Checking the 'Markers' option will select all points along the Sparkline graph. A wide range of predefined styles is available within the 'Styles' group and can be applied by simply clicking the style required. The colour scheme can be customised by choosing individual Sparkline and Marker colours from the tools located immediately to the right of the Styles group.

Changing Sparkline Graphs
There are 3 types of Sparkline graphs available: Line, Column and Win / Loss. The type can be changed by highlighting the Sparkline cell and then simply clicking the relevant the type tool button.

Customising the Axis
There are a number of options which enable fine-tuning of the Sparkline axis settings. If you have dates in your data range you can choose the 'Date Axis Type' from the 'Axis' tool on the ribbon. This is particularly useful if you have fluctuating dates within a range. The vertical axis minimum values can also be edited using this tool. The default value is 'Automatic for each Sparkline', but this can be changed to 'Same for each Sparkline'. One problem with Sparklines is that due to their small size, wide fluctuations in data ranges from one Sparkline chart to another can produce misleading results. Using this option, the scale can adjusted to suit the full range of data being charted.

Summary of Sparklines

We can see then that Sparklines are a very convenient way of displaying data in a visual manner in order that we can detect trends. To get the very best from them, we should look beyond the default chart view and become familiar with the range of formatting options available.

Paul Brown is the managing director of Paul Brown Associates Ltd, a UK training organisation which specialises in the onsite delivery of Microsoft courses. For information about Microsoft Excel training courses visit the blog at www.paulbrowntraining.org