Create a Progress Bar in Google Sheets

Visualising your progress can be a real motivator to get stuff done. Thankfully, Google Sheets can provide you with a few options to view your progress using a few of the lesser-known functions that we’ll explore here.

In this tutorial, we'll explore the REPT function that also utilises the CHAR function to get the job done. You should also check out my preferred method, which utilises the SPARKLINE function in Google Sheets in this post.

If you're looking to add a progress bar chart to your To Do list, make sure you check out this Task List Management post which includes a progress bar for each status in a To-Do list.

I also have two Google Sheet templates available with the links below to manage your tasks, and both include progress bar charts, so make sure you check them out. The Basic Task Management template is FREE!

Basic Task Management Template

Buy Now

Pro Task Management Template

Buy Now

The Pro Task Management Template is also available via Etsy.com

Okay, moving on, let's dive in and see how the REPT and CHAR functions work.

REPT and CHAR Function

First up is the REPT function, which allows you to repeat some specified text by a defined number of repetitions.

You’re probably wondering, what is the use of repeating text for a progress chart?

The great thing about this is that we can use the CHAR function in conjunction with REPT to generate an emoji image.

We’ll break it down step by step now.

CHAR Function

If you type =CHAR(129001) into a cell, for example, you’ll see the emoji image of this character shown.

You can find symbols, shapes, and all sorts of wonderful things by searching in Unicode tables like this.

To use these in Google Sheets, they must be entered as a HTML code. The image below from the Unicode website mentioned above shows you the Unicode number U+1F7E9 and the HTML code 🟩 It is the HTML code we will use. However, we only require the number, not the preceding ampersand and hash sign or trailing semi-colon.

We simply enter the number 129001 into the CHAR function to return this emoji image 🟩

=CHAR(129001)

Once you’ve found the emoji images and the respective HTML codes, you can create things like this example below, which shows increases and decreases along with percentage changes to visualise the data better.

In this example above, the up triangle is CHAR(9650), and the down triangle is CHAR(9660) I’m using custom conditional formatting in this example to turn them green or red depending on if the percentage change is an increase or decrease vs the previous week. It’s a nifty formula that we’ll explore separately.

REPT Function

Okay, back to the progress bar. To get this to work with the REPT function, we need some data to pass into the formula we have thus far.

=CHAR(129001)

Suppose you have ten steps to complete, and you’re currently on step 2, so for the sake of simplicity, we’ll just type out a 2 in the cell next to our original formula to represent two completed tasks.

Now, we can adjust this formula to include the REPT function.

If you recall, the REPT function had two arguments; the first being the text_to_repeat and the second being the number_of_repetitions.

REPT(text_to_repeat, number_of_repetitions)

Our first argument of CHAR(129001)is the text_to_repeat, which is the emoji image 🟩, so now we just need to specify the number_of_repetitions for this text or, in our case, the emoji image.

Now we can specify the number_of_repetitions as the cell next to our formula with the number 2 in it.

Hit enter, and voila, now we have two little emoji images.

Just bear in mind that the cell with the number 2 in it has just been typed out manually, but you’ll probably use a COUNT or COUNTA function to count the number of instances of something from a list of tasks to make this more dynamic, for example.

Joining Formulas

You have a few options next, depending on how you want your progress bar to look, but I’ll give you a few examples to get an idea of what’s possible.

We essentially need to repeat what we just did with a slight modification to finish this off and to show outstanding progress.

You can see below that I’ve added an ‘&’ symbol, which will join or concatenate the first formula to the second one.

Then I just repeated the formula, but now we’re using a different Unicode character in the text_to_repeat part of the formula REPT(CHAR(11036), which uses the HTML code 11036 to give us a grey emoji image ⬜

Lastly, we’ll specify the number_of_repetitions simply as the number 10 minus the cell with the number of completed tasks (2 in our case F3). This will give us eight grey emoji images ⬜ sat next to the two green emoji images 🟩.

There you have it, a progress bar showing two completed tasks and eight remaining tasks.

Changing the Appearance

You could change the second formula to CHAR(32), which shows a NULL character, if you wish; the highlighted cell below only shows completed tasks now without the outstanding ones.

Perhaps you might want to mix and match colours, such as 🟩 CHAR(129001) with 🟨 CHAR(129000), to make things stand out.

Alternatively, you might like to get creative with stars or show your support for Ukraine with hearts to match the colours of their flag.

In case you want to use these yourself, the stars are CHAR(11088)⭐ and the love hearts are CHAR(128153)💙 and CHAR(128155)💛

You can find all sorts of Unicode sets here.

That wraps up the first method for creating a progress bar chart using the CHAR and REPT functions; next, we’ll explore Sparkline bar charts as an alternative solution. You can find that here. SPARKLINE Function for Creating a Progress Bar Chart in Google Sheets.