Conditional Formatting Bar Chart in Excel: How It Works

Charts & pivot tables · Updated November 19, 2026

To make a bar chart inside Excel cells, select the values, then choose Home > Conditional Formatting > Data Bars and pick a style. Excel adds a bar to each selected cell; the values remain in the cells, so this is an in-cell visual aid rather than a separate chart object.

What an Excel data bar shows

Excel’s Data Bars feature is one type of conditional formatting. It displays a horizontal bar inside each cell, with the bar’s length representing that cell’s value relative to other values in the rule’s range. A longer bar means a larger value on the scale being used. The cell’s actual contents are not changed: a cell containing 72 still contains 72, and you can use it in calculations, sort it, or edit it as usual.

Data bars are useful when readers need to compare values quickly without looking away from a table. For example, a sales report can show each salesperson’s revenue alongside the corresponding bar. They work best for one numeric measure per row and a reasonably clear comparison group. They are not the same as a clustered or stacked chart, which can compare multiple series, add axes and labels, and be positioned separately on a worksheet.

Data bars can use a gradient fill or a solid fill, and Excel offers several preset colors. You can also edit a rule’s settings to control its scale, color, direction, and whether the number is displayed. These choices matter: a bar is only meaningful when its scale and comparison range suit the data.

Add data bars to a range

In Excel for Microsoft 365 on Windows, use these steps to apply a preset:

  1. Select the cells that contain the numbers you want to compare, such as B2:B13. Avoid selecting a column heading unless you intend to include it in the formatting range.
  2. Choose Home > Conditional Formatting > Data Bars.
  3. Choose a gradient-fill or solid-fill preset, then select a color.

Excel applies a rule to the selected cells immediately. The bar appears behind the cell value, and the formatting changes as the values change. If you add more rows later, the new cells may not be covered automatically; check the rule’s Applies to range or extend the range when needed.

To create a customized rule, select the cells and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose the data-bar rule type, then set the options you need and select OK. The dialog’s exact layout can vary slightly with Excel updates, but the same rule settings are available through the conditional-formatting controls. To revise a rule later, use Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule.

Choose a useful comparison scale

By default, Excel chooses the scale from the values in the rule’s range. The shortest and longest bars are determined by the minimum and maximum values in that group. This is convenient for a quick comparison, but it can make small differences look more dramatic: if all monthly sales figures are close together, the smallest value may still get a very short bar and the largest a nearly full-width bar.

For comparisons across different tables or reporting periods, consider setting a consistent minimum and maximum in the rule. For instance, if every department is measured against a 100-point score, a shared scale from 0 to 100 makes separate groups easier to compare. If each group instead uses its own automatic scale, a score of 80 could have a shorter bar than a score of 60 in another group, even though 80 is numerically higher. The scale is part of the message, so choose it deliberately.

  • Automatic limits: Useful for showing relative rank within one range. The bars adjust to the values found there.
  • Fixed numeric limits: Useful when the values have a meaningful common floor and ceiling, such as 0 and a target of 100.
  • Percentile-based limits: Can reduce the influence of unusually high or low values when setting the visual range. Check how outliers appear before relying on this choice.

When entering a fixed minimum or maximum, make sure the chosen limits make sense for the units. A 0-to-100 scale is appropriate for percentages or scores on that scale, but not automatically for currency figures or quantities with a different range.

Display the number, color, and direction

By default, Excel displays both the bar and the cell value. That is usually the most informative choice because readers can see the exact number as well as its relative size. If a compact dashboard needs to emphasize the visual comparison, edit the rule and select Show Bar Only. The number remains in the cell for calculations, although it is hidden by the conditional format. This option can make a table harder to use if readers need precise figures, so consider keeping the values visible or adding a clear label.

Use color to distinguish a measure or draw attention, not as a substitute for the value itself. A single color is usually easiest to scan in a comparison column. If the worksheet already uses color to communicate another status, avoid creating a confusing second meaning. Excel’s preset choices provide a quick start; the rule editor lets you change the bar color and fill style.

Negative numbers need special attention. A standard left-to-right bar is intuitive for values above zero, but negative and positive values may need to extend in opposite directions from an axis. In the data-bar rule settings, use the negative-value and axis options to control how that comparison appears. Check the result with positive, zero, and negative test values; the axis position affects how readers interpret bar lengths.

Examples of when data bars help

