How to Add Sparklines in Excel (Mini Charts in a Cell)

A full chart takes up a lot of room and only shows one or two series clearly. Sparklines are tiny charts that sit inside a single cell, one per row, so you can see the trend for every product, person or region at a glance, right next to the numbers.

Say you have 12 months of sales for 10 products, in B2:M11.

1. Select the numbers, B2:M11. Don't include the headings or product names.
2. Go to Insert > Sparklines and choose Line, Column or Win/Loss.
3. In Location Range, click the empty column where you want them, e.g. N2:N11.
4. Click OK.

Each row now has its own mini chart.

• Line: best for trends over time.
• Column: best for comparing values, like monthly totals.
• Win/Loss: shows every value as either up (positive) or down (negative), all the same size. Great for profit and loss, or wins and defeats.

Click a sparkline and the Sparkline tab appears. From here you can:

• Tick High Point and Low Point to mark the best and worst values.
• Tick First Point, Last Point or Negative Points.
• Tick Markers to put a dot on every value (line sparklines only).
• Change the Style, Sparkline Colour and Marker Colour.

By default, every sparkline is scaled to its own data. A product selling 10–20 units looks just as dramatic as one selling 1,000–2,000.

To compare them fairly:

1. Click a sparkline.
2. Go to Sparkline > Axis.
3. Under both Vertical Axis Minimum and Maximum, choose Same for All Sparklines.

Sparklines fill their cell, so resize the cell: widen the column and increase the row height.

• Sparkline > Edit Data lets you change the data range or the location.
• Sparkline > Line / Column / Win/Loss switches the type for the whole group.

Sparklines created together form a group, so formatting changes apply to all of them. To change just one, select it and click Ungroup.

If some months are blank, go to Sparkline > Edit Data > Hidden & Empty Cells to show gaps as a gap, a zero, or a line connecting the points.

Drag the fill handle down to copy sparklines to new rows, just like a formula. They adjust to each row's data.

Pressing Delete doesn't remove them. Instead:

1. Select the sparkline cells.
2. Go to Sparkline > Clear (click the arrow to clear a single one or the whole group).

The Sparklines buttons are greyed out. The file is in Compatibility Mode (an old .xls file). Save it as a .xlsx file.

All my sparklines look the same size even though the numbers are very different. Set the axis to Same for All Sparklines.

A sparkline is flat. All the values in that row are the same, or the row contains text instead of numbers.

Select your data, choose Insert > Sparklines > Line, pick an empty column, and every row gets its own trend chart. Turn on High and Low Point and set the axis to Same for All for an instant, honest comparison.