Excel Data Bars: How to Use Conditional Formatting

In Excel, bar conditional formatting is called Data Bars: it fills each selected cell with a horizontal bar whose length represents the cell’s value relative to other values in the range. To add one in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Data Bars and pick a style.

What Excel data bars show

A data bar places a small visual comparison inside the cell while leaving the underlying number intact. A larger value generally gets a longer bar, so a range of sales, costs, scores, or completion totals can be scanned quickly without turning it into a separate chart. The cell still contains its original value, which remains available for calculations, sorting, filtering, and copying.

Data bars are a type of conditional formatting, not a chart object. They are attached to cells and respond when the values change. The bar length is determined by the rule’s scale and the values in its Applies to range. Because the bar is an at-a-glance comparison rather than a labeled axis, readers should still be able to see the numbers unless the context makes that unnecessary.

For example, if a column lists monthly sales of 18, 42, and 70, the value 70 will have the longest bar under a common automatic scale, while 18 will have a shorter one. The bars do not mean that 18 is 18 percent or that 70 is 70 percent. They encode relative magnitude according to the selected scale.

How to add data bars

Select the cells containing the values you want to compare. Usually, leave the column heading out of the selection.
On the ribbon, choose Home > Conditional Formatting > Data Bars.
Choose a color under Gradient Fill or Solid Fill. Excel applies the rule immediately to the selected range.
Check that the displayed bars make sense alongside the numbers, including the shortest and longest values.

Gradient Fill fades the bar toward one side; Solid Fill uses a uniform fill. The color choice affects appearance, not the underlying values or the scale. If you want to change the rule rather than just choose a preset, open Home > Conditional Formatting > Data Bars > More Rules. In the New Formatting Rule dialog, choose the Data Bar style and adjust the available settings, such as the minimum, maximum, color, direction, and whether to show the number.

To remove a rule from selected cells, use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose the option to clear rules from the entire sheet only when that is what you intend; it can remove formatting beyond the cells currently in view.

Choose a scale that supports the comparison

Automatic minimum and maximum settings are convenient for a single group: Excel uses the values in the rule’s range to size the bars. The result is easy to set up, but its meaning depends on which cells share the same rule. If January and February each have separately applied rules, the longest bar in both months can represent a different number. That makes the bars useful for comparing each month internally, but not necessarily for comparing one month’s bar directly with the other’s.

For comparisons across groups, apply one rule to all the values that should share a scale. Alternatively, use fixed bounds in More Rules. For instance, if every team’s score is out of 100, setting a minimum of 0 and a maximum of 100 gives the bars a consistent reference, even if one team’s range contains no score near 100. This makes cross-table comparisons more meaningful.

Use automatic bounds when comparing values within one coherent range and the range’s own spread is the main point.
Use fixed bounds when readers need to compare separate periods, departments, or reports against the same benchmark.
Review outliers before applying a scale. One unusually large value can make the rest of the bars look almost equal.

A fixed maximum does not change a cell’s value; it changes how much of the available bar width that value uses. If a value exceeds a chosen maximum, the displayed bar reaches the scale’s limit, so choose bounds that fit the data or explain why values may be capped visually.

Customize colors, direction, and displayed values

In the Data Bar rule settings, choose a fill color that contrasts with the cell background and remains distinguishable from adjacent text. A solid fill can look stronger in a dense table, while a gradient can make the bars less visually heavy. Keep the colors consistent when the same meaning appears in different columns or sheets.

The Show Bar Only option hides the cell’s number while retaining the bar. This can create a compact visual display, but it removes precise values from immediate view and may make close results difficult to distinguish. It does not delete the values; the cells still contain them. Consider leaving numbers visible in reports where exact amounts, scores, or counts matter.

Data bar direction controls which way bars extend. For ordinary positive values, bars usually extend from left to right. Text direction and negative values can affect how a rule is displayed. If the visual direction is not what you expect, inspect the rule’s settings rather than changing the underlying data simply to make the bars look right.

Cell number formats are separate from the bar style. A value can display as currency, a percentage, or a whole number while the bar is calculated from the stored numeric value. For example, currency formatting adds a currency symbol to the displayed number; it does not make the bar a currency unit or alter its relative scale.

How negative values are displayed

A range containing both positive and negative values needs a visual zero point. Excel data bars can show positive bars extending one way from an axis and negative bars extending the other way. In the rule settings, options for negative values and the axis let you adjust the negative fill and where the axis appears. The axis matters because a bar that extends left of zero represents a negative number, not a smaller positive number.

Consider a profit-and-loss column with positive and negative results. A centered axis can make gains and losses easy to distinguish, but it uses some horizontal space for each direction. If values are all positive, a central axis may add clutter without helping interpretation. Check the finished range, especially when the positive and negative magnitudes differ greatly, so that small losses do not become visually invisible.

