Excel Conditional Formatting Data Bars: How to Use Them

In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Data Bars and pick a style. Excel adds a bar inside each cell to show its value relative to the other values in the selected range; the underlying numbers remain unchanged.

What conditional formatting data bars show

Data bars are a visual conditional-formatting rule: each cell receives a horizontal bar whose length represents its value. A longer bar means a larger value in the comparison range, while a shorter bar means a smaller one. By default, the numbers remain visible in the cells, so a reader can see both the exact value and its relative size.

For example, suppose B2:B6 contains monthly sales of 12, 18, 24, 30, and 36. A data bar rule makes 36 the longest bar and 12 the shortest. It does not convert those values to percentages, add a chart, or change the data. It simply makes the pattern easier to scan in the worksheet.

Data bars are useful for compact comparisons in tables, such as sales by product, task completion, inventory levels, or scores. They work best when the cells contain comparable numeric quantities. If the numbers use different units or scales, bar lengths can suggest a comparison that is not meaningful.

How to add data bars in Excel

On Windows, use the following steps in Excel for Microsoft 365:

Select the cells containing the values to visualize. Exclude headings if they are text labels.
Choose Home > Conditional Formatting > Data Bars.
Choose a preset under Gradient Fill or Solid Fill. The preview is applied to the selected range.

Excel creates a rule for the selected cells. You can change its appearance or the way it calculates the bar length later. To edit it, choose Home > Conditional Formatting > Manage Rules, select the data-bar rule, and choose Edit Rule. In Manage Rules, check the Show formatting rules for setting if the rule is not visible; the selection, current worksheet, and workbook options determine which rules are listed.

In Excel for Mac, the basic route is also Home > Conditional Formatting > Data Bars, though the ribbon layout and some dialog details can differ by release. Excel for the web supports conditional formatting and data bars, but its editing controls may not expose every option available in the Windows desktop dialog.

How Excel scales the bars

By default, Excel uses the lowest and highest values in the rule’s range as the scale endpoints. The minimum gets the shortest bar and the maximum gets the longest. Intermediate values receive lengths between those endpoints. If every value is the same, the bars cannot distinguish one cell from another because there is no difference in magnitude to represent.

This default is relative, not a universal scale. If one department’s values range from 10 to 20 and another’s from 100 to 200, separate rules can make each range appear equally varied even though the second department’s numbers are much larger. Likewise, adding a large outlier can compress the apparent differences among the remaining cells.

When editing a rule, you can set the minimum and maximum boundary types rather than relying only on the automatic endpoints. A numeric boundary is useful when the same benchmark should apply across multiple ranges. For instance, setting a common maximum can make bars in separate monthly tables easier to compare. A percentile boundary can reduce the influence of extreme values on the visual scale. Choose boundaries based on what the reader should infer, not simply on which setting makes the bars look dramatic.

Customize color, direction, and labels

The preset gallery offers gradient and solid fills. Gradient fill changes shade across the bar; solid fill uses a consistent color. For a report with several categories, a restrained color choice can make the table easier to read, but color alone should not carry information that readers need to understand. The numeric values should remain available unless there is a specific reason to hide them.

In the Edit Formatting Rule dialog, the options can include bar color, fill, border, bar direction, and whether to show the cell value. The exact controls depend on the Excel version and rule type. Turn on Show Bar Only when the bars themselves are more useful than the displayed numbers, such as in a compact progress display. Otherwise, leave the values visible; a bar is an approximate visual cue and does not replace reading an exact amount.

Direction matters when a range contains negative numbers or when a right-to-left display is appropriate. With positive and negative values, Excel can show bars extending on opposite sides of an axis. The axis marks the zero point, so a negative number is not simply treated as a small positive number. Review the preview and the actual worksheet after changing the direction or axis-related settings, particularly if the range includes both positive and negative values.

Choose a useful range and comparison

The selected range defines which values participate in a rule’s scale. Including a total row, a target, or a value from another unit can change every bar’s apparent length. Decide what constitutes a fair comparison before applying formatting.

