This step-by-step tutorial explains how to create the following progress bars in Google Sheets: Show
 Step 1: Enter the DataFirst, let’s enter some data that shows the progress percentage for 10 different tasks: Step 2: Add the Progress BarsNext, type the following formula into cell C2 to create a progress bar for the first task: =SPARKLINE(B2,{"charttype","bar";"max",1;"min",0;"color1","green"}) Copy and paste this formula down to every remaining cell in column C: The length of each progress bar in column C reflects the percentage value in column B. Step 3: Format the Progress Bars (Optional)You can modify the progress bars to display specific colors based on the progress percentage. For example, you can use the following formula to display a green progress bar if the percentage is greater than 70, else a yellow progress bar if the percentage is greater than 50, else a red progress bar: =SPARKLINE(B2,{"charttype","bar";"max",1;"min",0;"color1",IF(B2>0.7,"green",IF(B2>0.5,"yellow","red"))}) The following screenshot shows how to use this formula in practice: The color of the progress bar is now dependent on the value in column B. Feel free to add a border around the cells and increase the length and width of the cells to make the progress bars larger and easier to read: Additional ResourcesThe following tutorials explain how to create other common visualizations in Google Sheets: How to Plot Multiple Lines in Google Sheets A percentage progress bar is a bar in a single cell that changes according to the percentage of values in other cells. Here in this tutorial, we can learn how to create a percentage progress bar in Google Sheets. For this purpose, we can use a few text formulas. I mean a combo 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 way to create a % progress bar. That’s by using the SPARKLINE function. I’ve included both the methods in this guide. You can create a percentage progress bar for many purpose.
Example to Percentage Progress Bar (Text Function Based): From the above, you can easily understand the percentage of marks scored by each student in an Exam. The above is an example of the percentage progress bar in Google Sheets. Now I will explain to you the formula used for the above bar in a single cell. Two Methods to Create a Percentage Progress Bar in Google SheetsUsing the Text Functions REPT and CHARThe REPT function is the core of the formula. We can use this function to repeat any characters the given number of times. Here we repeat Percentage Progress Bar Formula in Cell C3: Explanation: For our explanation purpose, the above formula can be split into two parts as below. Formula 1: Formula 2: See the screenshot above. The value in cell B3 is 540. That is the mark scored by “Marlene Huff” in an exam out of 600 marks. So the percentage of marks scored is So obviously to shrink the percentage progress bar, you can modify this as Similar: Repeat Multiple Columns ‘N’ Times. The In ‘Sheet3’ I have removed this CHAR new line formula and instead added a blank space. I have used the Round function to round the percentage to two decimal places in the second formula. You can check the screenshot to clearly understand that. Then the formula copied to the cells down. Percentage Progress Bar Using the SPARKLINE Function in Google SheetsInstead of the text functions based formula in cell C3, we can use the SPARKLINE (chart) function to draw a percentage progress bar in Google Sheets. Copy this formula to the array C4:C9. To learn the above 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 end of this post to get the example sheet. Conditional Formatting to Change the Color of the Progress BarHere is another example. This is useful for EPC construction contracts to monitor the percentage of work progress. Even if you are not in that field, don’t worry. You can easily follow the bar chart. Sample Data: Cell B12 contains the total percentage to achieve and C12 contains the total achieved percentage. Since both the values are the same, 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 same earlier formula. The only difference here is, here I’m not using any percentage calculation in the formula. As an addition, I have applied some conditional formatting this time. Let’s see that. The formula for the above: Here again, the formula has two parts. But this time I have used an additional IF logical formula. It just verifies that the achieved percentage is not more than 100%. If more than 100% the formula would return a question mark instead of any bar. When you check the above image, you can see a percentage scale below the bar. Actually, I’ve not used any formula for this. I’ve just manually keyed it in the cell below the bar. Finally the conditional formatting aspect. You can apply certain conditional formatting rules as below in cell E2 to change the color of the bar when the percentage reaches certain values like 10 to 20%, 20 to 30% etc. To apply the above highlighting, go to the menu Format > Conditional formatting and in the custom formula field apply the below formula. Change the values as you wish. See such few rules below. ConclusionHere in the last example, if you wish to use a SPARKELINE bar, then use this formula. Do not use conditional formatting. See the ‘Sheet1’ in my below example sheet for the above two formulas. That’s all about the percentage progress bar in Google Sheets. Any doubt please feel free to use the comment box below. Example Sheet Resources:
Can you change individual colors on a bar graph Google Sheets?Click the Apply to arrow. Select a data series. Click Color . Pick an option to set the color of the bars.
Can you color code data validation in Google Sheets?To use color coding, right-click the selection and hit “Conditional Formatting” or choose Format > Conditional Formatting in the toolbar. Once there, set criteria and associated colors. Protip: To use Data Validation and Color Coding in conjunction, set data validation first, then set color coding.
Can I use an IF formula in conditional formatting Google Sheets?The process to highlight cells that contain an IF Statement in Google Sheets is similar to the process in Excel. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.
How to use conditional formatting with checkboxes Google Sheets?Use Conditional Formatting With a Checkbox in Google Sheets
1. Select the data range and in the Menu, go to Format > Conditional formatting. For the Formatting style, (3) select Fill color, (4) choose the background color (i.e., light blue), and (5) click Done.
|
