How to set up conditional formatting data bars in Excel: Use bars to display values

Key points of this article

The data bars are drawn inside the cells, and the bar lengths are proportional to the values. You can quickly compare the sizes without inserting a table.
Numbers must be in true numeric format, numbers in text format will not draw bars.
When there are multiple tables or multiple refreshes, the minimum and maximum values need to be set to fixed numbers or formulas, otherwise the bar lengths will not be comparable.
When the values are negative, check the direction of the bars individually and adjust the axis settings if necessary.
Use color scales when you need to judge classification or height, and use data bars when you need to accurately compare lengths.

When making reports, a column of sales, completion rate or inventory figures is arranged there, and it is very troublesome to compare the sizes row by row. There is a "data bar" in Excel's conditional formatting, which can draw a horizontal bar with a length proportional to the value inside the cell. The larger the value, the longer the bar. There is no need to insert an additional chart, and the table itself can show the level.

This article is written for people who often make statistical tables, monthly reports or comparison tables. After reading, you will be able to judge when data bars are suitable, know how to set them, and how to adjust the display of small values, large values, and negative values so that the bars will not mislead readers. The article also compares color filling and text strips generated with formulas to facilitate you to choose according to your needs.

How to set the data strip? One sentence conclusion

One sentence conclusion: when the same column is all positive numbers, you can directly apply the data strip; when the values are negative, or when multiple tables need to unify the scale, you need to manually set the minimum and maximum values.

your situation	Recommended practices	reason
The values in the same column are all positive numbers, and I just want to compare the sizes.	Select the column value and directly use the preset data bar style	By default, Excel converts the bar length from the smallest value to the largest value in this area. Set the quick
Numerical values may be positive or negative, such as profit and loss, increase or decrease	Use data bars and check the direction of the negative bars and adjust the axes if necessary	The starting point and direction of the negative value bar may not necessarily conform to reading habits by default.
Multiple months or multiple tables need to be compared	Set small and large values to fixed numbers or formulas	The automatic range of each table is different, and the bar lengths cannot be directly compared.
Just differentiate between grades or categories	Use color levels instead	Color depth is more suitable for expressing classification than bar length, and readers do not have to make precise comparisons.
Readers need to see accurate numbers simultaneously	Keep numerical display, uncheck only display bars	After hiding the value, readers can only estimate the size and cannot check the specific gap.

Before you start: Confirm these points first

Numbers are not real numerical values. Data strips only take effect on numerical values. Numbers copied from web pages, PDFs or other systems often exist in the form of text. There may be a green triangle in the upper left corner, which looks like numbers but does not draw bars. Before you start, test it with methods such as summation. Only if you can calculate the total does it mean that the number is a calculable value.

Whether the selection only contains numerical values. Do not select the table header, total row and remark row, otherwise the title text will interfere with the range, and the large number in the total row will shorten the bars of other rows. It is recommended to select only the data rows that need to be compared.

Whether a unified scale is needed. Excel's automatic range is only calculated within the currently selected range. If you were to look at the two tables for January and February next to each other, the maximum values would be different, but the bars of the same length would represent different values. This situation requires planning ahead with fixed minimum and maximum values.

If the reader requires precise figures. Data bars are suitable for quick comparisons and are not suitable as a substitute for precise data. If the report is to be checked by leaders or customers, it is recommended to retain the numerical display.

Whether the data source is PDF. If the numbers come from a PDF table, you can export to Excel first and then set conditional formatting. If it is a scan, the numbers recognized by OCR must be checked item by item with the original text. Recognition errors will directly distort the bars.

Steps to set up data bars in Excel

Select the numerical range where you want to display bars, excluding headers and total rows.
Open the conditional formatting menu and find the data bar category.
Choose one of the preset styles. Common ones include gradient fill and solid fill. First check whether the effect is clear.
If you need to customize it, open the rule management of conditional formatting, find the data rule you just added and enter the editing interface.
Set the type of small value and large value respectively, optional numbers, percentages, formulas or automatic (low value, high value). When you need to unify the scale, choose a number or formula, for example, enter =MAX($B$2:$B$13) so that the large value will always be the largest number in the area.
Set the bar direction, border and filling method as needed, and decide whether to check "Show bars only".
After confirmation, check whether the bar length is consistent with the numerical value, and then copy the format to other tables.

Check each item after completion

Check items	How to see	What to do if there is a problem
Is the bar length sorting consistent with the numerical value?	Spot-check two or three rows, big and small, and compare the bar lengths and numbers.	Most of them are text numbers. Convert them to numerical format first and then check.
The direction of the negative bar	See if the bars for negative cells extend in the expected direction	Adjust the coordinate axis position, or use fixed minimum and maximum values instead
Are the scales of multiple tables consistent?	Compare the bar lengths of the same value in two tables	Set the same fixed minimum and maximum values for both tables
Is the value still readable?	See if numbers can still be read in the cells	Uncheck "Show bars only" or create another column for values
Printing effect	View using print preview	When the gradient bar is lighter in color, use solid filling to avoid unclear visibility after printing.

📊 Core comparison

There are three common ways to express values using bars or colors in Excel, each with its own applicable scenarios.

Way	Suitable for the scene	Things to note
data strip	Comparing the length of values in the same column, you need to quickly see the difference	The automatic range is only valid within the selection, and the ruler must be fixed for cross-table comparisons.
Levels or highlighting rules	Distinguish between high and low grades, or mark data exceeding a certain threshold	Colors do not reflect specific differences and may be difficult for print or color-impaired readers to distinguish
Text bar generated by formula	I want to draw a bar directly using characters in the cell, and it needs to change with the formula.	A formula basis is required, for example =REPT("█",ROUND(B2/10,0)) will generate the corresponding number of characters based on numerical values.

❓ Frequently Asked Questions (FAQ)

1. What is the difference between data bars and bar charts?

Data bars are drawn inside cells and do not occupy additional space, making them suitable for quick comparison in tables. A bar chart is an independent chart object that can add axes, titles and legends, making it more suitable for formal display.

2. Why are the data bars set and all the bars are the same length or very short?

There are two common reasons: the number is in text format, or the large value is pulled up by some unusually large number. Check the number format first, and then change the large value to a reasonable fixed number or formula.

3. Can data bars be used for only some rows?

Can. Whichever area is selected, the rule will only apply to that area. When group comparison is required, you can set separate rules for each group and use the same fixed ruler.

4. Can data strips and color levels be used at the same time?

Technically, they can be superimposed, but the simultaneous presence of colors and bars in the same area will interfere with reading. It is recommended to choose one of them based on the focus of expression.

📝 Summary

Data bars can turn values into bars in cells and are a quick way to display values in bars in Excel. By selecting the right value range, confirming the number format, and then setting fixed minimum and maximum values based on whether cross-table comparisons are needed, you can make the bar both intuitive and reliable.

Open OmniDoc and work with your spreadsheet file