Hey there stranger!

Sign up to get access.

Hide Completed Bars - Make Progress Bars with Sparkline: Part 2

About this Tutorial

Use the IF() formula to hide completed bars and display messages instead of bars depending on the situation.

Video Transcript

 Hi, welcome back. We're making progress bars and we're making progress through our four part series. In the first part, we were able to create a progress bar, and now is a different use case I'm gonna be sharing with you. And we're gonna, at the end of this, you're gonna be able to hide a completed bar.

So what may happen is you may have, instead of individual tasks, each task may have a bunch of steps and. These steps may go over to the right where you can't see them. Because visually speaking, these check boxes make it a really nice way to see what is done. Where are you at with these steps?

Sometimes we're gonna do the steps out of order though. There's many cases where. What you'll have is more data than you have to ana analyze very quickly. And so these spark lines, these bar charts over here, progress bars make a really quick, really good visual representation of how far along you are on each of these.

Items, right? And honestly for a much bigger project, this is really good to make Gantt charts. This is a really good way to create more sophisticated visual representations of where progress is. Managers can use this, but also employees can use this to help their managers very quickly see where they are on these, on projects.

So these bar charts over here, these progress bars make it really nice. To see where we are. But what happens is, say we completed all the way to task 15, we completed it. Now what might happen is that's fine to see that it's completed visually, but it becomes a little bit busy. , if you have an hard to read, say right about there, right?

You can tell just very slightly that these two are different, but you don't really necessarily know which ones are completed, right? We can say, Put a couple completed. Once you get to this end where there's very nuanced differences, you wanna very succinctly and quickly know that these are completed and done and don't have to be discussed anymore.

So one way, and we're gonna do this in number four, one way is you can change this color, but right now I'm going to, I'm gonna share with you one way to do this by. Essentially hiding them. Okay, so what we're gonna do and this is what we get, like we don't hide it, but we can put any message we want.

And we're gonna use an if statement. Okay, we're gonna build this, and how do we do this? Let me explain this a little bit. The, if formula is one of the most powerful formulas in Google Sheets and it allows us to give a sort of comparison and say, okay, if this thing is true, Then the do the first thing, right?

A comma, and then here's an item, and if not, do something else. Okay. So I don't know if you can tell here I'll actually go up here and show you there's this complete, let me double click on it and click over here. We have this complete here, and that's, if it is. Okay. And if false, then just do the spark line.

So what you learned in the last one was just this spark line part. What we're gonna add an if statement. Now, the if statement is a little complicated because we need to know, essentially we need to do the same thing that the Spark line is doing. So we just take that, right? If we say, okay, everything here we're gonna filter actually here, let's just build this from the beginning.

We have a bunch of steps. We have these check boxes, right? We wanna know which ones are failed and which ones are not. We're gonna do equals, and let's just find out how far it is. So we gonna count. All gonna count. All good Way to get the max. Number six. That's a good map. Are checked or not? Okay. We need to put a filter.

instead filter. Get rid of those. Help filter this with the condition that this equals true.

Are you? And so now a filter. Cut that off. None. That's true. Okay. So what we want to do is we want to count all that right? Only no. Okay. Zero


What is an interesting thing that happens here is that the count all becomes One because it's an error here. So what we want to do is actually put a if error, this is a fun thing to do, noth do nothing so that we actually get zero there. Okay? So we're gonna take that. So this is our function of what is in the spark line, right?

Spark line. We did this before. Spark line, this value in a range and then the conditions.  chart type. Lemme just go over this real quickly. Bar Semicolon Max is gonna be, just take this, we're gonna write this out again. Count all


Let's do all that. See

now we wanna do a color. Gonna end that curly bracket. Wanna do color one? , let's set this color to green.

Great. And so now when we have none clicked, let's do, let's check this. I'll click None clicked right, but when we have it completed, okay. We want this to go away. We want it to say, So we go, if we, now we know everything else. If it's all false, if it's completed, what is it gonna do? It's gonna have be equal to this count all.

Count all,

oh, I put that in the wrong spot. We're gonna do an if statement. There we go. Equals if. I'ma comma, because this is gonna be the false and the true is I'm gonna just copy this part. If this is equal to this part, the count all,

let's do that.

Let's do that. Let's just type it out. It's not . Cool. So we're saying if this is actually true, right? If this count all is equal to all the filter is true, then right here in between we are gonna put anything we're gonna do come completed.

And so now, We have this great sort of progress bar that starts out blank if nothing right. That's a very quick visualization to see if it's blank. Now we have a nice progress bar to show while we're in the middle of it, and then if we actually complete it. Boom, it says Completed. We know unequivocally it is full.

We have done it. And so that's a really good way to visually help yourself know where you are in the task. We can see down here, we can see we are in the middle of these tasks, but which ones are completed? Other ones that say complete. Next up we're gonna run into a problem where sometimes we wanna make a vertical chart, a vertical bar, progress bar, and I'll explain why you can't use Smartline for that.

And then I'll also go into actually showing you how to do one here. So check out that next video. Thanks. Bye.