Sparklines in Excel: Tiny Charts Inside Cells

Sparklines in Excel are tiny charts that live inside a single cell. Each one shows the shape of one row of numbers, so a column of sparklines next to a table lets you see at a glance which products are rising, falling or bouncing around. This guide shows how to insert them, which type to choose, how to make rows comparable, and how to remove them, all on one small sales table.

What are sparklines in Excel?#

A sparkline is a mini chart drawn in the background of a cell. Unlike a normal chart, it has no title, legend or axis labels. It shows only the trend. Sparklines arrived in Excel 2010 and are in every later version, including Microsoft 365 and Excel for Mac.

There are three types:

All examples use this quarterly sales table in A1:E5, with sparklines going in column F:

Sparklines need one row (or column) of numbers per item. If your data is a long list of individual sales, summarise it first into a table like this one. The summary table lesson shows how to build one with SUMIFS.

How to insert sparklines#

You can add a whole column of sparklines in one step:

1. Select the numbers, B2:E5. Don't include the headers or product names.
2. Go to Insert > Sparklines and click Line.
3. In the Create Sparklines box, the Data Range already shows B2:E5.
4. Click in Location Range and select F2:F5.
5. Click OK.

Each cell in F2:F5 now holds a small line for its row. Laptops rise with a dip in Q3, Monitors peak in Q3, and Mice drift down. Widen column F and make the rows taller to see the shapes more clearly, because a sparkline always fills its cell.

You can also make one sparkline and fill it down. Create it in F2 from B2:E2, then drag the fill handle to F5. Like a formula, the data range moves down one row for each cell.

How to show high and low points#

A plain line shows the shape but not the details. When you click a sparkline, the Sparkline tab appears (in older versions it is called Sparkline Tools > Design). Its Show group has checkboxes that add markers:

• High Point and Low Point: mark the biggest and smallest value.
• First Point and Last Point: mark where the row starts and ends.
• Negative Points: mark values below zero.
• Markers: put a dot on every value (Line sparklines only).

Ticking High Point on the Monitors sparkline puts a dot on Q3. You can find the same thing with a formula, which is useful when you need the answer as text in a report:

=INDEX(B1:E1,MATCH(MAX(B3:E3),B3:E3,0))

MAX finds 110, MATCH finds that it is the 3rd value, and INDEX returns the 3rd header: Q3. Use Sparkline Color and Marker Color on the same tab to change the colours. A darker marker colour on a lighter line makes the high point easy to spot.

How to make sparklines comparable#

By default, each sparkline is scaled on its own. That means the Mice line, which moves between 38 and 50, fills its cell just as fully as Laptops, which move between 120 and 160. The shapes are right, but the sizes can't be compared.

To fix it, click a sparkline in the group, then on the Sparkline tab choose Axis:

1. Under Vertical Axis Minimum Value Options, choose Same for All Sparklines.
2. Under Vertical Axis Maximum Value Options, choose Same for All Sparklines.

Now every sparkline uses the same scale, so Laptops sit high in their cells and Mice sit low. Use this whenever readers will compare rows. Keep the default when only the shape of each row matters.

This setting matters most for Column sparklines. With automatic scaling, the smallest bar in each row can look almost empty even when the value is large.

When to use Win/Loss sparklines#

Win/Loss sparklines ignore size and show only the sign: a block up for a positive number, a block down for a negative one. They suit data such as month-on-month change, wins and losses, or budget over and under.

For Win/Loss, you need numbers that can be negative. Work out the change from each quarter to the next first. For Mice, the changes from Q1 to Q4 are +5, -10 and -2, so the Win/Loss sparkline shows one block up and two down. A Line sparkline on the raw sales would hide how often the product fell.

How to change, group and remove sparklines#

Sparklines made in one step form a group. Changing the type, colour or markers on one changes them all. To style one differently, select it and click Ungroup on the Sparkline tab.

Other useful buttons on the Sparkline tab:

• Edit Data changes the data range or location. Its Hidden & Empty Cells option chooses whether gaps show as gaps, as zero, or as a joined line.
• Line, Column and Win/Loss switch the type.
• Clear removes the selected sparklines.

Pressing Delete does not remove a sparkline, because it is not cell content. Use Clear, or delete the row or column it sits in.

Sparklines vs charts, data bars and REPT bars#

Sparklines are one of several ways to show data visually inside a sheet. Pick by what the reader needs:

• A full chart shows exact values, labels and several series together. See how to make a chart in Excel or a bar chart in Excel.
• Data bars shade each cell by its own value, which compares one number per row. The guide to data bars and colour scales explains them, and they are part of conditional formatting in Excel.
• REPT bars build a text bar with a formula, which works anywhere, including in files where formatting is lost. See simple in-cell bar charts with REPT.
• Sparklines show the trend of a whole row in one cell.

A common dashboard pattern uses all three: the latest number, a data bar for its size and a sparkline for its trend.

Practise it#

Try the exercise on this page: one formula finds the high point a sparkline would mark. Then practise conditional formatting in a live spreadsheet, where you type each rule and it checks it instantly, and build the summary tables that sparklines need in the summary table lesson.