Imagine a small sales table with salesperson names in column A and monthly revenue in column B. Applying data bars to B2:B13 lets a manager scan the same table for larger and smaller totals while retaining the exact revenue figures. If the report is repeated each month and values are compared across months, a fixed scale may be more consistent than allowing each month’s range to set its own maximum.

Data bars also suit progress measures, such as the number of completed tasks per project, or inventory quantities when larger values consistently mean more stock. They are less suitable when the numbers do not have a straightforward “more is larger” interpretation. For example, a longer bar for a higher error count might be visually prominent but is not necessarily good news. Add a clear heading and, if needed, a separate status format so the direction of the measure is unambiguous.

For a table containing several unrelated measures, apply separate rules to their own columns rather than comparing everything together. A percentage, a dollar amount, and a count have different units; placing them in one rule range would create a misleading scale.

Data bars versus a chart object

A data bar is an efficient way to add a small visual comparison directly to a table. It follows the cell and is easy to apply to many rows. A chart object is better when the reader needs a title, axis labels, a legend, multiple data series, a specific ordering, or a view that can be moved and resized independently. Choose based on the task rather than assuming that every bar-shaped display is the same kind of chart.

  • Choose data bars for compact, row-by-row comparisons that belong beside the numbers.
  • Choose a bar chart when categories and values need a dedicated visual, labels, or additional series.
  • Choose a simple table without bars when exact figures matter more than a visual ranking or the range is too small to distinguish clearly.

Data bars also do not create a separate chart to copy elsewhere or print independently. They are formatting on cells. If you need a chart with a chart title and axes, select the data and use Excel’s chart tools instead.

Common problems and how to fix them

If a bar appears missing or unexpectedly short, first check the rule’s range and scale. A value may be small relative to an outlier, or the selected rule may cover a different set of cells than intended. Open Home > Conditional Formatting > Manage Rules and inspect Applies to, then edit the rule’s minimum and maximum if the automatic scale does not serve the comparison.

If bars seem inconsistent between columns, verify that each column has its own appropriate rule and scale. Also check that the cells contain numbers rather than text that merely looks numeric. A value imported as text may not behave as expected in numeric conditional formatting. Excel’s number format can display a numeric value as currency or a percentage without changing its underlying value, but text entries are different.

If new rows lack bars, expand the rule’s Applies to range to include them, or create the rule after selecting the complete data range. If multiple rules overlap, Manage Rules helps identify which ranges and formats are involved. You can remove a rule through Home > Conditional Formatting > Clear Rules, but choose carefully: the menu offers options that clear rules from selected cells or from the entire worksheet.

Finally, inspect the worksheet at the size and contrast at which it will be read. Very pale fills, narrow columns, and hidden numbers can make a useful comparison difficult to interpret. Use a readable fill color and keep enough column width for the bars and any displayed values.

Excel version and platform notes

The steps above are for Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac also provides conditional-formatting data bars from the Home tab; the menu presentation and dialog details can differ slightly from Windows. Excel for the web includes conditional formatting and data-bar options, though some advanced rule-editing controls may be more limited or arranged differently than in the desktop app. If you cannot find a particular scale or negative-value setting in the web interface, open the workbook in the desktop version and edit the rule there.

When sharing a workbook, remember that conditional formatting belongs to the worksheet cells. The recipient needs an app that supports the formatting to see the bars as intended. The underlying values are still the important data; the bars are a visual layer and should not be the only way to communicate a critical result.

Frequently Asked Questions

Can I make a bar chart inside an Excel cell?
Yes. Apply Home > Conditional Formatting > Data Bars to numeric cells. Excel displays a bar within each cell, but it does not create a separate chart object.

How do I show only the bars and hide the numbers?
Edit the data-bar rule through Home > Conditional Formatting > Manage Rules, choose Edit Rule, and select Show Bar Only if that option is available in the rule dialog. The cell values remain present even when their display is hidden.

Why are all my bars nearly the same length?
The values may be very similar, or the rule may use a scale wider than the data range. Review the minimum and maximum settings. A narrower fixed scale can make differences easier to see, but use limits that are fair and clear to readers.

Can data bars show negative numbers?
Yes. Edit the rule’s negative-value and axis settings to define how negative and positive bars are displayed. Test the formatting with values on both sides of zero so the direction and baseline are clear.

Do data bars update when I change a value?
Yes. Conditional formatting recalculates the bar display when values in the applied range change. If you add new rows outside that range, you may need to extend the rule’s Applies to range.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.