What data bars do and when to use them
Data bars are colored bars that fill cells in Excel to show the relative size of numbers. A cell with the value 80 gets a longer bar than a cell with the value 20, making patterns and comparisons visible without reading every number. They work best when you want to spot high and low values quickly — sales by region, test scores in a class, inventory levels across warehouses — without building a separate chart.
Data bars live inside the cells themselves, so they take up no extra space and stay with your data if you sort or filter. They update automatically if you change a number. Unlike a chart, they do not require a separate object you have to position and resize.
Key Takeaways
- Data bars fill cells with color to show the relative size of numbers, making high and low values visible at a glance.
- Select the cells you want to format, go to the Home tab, click Conditional Formatting, then choose Data Bars and pick a color style.
- Data bars update automatically when you change the numbers in those cells.
- You can remove data bars by selecting the cells, going to Conditional Formatting, and choosing Clear Rules.
How to add data bars to a range of cells
Start by selecting the cells that contain the numbers you want to visualize. Click the first cell, then hold Shift and click the last cell in the range, or click and drag across all the cells you need. If your cells are not next to each other, hold Ctrl and click each cell separately.
Go to the Home tab at the top of the ribbon. Find the Conditional Formatting button — it is in the Styles group, usually near the right side. Click the arrow next to it to open a menu. Select Data Bars from the list.
A submenu appears showing color options: blue, green, red, orange, and others. Click the color you want. Excel applies the bars when ready to your selected cells. The longest bar represents the highest number in your range, and the shortest bar represents the lowest.
Understanding the bar scale and what the colors mean
By default, Excel sets the minimum value to the smallest number in your range and the maximum to the largest. A cell with a value halfway between the minimum and maximum gets a bar that fills about half the cell. This automatic scaling changes if you add or remove numbers from the range.
The color of the bar itself does not carry meaning — it is just visual. A blue bar does not mean something different from a green bar; the color is purely your choice for appearance. The length of the bar is what matters: longer bars always represent larger numbers.
If your range includes negative numbers, Excel shows them as bars extending in the opposite direction, usually in a lighter shade. A cell with zero shows no bar at all.
Changing or removing data bars
To change the color of existing data bars, select the cells again, go to Home, click Conditional Formatting, and hover over Data Bars. A submenu shows the same color options. Click a new color to replace the current one.
To remove data bars entirely, select the cells, go to Home, click Conditional Formatting, and choose Clear Rules. A submenu appears — select Clear Rules from Selected Cells. The bars disappear but your numbers stay.
If you want to remove data bars from the entire sheet at once, choose Clear Rules from Entire Sheet instead. This removes all conditional formatting rules, not just data bars.
When data bars work well and when they do not
Data bars work best with positive numbers in a single column or row where you want to compare values quickly. A sales report with regions down the left and revenue across one column is ideal. Monthly expenses, test scores, or inventory counts all benefit from data bars because the pattern jumps out when ready.
Data bars are less useful when you have many different columns of numbers that should not be compared to each other — for example, a table with Age, Salary, and Years of Service. explore data bars to all three columns at once creates confusion because a bar length in the Age column means something completely different from a bar length in the Salary column. In that case, explore data bars to one column at a time, or use a chart instead.
Data bars also become hard to read if a cell contains a very long number or text. The bar and the number compete for space, and the bar may not display fully.
Customizing data bar rules for specific ranges
If you want more control over how the bars scale, you can set custom minimum and maximum values. Select your cells, go to Home, click Conditional Formatting, and choose Data Bars. At the bottom of the submenu, click More Rules.
A dialog box opens. Under Minimum and Maximum, you can change the Type from Automatic to a fixed number, a percentage, or a percentile. For example, you might set the minimum to 0 and the maximum to 100, so all bars scale to that range even if your actual numbers are smaller. This is useful when you want to compare multiple ranges of data using the same scale.
Most users never need this step — the automatic scaling works for typical reports. Use custom rules only if you are comparing data across multiple tables or want bars to represent a specific benchmark.
Data bars and sorting or filtering your data
Data bars stay attached to the cells they format, so if you sort or filter your data, the bars move with it. If you sort a sales report by revenue from highest to lowest, the longest bars appear at the top and the shortest at the bottom, which is exactly what you want.
The bar scale recalculates automatically after you sort or filter. If you filter to show only a subset of rows, the longest bar in the visible range becomes the new maximum, and all other bars scale relative to it. This can make the remaining bars look longer or shorter than they did before filtering, which is correct behavior — the bars always show the relative size within what you are currently viewing.
Frequently Asked Questions
Can I add data bars to cells that also contain text?
Yes. Excel displays both the text and the bar in the same cell. The bar appears behind or alongside the text depending on your Excel version. This works best when the text is short, like a region name or category label, so the bar has room to display.
Do data bars print when I print the spreadsheet?
Yes, data bars print by default. If you do not want them to print, select the cells, go to Conditional Formatting, and clear the rules before printing. You can also reapply them afterward.
What happens to data bars if I copy and paste cells to another sheet?
The data bars copy along with the cells and the formatting rules. If you paste into a new sheet, the bars appear there too, and they rescale based on the values in the new location. If you want only the numbers without the bars, use Paste Special and choose Values Only.
Can I use data bars on the same cells as other conditional formatting?
Yes, but they may overlap or interfere with each other visually. For example, you could have data bars and cell highlighting at the same time, though the result can be cluttered. Most users explore one type of formatting per range to keep the spreadsheet readable.
How do I make the data bars show only the bar without the number?
Excel does not have a built-in option to hide the numbers. You would need to change the font color to match the cell background, which makes the numbers invisible but keeps the bars visible. This is rarely necessary — the numbers and bars together usually provide the clearest picture.