Use the Sparkline function in Google Sheets to create a mini Progress Bar Chart
Updated: Apr 9
The Sparkline function in Google Sheets creates a miniature chart contained within a single cell. This is great for showcasing your data visually in a compact manner. Therefore it can be utilised for creating a progress chart that is strikingly similar to the previous method of using the CHAR and REPT functions. Check this post out - Basic Progress Bar Chart for full details.
In this Task List Management post, you'll see a progress bar in action for each status in a To-Do list.
Pick up your FREE Basic Task List Google Sheets Template below. This template sheet automatically moves your tasks to another sheet based on their status. There is also a Pro version with enhanced functionality.
The Pro Task Management Template is also available via Etsy.com
CHAR and REPT vs SPARKLINE function to create a mini Progress Bar Chart in Google Sheets
Here they are side by side; CHAR and REPT function on the left and the Sparkline function on the right.
Out of the two methods, I prefer the Sparkline option since it’s possible to achieve a variety of charts within cells; we’re not just limited to a progress bar.
In this example, we’ll just look at the bar chart, but we will look at all the other Sparkline options in another tutorial as they definitely add an element of ‘je ne sais quoi’ to your sheets!
Just as we did with the previous CHAR and REPT functions, we will need some data to pass into the formula so that the progress bar can display properly.
We’ll stick with the same example of assuming we have completed two tasks, and we’ll also specify the total number of tasks within the function.
The progress bar will fill up the space in the cell relative to the size of the cell itself, so if you change the cell's width, the progress bar will dynamically adjust itself, a nice feature of the Sparkline function.
Let's begin by typing out the function next to the cell with the value of 2 in it:
=SPARKLINE(
You can see the Google function helper is expecting the first argument of the function, which is data - We’ll select the cell with the value of 2 in it, followed by a comma to move to the next argument in the function; [options].
This is where things can get a bit unnerving when you’re new to functions, but bear with it and just follow these steps.
You can see the Google function helper shows an example, and we’ll stick with this for now.
SPARKLINE(A2:E2, {"charttype","bar";"max",50})
The [options] is used to set the type of chart you want, for example, a bar or column chart, as well as colours for the chart and other specific settings like the max option shown above.
The general rules are:
1. All of these options are considered as conditions that should be placed inside a set of curly braces { ... }
2. Each individual option will be enclosed in double quotes, for example, "charttype"
3. Option values like the type of chart will be enclosed in double quotes, for example, "bar" however, numbers and boolean values (true and false) do not require double quotes, for example, the 50 value, which is associated with the max option in the example above "max",50
4. You can have multiple options and associated value pairs within the function, and these pairs should be separated by semi-colons
5. Lastly, options and their associated values are separated by commas
These are the two pairs in this example:
"Charttype","bar" is the first pair, and "max",50 is the second pair.
Moving along, let’s type out these two pairs in our formula, but for the max, we’ll set this to a value of 10 rather than 50 (This is the total number of tasks in our example).
Remember to include your curly braces around all the options and close the parenthesis to finish the function.
When you’ve finished, it should look like this:
Now we can jazz it up to suit your particular colour fancy. This will entail an additional option pair which is written with the same rules as before; option and associated value.
The colour option uses the American spelling of colour, and it’s possible to have multiple colours in a chart; therefore, each colour option will be written like this to identify them:
"color1"
"color2"
The associated value can be written simply as a word, such as "green" or "blue" for example, or you can use hex values to get the exact colour you’re looking for.
We’re going to use a hex value in this example, so we’ll add this option pair at the end of the last pair:
"Color","31c257"
Remember to include your semi-colon to separate this pair from the last pair and add a comma to separate the option from the option value:
Now we’ve changed the default colour of the bar from orange to green.
It sounds like a lot of work, but once you get used to the syntax, throwing these Sparklines together is a breeze. It just takes practice.
Okay, now we’ll take this one step further and add a second colour to get our Sparkline chart looking like our CHAR and REPT function progress bar from earlier.
For this, we’ll need two numbers to refer to.
One for the tasks completed and one for the outstanding tasks.
You can see in the example below we have two numbers along with a green and grey progress bar.
To achieve this, we just refer to a range G12:H12 now rather than just one cell, and we add another colour option pair to the end "color2","#e4e4e4"
Remember to include your semi-colon to separate this pair from the last pair and add a comma to separate the option from the option value, just as before.
You can play with different colours, like this yellow bar below which uses the option pair of "color2","#ffd966"
As alluded to earlier, there are a variety of options for Sparkline charts, which can be found here, or check out this post on all the different SPARKLINE charts you can adopt. Sparklines in Google Sheets: A Simple Guide to Rich Data Visualisation
You can really apply Sparkline charts to just about anything where data can be visualised within a single cell. Check out this post on Creating a Gantt Chart in Google Sheets; this is a good use case for tracking days.
Feel free to test this out yourself using the example sheet used in this tutorial for Creating a Progress Bar
Have fun! : )
