Skip to content
Tutorials Center
My learning

Topics

Lesson 58 of 93 ยท Charts and Visualization

Sparklines, Data Bars and Icon Sets

Add tiny charts and visual cues inside cells with sparklines, data bars, icon sets and color scales, with a runnable text-rendering stand-in.

A full chart takes space and sits on top of the sheet. Sometimes you want something smaller: a miniature trend next to each product, a bar inside each cell that shows size at a glance, a red, amber or green sign beside a result. Excel has three in-cell visual tools for this: sparklines, data bars and icon sets, and a fourth close relative, color scales.

All of them live in the cell grid, so they sort, filter and copy with your data. Excel cannot run on this site, so the menu steps are described in text. The JavaScript stand-ins render the same ideas as text, with block characters and symbols. They show the logic of each feature, not Excel's graphics. Data bars, icon sets and color scales are part of conditional formatting, which is covered in depth in Conditional Formatting.

Charts inside cells

Sparkline A tiny chart in one cell.
Data bar A bar that fills the cell by value.
Icon set Arrows, lights or flags by threshold.
Color scale Cell color follows the number.

Four ways to see patterns in a table.

1 / 2

Sparklines

A sparkline is a tiny chart drawn inside a single cell, ideally one per row. Select the cells where the sparklines should appear, then use Insert > Sparklines and choose the type. In the dialog, give the Data Range (for example B2:G2, the monthly numbers of the first row) and the Location Range (for example H2). Excel creates the sparklines as one group.

There are three types:

  • Line: a small line chart of the trend. Option for markers on every point.
  • Column: tiny bars, good for comparing values in a short series.
  • Win/Loss: every positive number is a bar up and every negative a bar down, all the same height. It shows the sign only, which suits results such as gains and losses by month.

Once a sparkline exists, a Sparkline tab appears. Useful options:

  • High Point, Low Point, First Point, Last Point, Negative Points, Markers: highlight those points in different colors.
  • Axis > Vertical Axis Minimum/Maximum Value: by default each sparkline has its own scale. Choose Same for All Sparklines to compare rows honestly. Otherwise a tiny product and a huge one both look equally tall.
  • Edit Data > Hidden & Empty Cells: whether gaps are drawn as gaps, zero, or connected.
  • Clear: removes sparklines. Pressing Delete in the cell does not delete them.

Facts to remember: sparklines are not objects floating on the sheet; they are drawn in the cell background, and you can still type or use formulas in the same cell. They have no axis labels, titles or legends, so they show shape, not exact numbers. Put the real numbers next to them. Because they are tied to the cell, resizing the row or column resizes the sparkline.

Data bars

Data bars fill part of a cell with a bar whose length depends on the value. Select the numbers and choose Home > Conditional Formatting > Data Bars. Pick a gradient or solid fill.

  • By default the smallest and biggest values define the scale. Under Manage Rules > Edit Rule you can set the type for the minimum and maximum to Number, Percent, Formula and so on. Set the minimum to a Number of 0 if bar lengths should be proportional to the values, otherwise the lowest value gets a very short bar and the differences are exaggerated.
  • Negative numbers get a bar going the other way from an axis, in a separate color.
  • Tick Show Bar Only to hide the numbers, though in most cases the numbers should stay.

Data bars are great for ranking tables: product revenue, support tickets per team, remaining stock. Because they sit behind the text, widen the column a little so the bar has room.

Icon sets

Icon sets put a small symbol in the cell based on where the value falls: arrows (up, sideways, down), traffic lights, flags, stars, check marks, ratings. Choose Home > Conditional Formatting > Icon Sets.

With a three-icon set, the default rule is:

  • The top icon for values greater than or equal to 67% of the way between the lowest and highest value in the range.
  • The middle icon for values from 33% up to that.
  • The bottom icon for everything below 33%.

Note that percent here means a share of the distance between minimum and maximum, not a percentile and not a percentage of the cell value. In Edit Rule you can change each threshold's type to Number, Percent, Percentile or Formula, reverse the icon order and tick Show Icon Only. For a status such as "on target", a fixed Number threshold is usually clearer than a relative one: green if at least 100, amber if at least 80.

Do not rely on color alone: choose an icon set with different shapes (arrows or check marks) for readers who cannot tell colors apart.

Color scales

A color scale shades cells from one color to another. A two-color scale goes from a minimum color to a maximum color. A three-color scale adds a midpoint, by default at the 50th percentile, and is used for green-yellow-red scales. A table filled with a color scale is a heat map: hot spots stand out immediately. Choose it under Conditional Formatting > Color Scales.

Pick a diverging scale (two colors meeting at a neutral midpoint) when the data has a natural center such as zero or a target, and a single-hue scale when it simply goes from low to high.

Stand-in: rendering the four visuals as text

The code below takes a small table and renders a sparkline with block characters, a data bar with # characters, an icon set with the default 67% and 33% rule, and a color-scale shade. It is our own simplified model of the logic.

JS Sparklines, data bars, icons and color scales as text

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stand-in: why the shared scale matters

Each sparkline normally uses its own scale, which can mislead. This second example draws the same rows twice, once with each row's own scale and once with one common scale, which is the Same for All Sparklines option.

JS Own scale versus the same scale for all sparklines

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Practical tips

  • Put the numbers beside the visuals. In-cell graphics show patterns, not exact values.
  • Use one visual per column. A table with bars, icons and colors in every column is hard to read.
  • Convert the range to an Excel Table so sparklines and rules extend automatically when you add rows. Conditional formats follow the table. Sparklines may need to be filled down by dragging the first cell's fill handle.
  • Check Manage Rules if icons or bars look wrong: overlapping rules from copy and paste are common.
  • Printing and sharing: all four features print as seen on screen and work in current Excel for the web, though exact styles can differ between versions.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a win/loss sparkline show?

    Win/loss sparklines ignore magnitude and draw every positive value as an upward bar and every negative as a downward bar.
  2. 2. You select a cell holding a sparkline and press Delete, but the sparkline stays. What should you use?

    Sparklines are not cell contents, so Delete does not remove them. Use the Clear command on the Sparkline tab.
  3. 3. With the default three-icon set, which value gets the top icon?

    Default thresholds use Percent of the range. 67% and up gets the top icon, 33% and up the middle, the rest the bottom.
  4. 4. Why set the minimum of a data bar rule to the number 0?

    If the minimum is the lowest value in the range, that cell gets a tiny bar and differences look larger than they are. Zero keeps the proportions honest.
  5. 5. How can you compare many sparklines fairly?

    Each sparkline normally scales to its own data. A shared axis shows real differences in size.

Summary

  • Sparklines are tiny line, column or win/loss charts in a single cell; use the same axis for all of them to compare rows, and remove them with Clear.
  • Data bars show size inside cells; set the minimum to 0 when proportions matter.
  • Icon sets assign a symbol by threshold; the default thresholds are 67% and 33% of the min-to-max range, and fixed numbers are better for targets.
  • Color scales shade cells by value and make heat maps.
  • Keep the numbers visible, use one visual per column, and prefer shapes over color alone.

Next: Conditional Formatting.