How to Create a Custom Progress Bar in Google Sheets Using the REPT Function

When tracking project completion, sales targets, or personal goals in Google Sheets, staring at a column of raw percentages (e.g., 45%, 72%) is not very engaging. Adding visual indicators makes a dashboard instantly readable.

While Google Sheets has a built-in “Sparkline” function to create mini charts, it is somewhat rigid and uses standard colored bars. If you want a fun, highly customizable progress bar that uses specific symbols—like filling up a bar of stars (★★★★★), squares (■■□□□), or even emojis—you can build one from scratch using the incredibly versatile REPT function.

Understanding the REPT Function

The REPT function does one simple thing: it repeats a specific text character a specified number of times.

The syntax is: =REPT(text_to_repeat, number_of_times)

For example, =REPT("★", 3) will output ★★★.

Building a 10-Point Progress Bar

Let’s assume cell A2 contains a number between 1 and 10, representing a project’s completion out of 10 phases. We want to display a progress bar in cell B2 using block characters.

We will use two special characters: a solid block (█) for completed phases, and a light shaded block (░) for incomplete phases.

1. Click on cell B2.
2. Enter the following formula:

   =REPT("█", A2) & REPT("░", 10 - A2)

3. Press Enter.

How it works:

• If A2 contains the number 4.
• The first half of the formula REPT("█", 4) generates four solid blocks: ████
• The ampersand & glues the next part to it.
• The second half of the formula calculates 10 - 4 = 6, and then generates six shaded blocks: ░░░░░░
• The final output seamlessly merges them into a perfect 10-point progress bar: ████░░░░░░

Adapting for Percentages (0% to 100%)

If your data in cell A2 is formatted as a percentage (e.g., 65%), the number underlying that percentage is actually a decimal (0.65). If you feed 0.65 into a REPT function, it rounds it down to 0 and displays nothing.

To convert a percentage into a 10-block progress bar, you must multiply the cell value by 10 and wrap it in the INT() function to ensure it is a whole number.

=REPT("█", INT(A2*10)) & REPT("░", 10 - INT(A2*10))

If A2 is 65% (0.65), it multiplies by 10 to get 6.5, the INT function rounds it down to 6, and it generates a progress bar showing 6 out of 10 blocks filled. You can easily change the block characters to any text symbol or emoji to perfectly match the theme of your spreadsheet dashboard.