How to Get the Bar to Show Up Again in a Google Doc

A percentage progress baris a bar in a single jail cell that changes according to the pct of values in other cells. Here in this tutorial, we can acquirehow to create a percentage progress bar in Google Sheets.

For this purpose, we can apply a few text formulas. I mean a philharmonic formula using multiple text functions.

If you wish you can further enhance this formula based % progress bar in Google Sheets with conditional formatting.

Other than the above, there is one more style to create a % progress bar. That's past using the SPARKLINE part. I've included both the methods in this guide.

Purpose of Percentage Progress Bar in Google Sheets

You lot can create a pct progress bar for many purpose.

• To simply show the % of marks scored by students in an test.
• Completion % of an allocated/awarded job.
• Pct progress of overs bowled in a cricket match.
• In uncomplicated data entry tasks similar out of 1000 rows, how much percentages edited, etc.

Case to Percentage Progress Bar (Text Office Based):

From the above, you can easily sympathise the percentage of marks scored by each student in an Examination.

The to a higher place is an instance of the percent progress bar in Google Sheets. Now I will explain to you the formula used for the in a higher place bar in a unmarried cell.

Two Methods to Create a Percentage Progress Bar in Google Sheets

Using the Text Functions REPT and CHAR

The REPT office is the core of the formula. We can use this function to repeat whatever characters the 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.

Percentage Progress Bar Formula in Jail cell C3:

          =REPT(char(406),B3/600*100)&char(10)&round((B3/600*100),2)&"%"        

Caption:

For our explanation purpose, the higher up formula tin can be split into two parts every bit below.

Formula 1:

          REPT(char(406),B3/600*100)        

Formula two:

          &char(ten)&round((B3/600*100),2)&"%"        

See the screenshot higher up. The value in prison cell B3 is 540. That is the marking scored by "Marlene Huff" in an examination out of 600 marks.

So the percentage of marks scored is 540/600x100 = 90. So, in formula i, we repeat Char(406), i.due east. "Ɩ" ninety times (please refer to 'Sheet2' in my case sheet [link at the terminate of this tutorial]).

So obviously to shrink the percentage progress bar, you can change this equally (540/600x100)/northward. Here 'n' tin can be 2, 3, or 4. In my instance sheet, refer 'Sheet3' to see this shrinking of the bar. There I've used 'n' as 4.

Like: Repeat Multiple Columns 'Due north' Times.

The char(10) in the 2d part of the formula (formula 2) imposes a new line in the aforementioned jail cell and again finds the percentage and join a % sign to it.

In 'Sheet3' I have removed this CHAR new line formula and instead added a bare space.

I have used the Circular function to round the percentage to two decimal places in the second formula. You lot tin can check the screenshot to clearly sympathise that. Then the formula copied to the cells downwards.

Percentage Progress Bar Using the SPARKLINE Function in Google Sheets

Instead of the text functions based formula in prison cell C3, we tin use the SPARKLINE (chart) part to depict a percentage progress bar in Google Sheets.

          =sparkline({B3,600},{"charttype","bar";"color1","light-green";"color2","white";"max",600})        

Copy this formula to the array C4:C9. To learn the in a higher place chart, please read my guide Sparkline Bar Chart Formula Options in Google Sheets.

In my example sheet, 'Sheet2' column range C3:C9 contains the Text function based bar and E3:E9 contains the SPARKLINE based bar. Please jump to the terminate of this post to become the instance sheet.

Conditional Formatting to Change the Colour of the Progress Bar

Hither is another example. This is useful for EPC structure contracts to monitor the percentage of work progress. Even if y'all are non in that field, don't worry. You lot can hands follow the bar chart.

Sample Data:

Cell B12 contains the full percentage to achieve and C12 contains the total achieved percentage. Since both the values are the aforementioned, we can understand that it is a completed job.

Our finished percentage progress bar in Google Sheets would look like as below. Here I am using the aforementioned earlier formula.

The but divergence here is, hither I'thou not using any pct calculation in the formula. As an addition, I have applied some provisional formatting this time. Let'south see that.

The formula for the to a higher place:

          =if(C12<101,REPT(char(406),C12)&char(10)&C12&"%","?")        

Here again, the formula has two parts. Merely this time I have used an additional IF logical formula. Information technology simply verifies that the achieved percent is not more than than 100%. If more than than 100% the formula would return a question mark instead of whatever bar.

When you lot cheque the above image, you lot can run across a percentage scale beneath the bar. Actually, I've not used any formula for this. I've merely manually keyed it in the jail cell below the bar.

Finally the conditional formatting aspect. You can apply sure conditional formatting rules as beneath in prison cell E2 to modify the color of the bar when the percentage reaches certain values like 10 to 20%, twenty to 30% etc.

To apply the in a higher place highlighting, become to the menu Format > Conditional formatting and in the custom formula field utilise the beneath formula.

          =and(C12>0,C12<20)        

Change the values as y'all wish. See such few rules below.

Conclusion

Here in the final example, if y'all wish to use a SPARKELINE bar, then use this formula. Do not use conditional formatting.

          =sparkline({C12,B12},{"charttype","bar";"color1","blue";"color2","white";"max",B12})        

Meet the 'Sheet1' in my below example sheet for the above 2 formulas. That's all about the percentage progress bar in Google Sheets. Whatever incertitude please feel free to use the comment box below.

Instance Canvas

Resources:

• Create a Gantt Nautical chart Using Sparkline in Google Sheets.
• Create a GANTT Chart in Google Sheets Using Stacked Bar Chart.
• How to Utilize Per centum Value in Logical IF in Google Sheets.
• How to Randomly Extract a Certain Percentage of the Rows in Google Sheets.
• Query to Filter a Column Contains Percent Values in Google Sheets.
• Computing the Percentage of Total in Google Sheets [How To].
• Percentage Modify Assortment Formula in Google Sheets.

cordovafoophy.blogspot.com

Source: https://infoinspired.com/google-docs/spreadsheet/percentage-progress-bar-in-google-sheets/

Share this post