 This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.

# Hey there stranger!

Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.

# Make Color Changing Progress Bars - Make Progress Bars with Sparkline: Part 4

Create awesome progress bars with sparkline formula. You can make progress bar colors can change based on the input.

Sheet Resources

Featured Formulas

Video Transcript

Hi, welcome back. We're doing now part four of make progress Bars with Spark Line. And something that is really interesting we can do is make the color change depending on some variable. Right now as this is how you build a spark line. You have some kind of value here. This is count if right now it's just counting if anything's true, counting it all up and saying the Max nine.

We don't have a formula for that, right? So this is a bar chart type. Color is yellow and absolutely we right now could change the color, but we have to go in here and change this color to green. And it changes it. But what if we want to change that color based on some situation. That's where the if formula comes into the rescue.

Just a quick review of what if formula does is you have the, if this is usually, if you do it in a cell, it's if we can. Go and see. I'll read about it. But what I'll tell you about is if you have some value comparison, you compare a value to another value and based on true or false, it'll do either the first thing or rather the second argument here.

True Or the third argument? False. So it takes three. Values, right? Three things. It takes a comparison and then based, if it's true, you do this. If it's false, you do the third thing. And how we string them together. So if we have, if we do X equals X, true false, what we can do is instead of this actually why let's do.

We can take this whole thing and instead of false, we can actually put another formula here and run that formula. So if it's false, then it'll ask, is X minus one. And we can do this as many times. Honestly, I've done this sometimes many times. And we can get to, so we can get a really cool if else kind of thing going on here.

If you're familiar with programming, this is one of the simple parts of programming, but in Google Sheets it's really difficult to do these sort of if else statements and here's where we, how we can do. So what we're gonna do though is let's go back to the simpler one. Okay. Just a simple if statement and how we do that, right?

We want to compare the amount of tax completed, right? So we do a count, if we count if in this range, right? Actually, we can do it here if count. In this range, right? And we wanna count the truths, okay? We gotta count. And what we do is we compare that to nine, right? And we say, if it's nine, we want it green.

If it's not, we want rent. Okay? So how we would do that is we will take our spark line, let's write our spark. Have a, I'm gonna copy and paste it cause I wrote it out earlier. We have it here. This is just a normal spark line. Let's go here, let's make, yeah, that's true. So that's a spark line of this, right?

Learn that. I don't need to go over that with you. And we will put that in the false part. And then the true. So all we do is put a couple commas here if, and we're gonna take this count if count if equals nine, then do the true right. So we want it green and we're gonna copy and paste the exact same thing over here and change the green to red.

So we're repeating it and hopefully you can read that here. If this count if is, or where is it? Count. If

we actually want to delete all this, we just want the count, if not the spark line. Sorry. So we don't need spark line three times. We just need spark line twice. We wanna go. If counts equals nine, then do this first spark line. If county if is false, we'll do red. That's what that's saying.

Ah. So I found out have Just a missing parenthesis here. So just had an, so we need to put, we, you could, to make this easier to read, you could put these spark lines within parenthesis, but I've chosen here not to and it got really confusing. So right now we have, if count, if this is equal to nine, we're gonna do the first thing that's gonna be green.

If not, it's gonna be red. So see it's red now, and as we get to nine it turns green. Cool. But we can actually go one value deeper than that and say, instead of just turning to green when it's completed, we wanna know, okay, it's red at the beginning and then it gets to like an orange in the middle.

So again, we need to replace this red, this one with red, this false. We need to replace it with another if statement. So let's take this entire thing. Oh, let's just copy it. And I'm gonna take this spark line here. So everything's an if and this is the fault. So if faults, I'm gonna paste it again here and now if so, we know it's less than nine, but we want to say if it's greater now, if it's greater than four.

Let's turn orange.

Am I taping? Taping here. We're gonna change this to orange.

We have an error somewhere. Probably in here if. Four. Where is it? Air? You have that picture. Okay, I found my error. It was, again, a curly bracket missing here. So sometimes this can get really complicated and you just have to go through. And one thing you can do to help yourself is to put all of these spark lines in their own curly brackets.

And so now all we did was copy and paste everything and change green to orange here into the fall. Now, if it's equal to nine, it's gonna be a green. If not if it's anything other than nine, then we're gonna go through another if statement. And this time I changed it to greater than four. That's true.

It's gonna be orange, and then if it's false, it's gonna be red. So now as we go that's orange. As it goes down more and more, it's. And that's how you make spark line change colors. Based on whatever value you want. You can change this to greater than two, or you can change this color to yellow.

And it gets, it looks very complicated. But as you hopefully saw a lot of this is repeated. You can just take the same thing you did. With this spark line copy and paste it and put it in the true false and put the fall, the true fall. Another if into the false. Wow, that's a really odd way there.

What I just said was very complicated, but I hope makes sense. You can change things to different colors and it becomes really, truly interactive and really a nice way to make your these progress bars a lot prettier. Thanks so much for watching.

Better Sheets