How to Use Excel Conditional Formatting Data Bars

When you look at a spreadsheet column containing hundreds of different numbers—such as monthly sales figures or student test scores—it is difficult to quickly identify the highest and lowest values just by staring at the raw digits. You could create a separate bar chart, but that takes up valuable screen space.

Instead, you can use Data Bars. This is a Conditional Formatting feature in Microsoft Excel that inserts tiny, horizontal progress bars directly inside the cells, directly behind the numbers. It turns a boring column of data into an instant, scannable visual dashboard without requiring a separate chart.

How to Apply Data Bars

Adding Data Bars takes only a few seconds and requires no formulas.

1. Open your spreadsheet in Microsoft Excel.
2. Select the cells containing the numbers you want to visualize. (You can click and drag to select a range, or click the column letter at the top to select the entire column).
3. Make sure you are on the Home tab in the top ribbon menu.
4. In the “Styles” group, click the Conditional Formatting button.
5. Hover your mouse over Data Bars in the dropdown menu.
6. A sub-menu will appear showing different colour options, divided into “Gradient Fill” and “Solid Fill”. Click on a colour to apply it.

Instantly, the cells will fill with horizontal bars. Excel automatically calculates the scaling: the cell with the highest number in your selection will have a bar that fills the entire width of the cell, and all other bars will be scaled proportionally relative to that maximum value.

How to Customise the Appearance

The default settings are usually sufficient, but you can customise exactly how the bars look and behave if you need to create a formal report.

1. Select the cells that contain your Data Bars.
2. Click Conditional Formatting, hover over Data Bars, and click More Rules… at the very bottom of the menu.
3. A formatting dialogue box will appear, offering several advanced options:

• Show Bar Only: If you check this box, Excel will hide the actual numbers in the cell and display only the coloured progress bars. This is perfect for creating ultra-clean visual dashboards where the exact digits do not matter.
• Minimum and Maximum Values: By default, Excel scales the bars based on the highest and lowest numbers currently in your selection. If you change the “Minimum” and “Maximum” dropdowns from Automatic to Number, you can set absolute limits (e.g., setting the maximum to 100 for percentage scores, ensuring the bar only hits 100% when a student scores a perfect 100).
• Negative Value Settings: Click the “Negative Value and Axis” button to control how negative numbers are displayed. By default, Excel will draw an axis line down the middle of the cell and draw red bars extending to the left for negative numbers, while positive numbers extend to the right in blue.

Click OK when you have finished customising your rules to apply the changes to your spreadsheet.

How to Remove Data Bars

If you no longer need the visualisations and want to return to plain text:

1. Select the cells containing the Data Bars.
2. Click Conditional Formatting.
3. Hover over Clear Rules.
4. Click Clear Rules from Selected Cells.