Add progress bar in your cells

Add progress bar in your cells
Last Updated on 14/09/2023
Reading time: 3 minutes

Add progress bar in your cells is very simple with the conditional formatting tool

  1. Select your Data

    It can be one or more than one column

  2. 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.

Report of the sales

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 
Add bars in the cells

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.

The highest value fill 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
The size depends of the highest and lowest value

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? 🤨

The min value has a bar in the cell

So let's check the settings in the menu Home > Conditional formatting > Manage Rules

Menu 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
Edit the rules of the bars

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 😉
Settings of the display of the bars

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

Selection of the setting lowest and highest values

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

New display of the bars

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.

The proportion of the bars is based on the values of all the cells

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).

Size of the bars in function of the values in the columns

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Add progress bar in your cells

Reading time: 3 minutes
Last Updated on 14/09/2023

Add progress bar in your cells is very simple with the conditional formatting tool

  1. Select your Data

    It can be one or more than one column

  2. 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.

Report of the sales

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 
Add bars in the cells

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.

The highest value fill 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
The size depends of the highest and lowest value

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? 🤨

The min value has a bar in the cell

So let's check the settings in the menu Home > Conditional formatting > Manage Rules

Menu 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
Edit the rules of the bars

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 😉
Settings of the display of the bars

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

Selection of the setting lowest and highest values

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

New display of the bars

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.

The proportion of the bars is based on the values of all the cells

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).

Size of the bars in function of the values in the columns

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *