In-Cell Data Bars

Excel Formulas › Conditional Formatting

All versionsData bars

Turn a column of numbers into a mini bar chart right inside the cells. Data bars fill each cell proportionally — instant visual ranking with no chart object to manage.

Quick formula: select your numbers, then:
Home → Conditional Formatting → Data Bars → pick a style
Excel scales the longest bar to the largest value and shrinks the rest proportionally. No formula required.

The example

Monthly sales with proportional bars.

AB
1MonthSales (bar)
2Jan████████ 80
3Feb█████ 50
4Mar██████████ 100
5Apr███ 30

The formula

Data bars are a built-in rule — no formula, but you control the scale:

Conditional Formatting → Data Bars → More Rules Minimum: Number 0 Maximum: Number 100 // fixed 0–100 scale for fair comparison

How it works

Data bars draw a gradient or solid fill sized to each value:

  1. Select the numeric range, then Home → Conditional Formatting → Data Bars and choose a gradient or solid style.
  2. By default Excel sets the shortest bar to the minimum value and the longest to the maximum — relative to the selection.
  3. For a fair, fixed scale, open More Rules and set Minimum = Number 0 and Maximum = Number (e.g. 100 or a target). Now bar length means the same thing every time.
  4. Tick Show Bar Only to hide the numbers and keep just the bars for a clean dashboard look.

Negative values? Data bars handle them — set the axis to “Cell midpoint” so positives go right and negatives go left from the center. Great for variance columns.

Try it: interactive demo

Live demo

Edit the values; bars scale to the max.

Variations

Fixed scale (fair bars)

Set Min/Max to numbers, not Auto:

Min: Number 0 Max: Number 100

Bar only (hide numbers)

Check “Show Bar Only” in the rule.

Percent-of-target scale

Max = a target cell for “% to goal” bars.

Pitfalls & errors

Auto scale can mislead. With Automatic min/max, the same value draws different bar lengths in different ranges. Use a fixed Number scale when comparing across tables.

Bars compete with text. Long numbers can overlap the bar fill; widen the column or use “Show Bar Only” in a helper column.

Not a real chart. Data bars don’t show axes or precise values — they’re for at-a-glance ranking, not exact reading.

Practice workbook

📊
Download the free In-Cell Data Bars practice workbook
A sheet with data bars applied (auto and fixed-scale), a negative-variance example, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I add data bars in Excel?
Select the numbers, then Home → Conditional Formatting → Data Bars and pick a style. Excel fills each cell proportionally to its value.
How do I make the bars use a fixed scale?
Open Conditional Formatting → Manage Rules → Edit, and set Minimum and Maximum to Number (e.g. 0 and 100) instead of Automatic, so bar length is comparable across tables.
Can data bars show negative values?
Yes. In the rule's options, set the axis to Cell Midpoint so positive bars go right and negative bars go left from the center.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Color-scale heatmap · Highlight top N · Repeat text as bars