Add progress bar in your cells is very simple with the conditional formatting tool
- Select your Data
It can be one or more than one column
- Use the conditional formatting menu
Activate the menu to display the progress bar
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
- Select a range of cells
- 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
- Select the option 'This Worksheet' VERY IMPORTANT !!!
- Select your rule
- 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
- Select the range B2:G8
- 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).