Compare like with like: A bar for revenue is meaningful beside other revenue values, but not necessarily beside a percentage or a count.
Keep headings out: Text headings do not produce numeric bars and can make the applied range harder to manage.
Consider totals separately: A grand total can become the maximum and make individual rows look tiny. Apply a separate rule or exclude the total if row-to-row comparison is the goal.
Use consistent boundaries across periods: If each month’s rule scales automatically to that month’s minimum and maximum, equal-looking bars may represent different actual amounts.

For a completion column containing percentages from 0% to 100%, a fixed zero-to-100% scale can communicate progress more consistently than a scale that expands to the smallest and largest current values. Otherwise, if the current entries are 70%, 80%, and 90%, automatic scaling can make 70% look like a very short bar even though it is already close to full completion. Check the rule’s boundary settings and select values that match the message you intend.

Common problems and how to fix them

If no bars appear, first confirm that the selected cells contain numbers rather than numbers stored as text. Blanks and text entries do not provide ordinary numeric bar lengths. Also check that the rule’s Applies to range includes the cells you expect. Open Home > Conditional Formatting > Manage Rules and inspect the range shown for the rule.

If the bars look almost identical, the values may be clustered, the range may include a much larger outlier, or the current scale may be making small differences hard to see. You can set appropriate numeric boundaries, but avoid choosing limits that exaggerate differences without a clear reason. A data bar is a relative visual encoding; it cannot make a narrow spread of values into a meaningful performance gap.

If bars change after new data is entered, that may be expected: automatic boundaries can adjust to the new minimum and maximum. For a stable benchmark, use fixed boundary values that fit the purpose of the report. If you copy cells and see unexpected formatting, remember that copying can bring conditional-formatting rules along with cell contents. Review Manage Rules for overlapping rules or duplicated ranges, and use the rule’s Applies to field to correct the scope.

Dates are stored as serial numbers in Excel, so a data bar applied to date cells can reflect their underlying numeric values. That may be useful for comparing dates in a suitable layout, but it can look confusing if the reader expects bars to represent elapsed time or a named category. For percentages, ensure the cells are genuine percentage values and consider setting an explicit scale. A displayed value of 50% is stored as 0.5, which matters when specifying numeric boundaries.

Data bars compared with other visual tools

Color scales use background colors to show relative values, while icon sets use symbols such as arrows or traffic-light indicators to group values into categories. Data bars are a good choice when the size of each value matters and the cell should remain part of a compact table. A color scale can be easier to scan across a broad grid, and an icon set can communicate status bands, but neither necessarily shows magnitude as directly as a bar beside the number.

A chart is often better when readers need a legend, labeled axes, a time trend, or a comparison across many categories. Data bars are convenient for in-cell context, not a substitute for every chart. You can use data bars alongside other formatting, but multiple visual signals can clutter a table. Keep the number of colors and rules limited and make sure the formatting has a clear purpose.

Frequently Asked Questions

Do data bars change the values in Excel?
No. They are conditional formatting, so they change how cells look, not the values stored in them. Formulas that refer to those cells continue to use the original numbers.

Can Excel show the number and the data bar together?
Yes. By default, the value remains visible over or beside the bar. In the rule settings, Show Bar Only hides the number; turn that option off to display both.

Why do equal values have different-looking bars?
Check whether the cells belong to different rules or ranges with different scales, or whether a rule has different minimum and maximum boundaries. A shared, consistent rule and common boundaries help make equivalent values look equivalent.

Can data bars display negative values?
Yes. Excel can place an axis at zero and draw negative and positive bars in opposite directions. Review the axis and direction settings so the result is clear for the range being displayed.

Can I use a data bar for a progress percentage?
Yes. Apply the rule to the percentage cells and consider setting the scale from 0% to 100%. That keeps a value such as 70% visually anchored to the full possible range rather than only to the other current entries.

How do I remove data bars without deleting the numbers?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes conditional formatting from the selection, not its values. Check the selection first so you do not clear other rules you want to keep.

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.