Excel Sparklines

Sparklines are mini graphs that you can add to cells in your spreadsheet. They were introduced in Excel 2010, and look like this:

Cell A8 showing a Sparkline graph

The graph in the cell A8 shows the scores that Lisa achieved in her tests. You can quickly see that Lisa's scores are going up all the time, with no dips.

To add Sparklines to your own spreadsheets, start with some data. (You need more than one value, otherwise you'll just have a dot.) In a new Excel spreadsheet, type the following exam scores:

The data for the spreadsheet

Change the height of row 8 to create more room for the Sparklines. To do this, you can simply move your mouse to just below the number 8 on the left of Excel. When your mouse pointer changes shape, hold down the left mouse button. Keep it held down and drag to a new height.

If you want an exact value for the height, click inside of any cell on row 8. Locate the Cells panel on the Home ribbon at the top of Excel:

The cells panel on the Excel ribbon

From the Cells panel, click on Format. From the Format menu, select Row Height:

Items on the Format menu in Excel

When you select Row Height, you'll see a small dialogue box appear. Type in a new value for the height and click OK:

The Row Height dialogue box

Row 8 on your spreadsheet should now look like this:

Row Height changed in Excel

 

Adding a Sparkline to a Spreadsheet Cell

To add a Sparkline, click inside of cell A8. Now select the Insert ribbon from the top of Excel. From the Insert ribbon, locate the Sparklines panel:

The Sparklines panel on the Excel ribbon

You can see that there are three options: Line, Column, and Win/Loss. The first two are the most used options. Click on Line and you'll see a dialogue box appear. This one:

Create Sparklines dialogue box

The first text box is where you specify your Data Range. Click inside this box and enter A1:A7. For the second text box, Location Range, you specify where on your spreadsheet you want your Sparkline to appear. We want the Sparkline to appear in cell A8. The dollar signs before the A and 8 mean it will be an Absolute cell reference, as opposed to a Relative cell reference. (We go into this in more detail in a later tutorial, here: Absolute Cell References.) If you leave the Location Range box blank then your Sparkline will appear in the currently selected cell.

Your Create Sparklines dialogue box should look like this:

Sparklines dialogue box - Data Range

Don't worry if your Location Range text box is blank, though. As long as your Data Range box is filled in you can click OK.

When you do click OK, your A8 cell will look like this:

A Sparkline added to cell A8

You can use AutoFill for the other exam scores. Move your mouse pointer to the bottom right of cell A8 until the pointer turns into a black cross. Hold down your left mouse button. Keep it held down and drag to cell D8. You should see all four cells fill with Sparkline charts:

sparklines in cells A8 to D8

You can liven up your sparklines by added markers and changing line colours. We'll do that in the next part below.