Display bars is one of the most interesting options of conditional formatting. This way, you can visualise the distribution or progress of your data with this visual.
Visualization in a workbook
Let's take this example where you have the result of the cells for the first semester.
How to add bars in the cells?
The technique to add bars in the cells is very simple
- Select a range of cells
- Then open the menu Conditional Formatting > Data Bar
Let's analyse the size of the bars
Your cells instantly is filled with a bar proportional to the values in the range.
The highest value is 1000 and the bar fill the entire the cell.
Then, you can see that for the value 500, the bar fill 50% of the cell. This is because
- the minimum value in the range is 0
- the maximum value in the range is 1000
Change the default settings
Now let's change some value in the range in order to have the minimum = 200. And apply one more time the conditional formatting bars
What is weird here, is the fact the lowest value as a bar? So let's check the settings in the menu Home > Conditional formatting > Manage Rules
Then follow the next steps
- Select the option 'This Worksheet' VERY IMPORTANT
- Select your rule
- Click on the Edit button
And now, you see the settings 😀 As you can see, the Automatic Type is selected by default. So you let Excel manage the display with its internal rules. But it's better to put your own parameters 😉
To finish, change the setting to Lowest & Highest value
So now, the minimum value doesn't show any part of a bar
Same proportion for all the cells
Now, if you select the range B2:G8, the size of the bar will depend of the min and max of the selection.
But if you apply the conditional formatting for columns, this time the size of the bars is based on the values of each columns (and not the whole range)