Project Based Task Management

Conditional Formatting Beginner

About this Tutorial

Create a better checklist for project based task management.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNTA() COUNTIF() IF()

Video Transcript

<div> Okay, we're creating a checklist here. Pretty much an epic checklist . And one of the issues we have is that we have everything in projects. This is very common. We break down tasks or maybe we have a job and a role, and then we get a project. Then we have multiple mini projects within that we need to. Do some tasks, and we wanna manage that for ourselves and maybe for our boss as well. We wanna manage how far are we.

One thing if you're working on your own and you're creating checklists and task lists to manage or you have this task management that you're trying to manage you wanna keep track for yourself and gamify it a little bit to see how far have you gone today? So I'm gonna show you a few things that you probably already know, and then I'm gonna show you a few things that you might not know, which is like conditional formatting of the header row based on if you have this complete.

Because one simple thing we can do is probably add some rows above. You can do some count if, maybe count if a four a. 13, I think. And we need to add account if for true, because our check boxes are true and when they're checked and false, when they're unchecked. So now we have a count. Okay, great. We have a count and maybe we can wrap this in a concatenate concatenate and cat eight and say four of, and then maybe we can do a. Count all or can't count a same thing, but in the B column, B four, colon B 13, 13. There we go.

So now we have four of 10 done there and we can bring it over here. Zero of 10. Zero of 10. We can copy this all the way over. Maybe we can even add down here a similar thing. Let's add one row above so we have similar. And this is okay, right? These are automatically changing to the sort of relative to where we are pasting them. And great. We now have this sort of X of y done. And this could be any number of tasks, but this isn't, this number up here isn't much different than. Just looking at these check boxes, right? Maybe we wanna put this in another tab.

We definitely can put, create a summary tab and have project one all the way down to, I think we had 12 projects, something like that. And we can take each of these Command X. And move it over here. And the, if we do command X or cut the formulas will stay the same. Great. So we can do that, but not much better. What if, let's say instead of doing that, let's delete that row. Let's delete these rows. Let's delete the rose. What if we had a red green. Kind of thing. Like red, it's not complete.

Maybe there is a orange where it's like almost complete or over halfway complete. And then we have a green where we're like, we are done. And we want the entire project one here and be one to be highlighted. How do we do that? First off, we're gonna use conditional formatting format , conditional formatting . Now I don't really necessarily think colors are the best thing. I think some Icon some. I think the check boxes here are actually a great little thermometer kind of thing. But let's see, a lot of people wanna do conditional formatting .

One thing we need to do is we need to do a custom formula , and I think we need to do equals count if a to colon a 11. And that's true greater than five. And we want it to turn orange done. So if it's less than five, it's now four. It is nothing. So what we're actually gonna do is change this to a. Actually wait. We need to, let's say we, we haven't initiated it, so we don't want a color. We only want the color if there is one.

Not if it's less than five, which would be zero included. But we want to have a conditional formatting here where it's so we'll take that same, actually, we'll go and edit it. Let's go back over to formatting , conditional formatting . Let's delete that one. We accidentally put in, we're gonna take this custom format . Formula . Ugh. I'm gonna actually count if do it in all caps. Click done. Add a new rule, custom formula . Again, the exact same custom formula , but I will edit it and we'll say equals zero.

So we have to put, by the way, just a little note , we have to put this equals sign in front of this custom formula , and we want it to be just dark red. So I think we have a little bit of an issue here. We need this A two to be not relative. I think we need to do that. Oh, and it's not supposed to be zero. Sorry. Don't need that. We need it to be. Is equal to one. So if it's zero, we want nothing. We want it to be normal template . But the moment we start, okay, now we're in the red. We gotta get some stuff done as we click through it, we got four done. Great. Five done.

We don't have anything. So let's see. Let's change this. We wanna keep it red is greater than. Zero, let's say done. And I think we wanted to put this one first because Nope, we wanna do it last, cuz now we want this to be as well. We need the dollar sign so that it's not relative when it's in the B one range . When it's looking at that, it needs to look at a one. When it's a range , it needs to look at a one. And it's greater than actually four.

If we're halfway through, we want it to be orange. We're going to add another rule, do the same exact thing, custom formula post in there, and it's gonna be greater, oh greater than nine, let's say, just in case we, you can always edit that later if you need different Numbers, but we are gonna do the ca little dollar sign there and we are going to say, this is green. We are good to go. So the moment of truth, it is not happening. So what we need to do is put that green on top.

So what happens is it looks in the order that you have them first for the green one, then the orange one, then the red one. We need to put them in that order so that it is shows up. There we go. And then if there's nothing done, what we can do as well is do that conditional formatting for each and every one of them if we want. Okay. And if you're ever doing conditional formatting and you only use colors, I would also add some status. I would add some word, some image , something other than just color. So if count if same thing. The range is a two A 11. True.

Count if is greater than or is equal to, let's say in this case 10. And if the value is true, I want it to say done. Or if not done complete. And then I want, if it's false, I don't want it to say anything. I want it to remain blank. So now as we're going through, it'll be orange and now green, and it'll say complete. Over here. We can also bold that if we want, we can actually make it even white if that  If you're looking for a video in which you can grow your money or grow a plant inside of a Google sheet, if you've seen that viral.

Growing plant money tree in Gucci. I, uh, actually help create.   stands out a little bit more. And now that's not complete, not complete. And now we have that is an easy formula to copy and paste elsewhere as well. So let's take that conditional . Let's copy and paste that formula . All over on all of these projects, and then we're gonna see if this is working or not. Let's just, whoops. There it is. And it says complete, but it's in white, but it'll be, if you do the conditional formatting elsewhere, it'll be, there's complete. All of them work.

If they're all complete, they say complete. You can also even put it in this here. Project one you can say equals, you can have exactly the same thing, actually. But instead of complete, you say Project One complete and instead of nothing, we do Project one, and now Project one, the name of the, okay, project becomes project complete. Now that might mess you up a little bit.

If you're looking at this project one in the lookup and stuff might help you out also in other ways maybe, I don't know. But I thought that would be fun to show you a little bit away to superpower your checklist , your task management. If you're doing a lot of project based task management, you have tasks all over a bunch of different projects and you're like, which ones are complete, which ones are not you can keep 'em like this.

Now you see our conditional formatting has been copied over, so that is going to be an issue until you just copy over the conditional formatting for each one and change it for each one to be Project two to here, project three to here, blah, blah, blah. Hopefully that helps you out a little bit. Super powering your project based tax management. Task management buddy.</div>