Create a Progress Bar - Make Progress Bars with Sparkline: Part 1

About this Tutorial

Let's make a progress bar in Google Sheets.

Featured Formulas

SPARKLINE()

Video Transcript

<div> Hi, welcome. This is part one of four videos that I've done about spark lines and progress bars. Spark Line is a really cool function a formula in Google Sheets that allows you to do this bar across. It actually also offers you little other types of charts like bar charts line charts I think one of the most interesting and really easy to handle use cases of, of this spark line is as a progress bar . And so we're gonna go through four parts. In the first part, I'm gonna show you how to create a progress bar .

I'm gonna show you a few ways to manage that and then I'm gonna show you how to hide completed bars, cuz sometimes, and I'll show you a use case where you might have many of them to visually represent sort of a progress across on a large project. And sometimes when they're completed, it's very hard to tell if they're actually. Completed or not. So you want to know unequivocally, you know that this is done, so I'll show you how. And then one problem with Spark line is that it doesn't transpose, meaning it doesn't flip 90 degrees.

So I will show you how to do a couple ways to do a vertical progress bar that I, I think one way, sorry, without Spark line or one way is with Spark Line. A bar, a column chart . And then I will also quickly show you how to do a color changing progress bar . This is using an if statement or an if formula . And I'll show you that as well. But right now, let's go and create a progress bar . And this is pretty simple.

It, it is simple, but it is very complicated and it looks complicated. So in this use case, we have a bunch of tasks and we have these check boxes that will check off as we do them. And we just want to visually represent, even though these check boxes will visually represent, you know, okay, I'm four of the nine done. I just want this nice color colored progress bar . One of the few. .

Things that is detrimental or, or not very useful is that you can't tell how, whether the bounds, so if I turn on grid lines right now, like you can tell, okay, that's in one grid , but if I turn off grid lines, you can't really tell that that's where it is, but I'll show you some other use cases instead of just one bar chart across. But this progress bar is really cool. What, what we're gonna do with Spark. And I'll explain it a little more detail and we'll walk through how to build this, this formula . But what's really cool is you can give it a, a formula as a input here.

And then also you can give it a formula if you wish for the maximum. And really all you need to do is set that the chart , chart , this bar, you need to set the maximum and then you set the value over. , and you set the color as well, so you can actually change the color. I can change this to green and it changes this to green, but let's, let's build this step by step. So if we have a set of these check boxes, we need a way to calculate how many are checked. So what we'll do is we'll do a count if one second count if. And count if asks for a range . So we'll range .

We'll just highlight that range and the criteria will be true, right? Because checkbox is either true or false. I'll show you a really cool thing later on how to change that. And, and it might be a really interesting use case. You might not, all of these values might be just true or false. You might want actual numbers to make sort of a task to-do list a little nicer looking. So right here, if we just do that, we can see that this counts, right? 1, 2, 3, 4. So that's our number . You can tell that it's true if we just do all of them. 9 0, 9 0. Cool.

So we wanna put that as a value. And spark line is sparkline . And if you look here, it is data and then a bunch of options. And we have a lot of options we can choose from. I'm only gonna share with. Three of these right now. But the options, we're gonna put a little comma, we're gonna put them in curly brackets. That's one of the unique things about Spark Line is that it needs curly brackets. So we're gonna do curly brackets. We're gonna, we have to say what is the val value chart type . , sort of what is the function we're changing. We're going to do bar, that's the one bar across.

And then we also need a semicolon. This semicolon breaks up these sort of features. We're gonna do max, and that means what is the maximum value of if it was a hundred. We're gonna do comma or we're gonna just put in nine. Again, you couldn't do something like count all here. You can do count all count A, and then put in the range here. Do that, that will work as well. But we're just gonna put in nine to make it simple. And then we want to color, so we wanna be able to  dictate, you know what color this is? Maybe it is orange. And just put that in quotation mark.

So, so basically we've said, count all this, this is a value of that. Fill out this bar as much as you can until nine. That's what we're doing here. And that's Spark line. Oh. Literal, oh, I missed something here. Oh. I need color. So color is not color. Well, it's not color. So you might think right. It should be color. It's actually color one. Cause there's some more complicated things you can do to take in two values . Have one color, one value, one color, and the other value the other color. So we're gonna do color one, and now we have an orange. And see they work the exact. .

So that's how you make a progress bar with Spark Line. You can draw this out merge this with horizontally and it automatically moves it so you can make 'em bigger. You can make them across a whole lot of other things. Now what I want to do now is show you one more thing you can do with it and you can say, okay, instead of of value, just count. , we're actually gonna sum up these numbers cuz maybe these tasks either take longer time or they're valued at different rates. One way to do that is to just set a column with the values you want and then use a sum filter .

So the sum of the, and we're gonna filter for everything that's checked off. So we want to filter this column, I'm sorry, this column.  with the condition that B 22 to this column, B 22 to B 30 30 is equal to true. Right? And so now we can look at that number change, right? As I check off these boxes, that number changes drastically when I change it to 100. So that's a really cool feature you can add in instead of  instead of just a number of true false, you can do these numbers.

And the only thing I would mention is a, a very clear thing you should, I should mention, is that the maximum is now set to sum of this total, right? You, you might know the sum and you might wanna hard code it, but you might not have to if you, if you're gonna be changing these values a lot, like if you want to, oh, this one was wrong. Okay, this one's actually 200. , notice these, this sum of this column helps you move that max without having to go back to this formula . So I think that's a really cool thing to do.

Then the last thing we're gonna go through, think in this video is when, when I wanna change. So, so,  is okay to have these numbers different, but if you know, like you're not gonna be changing these numbers very often and sort of, you just wanna hide them you don't wanna be hiding these columns. You wanna just abstract this away. One way is to change the actual checkbox . So if I have a checkbox here, normally if I insert checkbox , it's normally true false.

So if it's false, now it's, and now it's true if I check it, but if I right click data of validation and it's checkbox , I use custom cell values , I can change this to anything I want. I can change checked to a hundred and unchecked to zero save. And now the value inside here, zero. And I check a hundred. So what this allows me to do is just. Make this formula a lot simpler. Some of these checked boxes, right? Cuz I know all those are so are values .

I've said all those values abstract abstracted away. So this makes the same exact chart as this, these to-do lists here and the same exact thing, but it's a little bit cleaner. But obviously you will only be able to do this if you. , if you can abstract that away, and you're not gonna be changing these values very often, right? If, if you're gonna be changing these values , then this probably is a better solution up here. To give you another way to solve the same problem for your choices here. So next video we're gonna go into how to hide a completed bar chart .

How to, when the bar gets completed, sometimes it's hard to tell and you want to very unequivocally know that it's hidden or, or, or completed. Sorry. So this one's done. And see, we're gonna be using these bar chart bars here. Thanks so much for watching. See you.</div>