GRID is a friendly data tool for modern teams

With GRID, you can create beautiful reports and analysis, add key insights and explain data to your team in a way that everyone can understand.

_______

➜ Google Sheets Tip #218: Amazing In-Cell Sparkline Progress Charts

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

For today's tip, we're going to see how to build a sparkline bar chart to show progress towards a goal.

Suppose we have a class with 17 students, and 7 of them have completed their end-of-year project.

We have the value 7 in cell A2, and the value 17 in cell B2:

Then, we can calculate the % complete as follows:

=A2/B2

which gives an answer of 41% in this example (format the number as a % to see this).

To calculate the % remaining, we could do this:

=1-A2/B2

This tells us there are 59% remaining.

We can use an array literal construction to do both of these calculations with a single formula:

={ A2/B2 , 1-A2/B2 }

This creates two data points, the % complete and the % remaining.

Next, we plug this data into our sparkline formula and set the type to be a bar chart:

=SPARKLINE({A2/B2,1-A2/B2},{"charttype","bar"})

Finally, we can set the colors for the sparkline if we wish (here, I'm using hex codes to set the colors):

=SPARKLINE({A2/B2,1-A2/B2} , { "charttype","bar" ; "color1","#FF1493" ; "color2","#FFC0CB" })

In our Sheet:

We can add a border to make it stand out more too.

We can simplify the sparkline formula slightly, by not using the array literal method.

Instead, we set a max value (equal to B2) as one of the sparkline settings:

=SPARKLINE(A2 , { "charttype","bar" ; "max",B2 ; "color1","#FF1493" })

Note that we can only specify one color now, because we only have a single datapoint for the sparkline data.

The max value is set by the "max" option in the settings, and points to the max value cell.

Here's an example with a sparkline bar chart, driven by checkboxes, to indicate how far through a checklist I am:

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben