Home > Format > ConditionalFormatting > Data Bars
Excel Conditional Formatting Data Bars
Use conditional formatting to add data bars to cells with numbers. This makes it easy to visually compare numbers, as you would in a bar chart.
If cells contain numbers, you can add conditional formatting data bars, to show the differences among the amounts. Watch this short video to see how to set up data bars in a cell, and the written instructions are below.
In the screen shot below, conditional formatting data bars have been added to a sales report. You can quickly see that June had the smallest sales, and January and March have the largest.
To add conditional formatting with data bars, follow these steps.
The selected cells now show Data Bars, along with the original numbers. In this example, the Green Gradient data bar option was selected.
There are two sets of Data Bar options -- Gradient Fill and Solid Fill.
NOTE: Change the numbers to Bold font, so they are easier to see.
If possible, make the column wider than usual, if you are adding data bars, especially if the numbers will also be visible.
After you add Data Bars, you can change the conditional formatting settings, so only the data bars are shown in the cell, without the numbers.
Follow these steps to hide the numbers:
When you add Data Bars, their minimum and maximum values are automatically set, usually with a minimum of zero, and a maximum that matches your highest value.
To "zoom in" on the differences between the amounts, follow these steps to change the Minimum setting:
The formula finds the lowest value in the group of cells, and sets the minimum 10% below that. (If you just choose Lowest Value, there is no bar visible for that value, which can be confusing).
NOTE: In the sample file, a formula is also used to set the Maximum: =MAX($G$4:$G$9)*1.1. That formula finds the highest value, and sets the maximum 10% above that. This creates a small space at the end of the bar, instead of going to the cell border. Use a similar formula if you want extra room to show numbers, without the bars overlapping them.
To follow along with this tutorial, get the Conditional Formatting Data Bars sample file. The file is in xlsx format, and does not contain macros.
Last updated: October 29, 2022 3:37 PM