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

1. Select a range of cells
2. 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

1. Select the option 'This Worksheet' VERY IMPORTANT
2. Select your rule
3. 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)