Data Bar Conditional Formatting in Excel: Setup and Tips

How-to basics · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Data Bars and pick a style. Excel adds an in-cell bar whose length represents each value relative to the values in the selected range; the cell’s number remains visible unless you choose to hide it.

How to add data bars

Data bars are a type of conditional formatting: Excel changes how a cell looks according to its value, without changing the stored value. A quick application takes only a few steps:

  1. Select the cells containing the values you want to compare. Include only the relevant data, not headings or totals unless you want those cells included in the scale.
  2. On the ribbon, choose Home > Conditional Formatting > Data Bars.
  3. Choose a gradient fill or solid fill from the gallery. The bars appear immediately in the selected cells.

For example, if B2:B6 contains 12, 25, 40, 55, and 80, applying data bars makes 80 the longest bar and 12 the shortest. The values themselves are still available for calculations, sorting, and charting. To use the same formatting on a separate range, select that range and apply the rule there, or use Format Painter to copy formatting.

On Excel for Mac, the comparable ribbon route is Home > Conditional Formatting > Data Bars. Excel for the web also offers data bars under Home > Conditional Formatting > Data Bars, though its rule-editing options may not match every control in the Windows desktop dialog.

How Excel scales the bars

By default, Excel scales bars against the lowest and highest values in the cells covered by the rule. The smallest value gets the shortest bar and the largest gets the longest. Values between them are shown proportionally. If a range runs from 10 to 100, a value of 55 sits about halfway between the endpoints for display purposes. This is a visual scale, not a conversion of the numbers into percentages.

The selected range matters. If you format each month’s sales column separately, each month may have its own minimum and maximum, so similarly sized bars can represent different amounts. If you want direct comparison across months, apply one rule to all the monthly values rather than creating a separate scale for each column. Conversely, separate rules can be useful when each group should be judged against its own range.

Blank cells generally show no bar. Text values do not behave like numeric measures, so keep labels and notes outside the range whenever possible. A zero is a numeric value and can affect the scale or axis, particularly when the range includes negative numbers. These details are one reason to select the intended data cells carefully rather than applying a rule to an entire worksheet column by habit.

Customizing a data bar rule

The gallery is convenient, but a custom rule gives you more control over the scale and appearance. In Windows Excel, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. Set the rules list to the current selection or the worksheet as appropriate, select the data bar rule, and choose Edit Rule. You can also open Home > Conditional Formatting > New Rule to create a rule with the desired formatting style.

In the rule dialog, choose a minimum and maximum type and, where required, enter a value. Depending on the rule and Excel version, scale endpoints can be based on automatic or lowest/highest values, a number, a percentage, a percentile, or a formula. A fixed numeric endpoint can make separate periods comparable: for example, setting a scale from 0 to 100 means a value of 50 uses roughly half the available bar width, even if the current data’s maximum is only 70. Use a fixed limit only when it makes sense for the data; values beyond the limit may all appear fully extended.

  • Bar color: Choose a fill color that remains distinct from the cell background and is readable in the workbook’s theme.
  • Gradient or solid fill: Gradient shading can look softer, while solid fill often gives a clearer comparison in dense tables or when printed.
  • Show Bar Only: This option hides the cell’s number while retaining the value underneath. Use it when a compact visual is more important than seeing exact figures in the grid.
  • Axis and direction: When the data includes negative and positive values, Excel can display bars extending on opposite sides of an axis. Direction settings can also control whether the bar grows from left to right or right to left.

After editing, check the rule’s Applies to range in Manage Rules. An incorrect range can leave new rows unformatted or include unrelated cells. If you want a rule to cover data added later, consider applying it to a suitable larger range or an Excel table, while avoiding an unnecessarily broad range that makes the scale confusing.

Choosing the right scale and appearance

Choose the scale based on the question the reader should answer. Automatic minimum and maximum values are useful for showing variation within one dataset. A fixed zero minimum is often better for measures such as units sold or completed tasks, because it anchors bar length to a meaningful baseline. If values are proportions or scores with a known range, fixed endpoints such as 0 and 100 can make comparisons more consistent across reporting periods.

Think about whether the display should emphasize rank or magnitude. Automatic scaling makes small differences visible when the values are tightly grouped, but it can make modest differences look dramatic. If values range only from 96 to 100, the shortest bar may still look fairly long; the visual difference is relative to that narrow range. A fixed minimum of zero gives more context about the absolute amount but may make differences between close values harder to see.

