Data Bars, Color Scales and Icon Sets in Excel

Data bars in Excel turn a column of numbers into small bar charts inside the cells, so the biggest and smallest values jump out at a glance. Color scales and icon sets do the same job with shading and symbols. All three are conditional formatting rules: they update when the numbers change. This guide shows how to add each one and how to set it up so it tells the truth.

Data bars, color scales and icon sets: what's the difference?#

All three live under Home > Conditional Formatting, and all three compare each cell with the rest of the selection:

The examples use this list in A1:C9:

For highlight rules such as "greater than 5,000", see the beginner's guide to conditional formatting instead.

How to add data bars in Excel#

1. Select the numbers only: C2:C9. Leave out the header and any total row.
2. Go to Home > Conditional Formatting > Data Bars.
3. Hover over the options to preview them, then click one under Gradient Fill or Solid Fill.

Gus, at 7,400, gets the longest bar. Dev, at 950, gets a short one. When all the values are positive, each bar's length is roughly the value divided by the largest value, so Ana's bar is about 4,200 / 7,400 = 57% of Gus's. The steps are the same in Excel for Mac.

Never include the total. If C10 holds the total of 33,450 and you select C2:C10, the total gets a full bar and every rep's bar shrinks to a sliver. Select only the values you want to compare.

Change how data bars look#

Go to Home > Conditional Formatting > Manage Rules, pick the data bar rule and click Edit Rule. Useful settings:

• Show Bar Only hides the numbers and leaves just the bars.
• Minimum and Maximum set where a bar starts and ends. Choose Number and type 0 and 10000 to compare against a fixed scale, such as a target.
• Fill and Border switch between gradient and solid, and set the colour.
• Negative Value and Axis sets the colour for negative bars and where the axis sits.

How to add a color scale#

A color scale shades every cell by where it sits between the lowest and highest values.

1. Select C2:C9.
2. Go to Home > Conditional Formatting > Color Scales.
3. Pick a two-colour or three-colour scale.

On a green-yellow-red scale, Gus's 7,400 turns green, Dev's 950 turns red, and the rest get shades in between. On a three-colour scale, the middle colour sits at the 50th percentile by default: half the values are above it.

Choose colours that match the meaning. For sales, high is good, so high should be green. For costs or late days, high is bad, so pick the scale with red at the top. To swap colours on an existing rule, edit it and change the Minimum and Maximum colours. Color scales work best on big grids, such as sales by month and region, where they act as a heat map.

How to add icon sets and set your own thresholds#

Icon sets add a symbol to each cell based on thresholds.

1. Select C2:C9.
2. Go to Home > Conditional Formatting > Icon Sets and pick a set, such as 3 Traffic Lights.

By default, the thresholds are Percent values of 67 and 33. Percent here means a position between the lowest and highest value, not a share of the total. With a low of 950 and a high of 7,400, the green cut-off is about 5,272 and the yellow cut-off about 3,079. So Gus, Cara and Eli get green, Hana and Ana get yellow, and Femi, Ben and Dev get red.

Usually you want real targets instead. Edit the rule and, for each icon, change Type to Number:

Now green means "sold at least 5,000". Tick Show Icon Only to hide the numbers, or click Reverse Icon Order when low values are the good ones.

Sort and filter by colour or icon#

Once the formatting is on, you can sort or filter by it. Turn on the filter buttons with Ctrl+Shift+L (Mac: ⌘⇧F), click the arrow on the Sales column, and choose Filter by Color to show, say, only red icons or one fill colour. In the Sort dialog, set Sort On to Cell Color or Conditional Formatting Icon to bring one group to the top. The guides to filtering data and sorting data cover these menus in full.

Common mistakes with data bars and color scales#

• A total in the range. It stretches the scale. Remove it from Applies to in Manage Rules.
• Text that looks like numbers. Cells holding "4200" as text get no bar. Convert them to numbers.
• Bars that all look the same. The minimum may be set to the lowest value or a high number. Set Minimum to Number 0.
• Too many rules. A data bar, a color scale and icons on the same cells is hard to read. Use one.
• Colours with no key. Readers may not know what green means. Add a short note above the table.

Formula alternatives and next steps#

You can also draw bars with a formula. =REPT("|",C2/500) repeats a character once per 500 in sales, which works anywhere, even in exported text. See in-cell bar charts with REPT. Data bars also look great beside a summary table built with formulas, as in summarizing data without a pivot table.

Practise it#

Visual formatting is quick to apply, and tests check that you pick the right range and rule. Practise conditional formatting in a live spreadsheet: highlight values over a limit, duplicates, the top 3 and whole rows, with instant checks. Try the bar-length exercise on this page first.