Have you ever wondered how to create eye-catching and informative percentage progress bars in Google Sheets? Look no further! In this article, we will guide you through the process of creating both vertical and horizontal percentage progress bars in Google Sheets. Whether you’re tracking project milestones or visualizing survey results, these progress bars are a fantastic way to communicate data in a visually appealing manner.
Creating a Horizontal Percentage Progress Bar in Google Sheets
Let’s start by creating a horizontal percentage progress bar using the SPARKLINE function in Google Sheets. We will use the percentages in the cell range A2:A5 as examples (25%, 100%, 50%, and 75%).
In cell C2, enter the following formula and drag it down to cell C5:
=SPARKLINE(A2, {"charttype", "bar"; "color1", "green"; "max", 100%})
This will generate a green chart that adjusts based on the column width and row height. If you prefer a different color, you can replace “green” with “red” in the formula. To create a bar with different colors below and above 50%, you can use the following formula:
=SPARKLINE(A2, {"charttype", "bar"; "color1", IF(A2<=50%, "red", "green"); "max", 100%})How to Add Text to My Horizontal Percentage Progress Bar in Google Sheets
If you want to display text alongside the percentage progress bar, you can use the REPT function. Follow these steps:
- Set the percentage progress value in cell A2 (initially set to 100% for ease of step-up).
- Enter the following formula in cell C2:
=REPT("█", A2*100/4)&TEXT(A2, " 0%") - Evaluate the length of the bar and adjust the formula if necessary.
- Match the column width to the size of the bar.
- Replace the percentage value in cell A2 with the desired value (e.g., 25%) and drag down the formula.
Please note that the REPT function has limitations regarding row height and column width flexibility. However, it is the only method available to include text with a percentage progress bar in Google Sheets.
How Do I Change the Color of the Bar?
To change the color of the bar, simply navigate to the cell containing the REPT formula, click on “Text color” within the Google Sheets toolbar, and apply the desired color.
Creating a Vertical Percentage Progress Bar in Google Sheets
To create a vertical percentage progress bar using the SPARKLINE function, follow these steps:
- In cell C5, enter the following formula to create a vertical bar:
=SPARKLINE(B5, {"charttype", "column"; "ymin", 0%; "ymax", 100%; "color", "green"}) - Adjust the row height and column width to see a genuine vertical percentage progress bar.
- If you want to conditionally adjust the color based on the 50% mark, use the following formula:
=SPARKLINE(B5, {"charttype", "column"; "ymin", 0%; "ymax", 100%; "color", IF(B5<=50%, "red", "green")})
And there you have it! With just a few simple formulas, you can create impressive percentage progress bars in Google Sheets. Feel free to experiment with different colors and customizations to match your preferences.
Resources
The SPARKLINE function is a powerful tool in Google Sheets, allowing you to create various miniature charts. If you’re interested in learning more about the SPARKLINE function and its possibilities, check out the following tutorials:
- Create a Gantt Chart Using Sparkline in Google Sheets
- Sparkline Bar Chart Formula Options in Google Sheets
- Sparkline Column Chart Options in Google Sheets
- Sparkline Line Chart Formula Options in Google Sheets
- SPARKLINE for Positive and Negative Bar Graph in Google Sheets (Array Formula)
Now you have all the tools you need to create stunning percentage progress bars in Google Sheets. Happy charting!