Blank cells do not have a numeric value to represent and therefore do not show a meaningful data bar. Text entries are not treated as numeric magnitudes for the bar scale. If a number appears to have no bar, verify that it is stored as a number rather than text and that the conditional formatting rule includes that cell.

Data bars compared with color scales and charts

Data bars are best when the reader needs to compare magnitudes in a list and still connect each bar to its row. A color scale instead changes the cell’s fill color according to its value. It can reveal high and low values across a matrix, but does not show magnitude as a length. Icon sets use symbols such as arrows or flags to classify values into groups; they communicate categories or direction more than exact size.

A bar chart is a better choice when the comparison needs a visible axis, labels, a title, or a separate presentation area. It also gives more control over categories and series. Data bars are compact and convenient inside a table, but they have limited room for labels and do not replace a chart when the audience needs a clear, standalone graphic.

One common misconception is that a full-length data bar always means the value is 100 percent. It means the value reaches the maximum of that rule’s scale. That maximum may be the largest value in the selected range, or it may be a fixed number chosen in the rule settings. Check the scale before interpreting the bar as a percentage of a target.

Common problems and how to fix them

Bars look nearly identical: Check for an outlier setting the scale. Consider fixed bounds or a separate range if the purpose is to compare typical values rather than extremes.
Some cells have no bars: Check that the cells are included in the rule, contain numeric values, and are not being treated as text. Also inspect whether the rule has been set to show bars only or has unexpected bounds.
The values are hidden: Open the rule and turn off Show Bar Only if you want numbers displayed with the bars.
Bars use different scales in different sections: Review the Applies to range in Conditional Formatting Rules Manager. A copied rule may have been split or limited to a smaller selection.
Unexpected formatting remains: Open Home > Conditional Formatting > Manage Rules and inspect the rules for the current selection or worksheet. Rules can overlap, and their order or scope can affect the result.
New rows do not receive bars: Check whether the Applies to range includes those rows. If the data is maintained as an Excel table, applying formatting to the table’s data column can make ongoing additions easier to manage, but verify the rule after adding rows.

When troubleshooting, select a cell that looks wrong and use Manage Rules to see which rule applies and what range it covers. This is usually more reliable than repeatedly applying a new preset, which can leave several overlapping rules behind.

Keep data bars useful in shared workbooks

Conditional formatting updates as values change, so a bar may grow, shrink, or disappear when new data is entered. That is useful for live reports, but it also means the visual scale can shift over time if its bounds are automatic. For a recurring report, decide whether the intent is to show the current range’s distribution or to compare results with a stable target. Then set the scale and label the context accordingly.

Accessibility and readability matter. Do not rely on color alone to communicate whether a result is good or bad; retain the number or add a clear heading, legend, or separate status indicator where needed. Choose colors with adequate contrast, and avoid applying bars to a very wide range of unrelated measures. A bar comparing dollars with percentages, for example, is not meaningful merely because both values are numeric.

On Excel for Mac, the Conditional Formatting controls are also on the Home tab, though the ribbon layout and dialog presentation can vary by release. Excel for the web supports conditional formatting, but some advanced rule controls may differ from the desktop dialog. If you cannot find an advanced setting such as a custom bound or negative-value option, open the workbook in the desktop version of Excel and check the rule there.

Frequently Asked Questions

Where is conditional formatting for bars in Excel?

Select the cells, then choose Home > Conditional Formatting > Data Bars. Pick a Gradient Fill or Solid Fill preset. For detailed settings, choose Home > Conditional Formatting > Data Bars > More Rules.

Can I show the number and the bar in the same Excel cell?

Yes. The number remains visible by default when you apply a data bar. In the rule settings, make sure Show Bar Only is not selected. The cell continues to hold its numeric value either way.

Can I make Excel data bars start at zero?

Yes. Open the rule through More Rules and set the minimum bound to a fixed number of 0. This is useful when zero is the meaningful baseline or when separate ranges need a consistent scale. Confirm that the chosen maximum is also appropriate for the comparison.

Why are all my data bars different lengths from another column?

Each rule may be scaling its own range independently. If you need bars in both columns to use the same reference, apply a shared rule to the combined values or set matching fixed minimum and maximum bounds for both rules.

Do data bars work with negative numbers?

Yes. Excel can display negative bars on the opposite side of an axis from positive bars. Open the rule settings to review the negative fill and axis options, then check the result in the cells where values cross zero.

Are data bars the same as a bar chart?

No. Data bars are conditional formatting within worksheet cells and are useful for compact comparisons in a table. A bar chart is a separate chart object with more options for axes, labels, titles, and presentation.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.