A percentage progress bar is a bar in a single cell that changes according to the percentage of values in another cells. Here in this Google Sheets tutorial we can learn how to create a percentage progress bar in Google Sheets. For this purpose we can use few text formulas. If you wish you can further enhance the percentage progress bar in Google Sheets with conditional formatting. So here we go.
Purpose of Percentage Progress Bar in Google Sheets
You can create a percentage progress bar to simply show the percentage of marks scored by students in an exam, completion percentage of an allocated or awarded job, percentage progress of overs bowled in a cricket match like so many situations.
Example to Percentage Progress Bar:
From the above example, you can easily understand the percentage of marks scored by each students in an Exam. This is an example to the percentage progress bar in Google Sheets. Now I will explain you the formula used for the above bar in single cell.
How to Create Percentage Progress Bar in Google Sheets
The REPT function is the core of this formula. It can repeat any characters given number of times. Here we repeat char(406), which is equal to “Ɩ” certain number of times. For your information, CHAR is a function in Google Sheets.
For our explanation purpose, the above formula can be splitted into two parts as below.
See the screenshot above. Here the value in B3 is 540. This is the marks scored by “Marlene Huff” in an exam out of 600 marks. So the percentage of marks scored by him is 540/600×100 = 90. So in the formula we repeat Char(406), i.e. “Ɩ” 90 times.
Similar: Repeat Multiple Columns ‘N’ Times
The char(10) in the second part of the formula imposes a new line in the same cell and again finds the percentage and put a % sign to it. We used the Round function to round the percentage to two decimal places. Check the screenshot to clearly understand it. Then the formula copied to cells down.
Percentage Progress Bar in Google Sheets Using Formula + Conditional Formatting.
Here is another example. This is useful for construction works to monitor the percentage of work progress.
This is a completed job. I’ve the percentage of job completion in Cell C12. So no need to calculate it again. This is because, here for each job, depending the manpower and time required, we have been given a percentage weight. Total weight of the job is 100% and distributed the same to different jobs.
Our finished percentage progress bar in Google Sheets would look like as below. Here I am using the same above formula. The only difference is, here I’m not going to use any percentage calculation in the formula. As an addition, I have applied some conditional formatting this time. Let’s see that.
Formula for the above:
Here again the formula has two parts. But I have used an additional IF logical this time. See our sample data. We have already the calculated percentage in cell C12. By any reason, the percentage crosses 100%, the formula would returns a “?” mark. The IF logical part does this wonderfully.
When you check the above image, you can see the percentage scale below the bar. Actually I’ve not used any formula for this. I’ve just keyed it in the cell below the bar.
Finally the conditional formatting aspect. You can apply certain conditional formatting rules as below to change the colour of the bar when the percentage reaches certain values like 10 to 20%, 20 to 30% etc. See such few rules below.
That’s all. Any doubt please feel free to use the comment box below.