Sparklines in Microsoft Excel

Sparklines in Microsoft Excel

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

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:

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 theCells panel on the Home ribbon at the top of Excel:

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

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

Row 8 on your spreadsheet should now look like this:

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:

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:

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:

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:

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:

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

Posted in: Microsoft Excel,Microsoft Excel 3 (Excel 2007/2010 Charts),Tips,Trick,Tutorial,Tutorial Excel 3