Visualization in a workbook

Let's take this example where you have the results of the cells for the first semester.

How to add bar 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 analyze the size of the bars

Your cells instantly are filled with a progress bar proportional to the values in the range.

The highest value is 1000 and the bar fills the entire cell.

Then, you can see that for the value 500, the bar fills 50% of the cell. This is because

• the minimum value in the range is 0
• the maximum value in the range is 1000

Manage the settings of the progress bars in your cells.

In this example, the minimum is 200. But you can see that the cell is not empty. Why? 🤨

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 of your rule😀

• As you can see, the Automatic Type is selected by default.
• Automatic means: you let Excel manage the min and max values (not good).
• But now, you can change these values and put your custom limits 😉

To finish, change the setting to the Lowest & Highest value

And now, there is no progress bar for the minimum value 😀

The same proportion between one or many columns

This last remark is very important

1. Select the range B2:G8
2. Apply the progress bar option of the conditional formatting tool

The size of each progress bar is linked to the value of all the cells in all the columns.

But if you apply the conditional formatting column by column, this time the size of the bars is based on the values of each column (and not the whole range).

Related articles

• Highlight the min and the max with a different color
• Create a progress bar with icons