Keep the table easy to read. A bar should not obscure a number, and a colored bar should have enough contrast against the cell fill. Consider readers who may not distinguish colors easily: the number, a clear label, or another visual cue should still communicate the important information. Avoid assigning many unrelated colors to a single series without a reason, since the bar length is usually the main signal.

Useful examples and when bars help

Suppose a project tracker has task names in column A and completion percentages in B2:B8. Applying data bars to B2:B8 lets someone scan progress quickly: 25% appears shorter than 75%, while the percentage remains available in each cell. If these are true percentages stored as decimal values and formatted as percentages, Excel bases bar length on the underlying values, so 0.75 is greater than 0.25 as expected.

For a sales report with monthly values, data bars can help identify high and low results without replacing the numbers. Apply the rule to all months if the goal is to compare amounts on one scale. For a staff workload list, bars can make uneven assignments noticeable, but the range should not combine unrelated measures such as hours and employee counts. A bar encodes magnitude, not meaning: readers still need a header, units, and enough context to know what the values represent.

Data bars work best in a table where each row contains one comparable measurement. They are less suitable when the order of categories matters more than magnitude, when precise time-series patterns are central, or when the values are mostly text. A chart may communicate trends across many dates more clearly; a data bar is strongest as a compact aid alongside the data.

Common problems and fixes

  • Bars look almost identical: The values may be close together, or a few extreme values may set a wide scale. Review the minimum and maximum settings. A fixed scale can improve comparisons, but do not choose limits that misrepresent the data.
  • Some new rows have no bars: Open Home > Conditional Formatting > Manage Rules and inspect Applies to. Extend the range or use a table-based workflow so added records are included.
  • Bars seem inconsistent between sections: Each section may have its own rule and automatic scale. Apply a shared rule over the ranges you intend to compare, or use matching fixed endpoints.
  • Numbers disappeared: Edit the rule and clear Show Bar Only. The values are not deleted when hidden; that setting affects their display.
  • A total looks misleading: A grand total included in the same scale can become the longest bar and compress all the detail values. Exclude totals from the rule or format them separately.
  • Bars appear on the wrong cells: Check the selected range and the rule’s Applies to reference. Also look for other conditional-formatting rules that affect the same cells.

Conditional formatting rules can overlap. Manage Rules lets you inspect and edit them, and rule order can matter when multiple formats apply to the same cells. If a workbook has accumulated copied or duplicated rules, remove only the rules you understand and verify the result on representative cells. Clearing a rule removes its visual formatting, not the underlying values.

Data bars compared with other Excel formatting

Color scales shade whole cells according to their values, making it easy to spot high and low areas in a matrix. Icon sets place symbols such as arrows or traffic-light indicators in cells, usually grouping values into categories. Data bars instead show a length for each number, which is particularly useful for comparing quantities in a list while keeping the value visible.

Use a chart when the reader needs a labeled axis, a legend, a trend over time, or a comparison across many series. Use a simple number format when exact values are enough. A data bar is not a chart and usually does not provide a separate axis with labeled tick marks; it is a small visual cue inside the cell. For a compact dashboard table, bars can complement a chart, but duplicating the same information in several visual forms can make the page harder to interpret.

Frequently Asked Questions

Can I show the number and the data bar together?
Yes. That is the usual default: the bar appears in the cell and the numeric value remains visible. In the rule settings, leave Show Bar Only unchecked.

Do Excel data bars change the cell value?
No. Data bars are conditional formatting, so they change the cell’s appearance rather than its contents. Formulas, sorting, and other calculations continue to use the underlying number.

Can data bars show negative numbers?
Yes. Excel can show negative and positive values on opposite sides of an axis. Edit the rule’s bar settings to review the axis and direction, and make sure the selected range includes the values you intend to compare.

Can I make all the bars use the same scale?
Yes. Apply one rule across the ranges being compared, or edit the rule to use consistent fixed minimum and maximum values. Separate automatic rules can scale each group independently and therefore produce bars that are not directly comparable.

Why does a value of zero have no visible bar?
A zero has no positive length when the scale begins at zero, so it may appear as no bar. If the range contains negative values, the axis and bar direction determine how zero is displayed relative to the positive and negative bars.

Related

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.