That Viral Growing Plant Progress Chart

Data validation Beginner

About this Tutorial

Create a growing flower pot from checkboxes with this bountiful formula combination. Members get free access to the sheet in the video.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNTIF() IMAGE() SWITCH()

Video Transcript

<div>00:00 Hello, so you might have seen a viral TikTok where there is a progress bar and a growing sort of garden of flowers or a potted plant that's growing as you check off this task bars tasks. 00:14 So I want to walk through what this formula combination is, how it's really cool. It.

And I am doing this by the way, without actually seeing exactly the formula used, this is sort of my own idea and concept of this. 00:31 So I haven't looked at the formula that they use, but I do want to share with you how to do this because you might want to create your own task list that. 00:40 As a growing something, right? And I'm going to show you how you can edit the images that are used and edit the pieces so that you can sort of be more flexible with this in any of your task list that you want to add this to. 00:54 And also.

In addition to images, once you know sort of the core idea of this, which is. Counting if and counting the true, maybe you can do other things like emoji progress bars or completely other things inside of sheet. 01:11 So it's really flexible and I want to share with this with you. So I want to show you it first. 01:15 So here's a list of tasks. Maybe you have them on a Monday, Tuesday.

And you know, each day of the week, perhaps, or a weekly task that you do at the first week of the month, second week of the month, no matter what. 01:30 And if you start, as you start, you have, you know, nothing growing, right? And then you add one task, you're like, okay, I got something growing. 01:37 You have some feedback. This is a really cool way to see, okay. Have I grown as a person?

Have I been doing these things that I need to do? 01:46 And so I have these different images and I use very poor mix of images just to show you that they are changing and they are completely different images. 01:56 But let's go through this. So I thought, let me show you this and you can see how it changes. As the number of tests are done. 02:05 If you go from zero to one, it adds a little plant. If it goes from one to two, it adds, it makes a bigger plant and then even a bigger plant after that. 02:13 And actually after this, it's NA.

So we haven't gone, just want to show you this simple one and then you can add more as you go. 02:20 The answer to this and the formula inside here is this, which is using three formulas. It is using switch . It is using count if and image . 02:30 I will show you all of this, but first I want to show you how to set this up. One is you just have a set of let's actually copy this and delete everything. 02:41 Okay. Hate it. And we will delete this one. Let's just delete everything here and even these check boxes.

So the first thing is create some tasks and add these check boxes. 02:56 So we go up to insert checkbox and we're going to do that each and every. All of them here has many as you need. 03:04 Now we have a blank canvas, but we don't necessarily have a blank canvas. We have to do one more thing to set this up. 03:11 We need the images. I put these on a sheet called plants just so that I would know the word that you'll see later when we type this out. 03:21 Some simple. It is literally just an image to a PNG here. Actually, one of them is a JPEG.

I just found these online. 03:29 You can do this, but I would actually recommend hosting your own images, especially one thing. If you are going to create a task management system and sell it to someone else, a hundred percent, you should be hosting your own. 03:43 If you are just sort of doing this for fun, for your own personal use, then yeah, go find some Google images, and then you have to find the actual image URL. 03:53 You cannot put these inside of a Google Drive and then access them there.

You will have to actually host them online somewhere, or get the actual image . 04:02 You can do a Google URL of the actual image , not just like click the download button. I had to go to the download button and click and find the actual image address URL. 04:11 So you put those here, and what we are going to be doing is sort of manifesting this image function . This image function is really cool. 04:20 We can do image, literally equals image, and you can have, any URL online that is an image, and it will show up here. 04:26 It can be a chart, it can be a piece together sort of API.

You can sometimes get a URL from an API, or you can get a static image that is hosted somewhere, like here on Clipart Library. 04:37 Image is a really, really powerful function and formula inside of Google Sheets. That's the next setup. So two things. We have our checkboxes. 04:48 Yes, we have our plants, the images that we want to show.

And specifically, there's like a, they're in an order. 04:56 There's the ground, and then there's a little sprout, and then there's a potted plant with a bigger sprout, and then there's a. 05:03 Much larger sprout, so you can use multiple images here that are of the same thing that is just growing, and it will look a little neater than this. 05:11 I wanted to show that there is a different though. So so I did this sort of on purpose badly. Just show that there is a real difference between when you click and see the different images. 05:22 Okay, so. Let's start.

The first thing we're going to do is type in the equal sign, and we're going to do switch . 05:28 Switch is a really powerful formula that gives us one expression and then tells us, and then we can say based on this expression , what is the result of that and different get do different values for different results in the case of switch . 05:44 They give you expression , which is going to be this actually probably the hardest part of this whole thing is count if the range is going to be B2 here B2 to B7. 05:57 Comma, the criterion is going to be true.

We just want to count if it's true check boxes are really cool because they're. 06:04 Automatically true or false and that is it. You can actually change that and that's sort of a more advanced function if you want to like you can change it to yes or no, but I like keeping it as true or false. 06:16 It is simple. It is built in is baked in. It's awesome. So that's it. We use the count if now it's going to count any time that they're. 06:26 Are checkboxes.

Let me delete this switch and just show you real quick what this is going to do here zero because nothing is checked. 06:32 We have one check that changes to one. This changes to two when we have two three and if we uncheck it goes back down to two. 06:39 So this count if it's just going to give us the count of how many truths there are. Any checkboxes are checked. 06:46 Okay, let's wrap that back up with switch . There we go. Now that's our expression . We know, okay, it's going to be zero to N. 06:54 However many there are here, we're going to have some counts. Now what do we do?

How do we say if it's zero do show this image ? 07:02 If it's one show this image , we do. Comma. And now we have case and value. Case one, value one. In the case of zero, we literally type in the zero, nothing else. 07:12 We don't have to put in quotes. We don't have to do any formulas here. We literally put in the number zero. 07:17 We can do Comma, value. Now what do we want to show when it's zero? We might want. To have nothing, but in this case we want to have that ground. 07:27 So we're going to type an image and we know that it is on the plants. Cheat.

So we're going to touch type plants exclamation mark and a one. 07:38 That is the URL of the very first plant that we want to show. And we're going to just end it right here. 07:44 I'll show you the next steps once we see this. So now we have this image of ground. It's going to go away and we're actually going to get an error if we actually have one, but we can copy and paste this all the way over. 07:57 Why is it not working? What happened here? It's because our these are relative. The formula . The arrays are, are relative. 08:09 So they are changing.

Look, we have B one here, which we didn't do. C two here to C seven. We need to bake these in. 08:16 We need to keep these the same. So we need to sort of lock them in with this this dollar sign . 08:22 We're going to put a dollar sign in front of the bees. And in front of the A and the one, I think we want to do as well. 08:30 We might actually want to bake it in the B two to B seven, just in case we want to put this somewhere else. 08:34 But now we copy and paste perfect. We are absolutely on the way to having this done all over.

But again, we have a problem once we hit. 08:45 This, what's going on? Nothing's happening, right? We want something to happen. We want it to grow. Alright, go back to our image , add another comma. 08:54 Now we're in case two. Okay, so we have case one, which is zero. Case two, which is just the number one. 09:00 We just want to know, okay, if it's one thing checked, comma, image , we're going to read. Type plants, and instead of a one, we want a two. 09:11 And we're going to end that parentheses there. So let's look at that. Let's just hit enter.

And if we have one, boom, it is a sprout. 09:21 Now look, all these are changing as well. Why is that? It's because I made a mistake earlier on. I did, I locked in this B column, and you probably saw me do that and said, oh my god, no, it's going to be an error . 09:33 Well, whatever. We got, we fixed the error already. Once we start testing the stuff out, right? And now we have our one, but our two isn't working. 09:42 What do we do with our two? How do we get that to work? Same exact.

Then we add a comma and we add the number two, which is now case three. 09:50 So we have case one, which is zero. Case one, which is one. Case two, case three, which is now two. 09:56 And we go image , we type in plants, and we can do A with the dollar signs and we can do A3. 10:06 The third row on the plants A column. Now we can just, let's just add the fourth one. I think we have four. 10:15 Yeah, we have four of them. So we can do, if there's three checked off, image , not images, image , plants, and then do A. 10:27 Four. There we go.

So now, as we click this, let's just test this. One, two, three. Now we get an error here. 10:35 But you can see the logical progression if you want to add more images, if you want to add more plants. 10:40 Or, what's more interesting too, is you can change this case instead of zero. Instead of one, maybe it is nothing. 10:49 And it's one, two, three, four. We're going to get an error when it's zero because we don't have a case of zero. 10:58 So maybe we add this here. Zero, and we just have nothing. Right? We have no dirt at all. Right?

If we have nothing, if we have one now, we have some dirt. 11:09 We've started till when we started this, this emotion, right? You might want that. And that's how you do that. If you add that zero, comma, and then have nothing inside of there, it'll show nothing if it's zero. 11:22 So let's say though you want one and two. It'll be the same image . So one thing you can do is just change that a two to a one. 11:31 So now we have zero one, two and two, one and two don't change, but two and three change.

And now we want maybe four to be the same as three. 11:41 So we can change these a two to a two, and now we have more. So for options we can do five and we can say image , plants, a three I think. 12:01 So now we have a much slower growth. We have nothing if there's zero. Now we have some ground. So we can say image , plants, a three I think. 12:07 Ground, plant, plant, and then pot of plant, right? Now we have some error when we have six. So we can keep adding to this if we want. 12:16 Maybe we don't want any change.

Maybe we have, you know, ten tasks we need to do and only five images. 12:21 But sometimes we want, you know, incremental changes so you can take your pick. You can choose either. Okay, every single checkbox checked is going to have some feedback, which is actually really important to have and really good to have. 12:33 That every single action has some feedback and some visceral real feedback. I think this is a really cool progress chart . 12:41 Really great way to visualize your own progress, especially around habits or daily tasks that help. Great habits.

And I hope that this video helped you understand how this works and how you can implement this. 12:55 And also gives you some flexibility of changing those images to something else changes. The images also changes the way use those images. 13:02 If you want to do them very fast or very slow based on multiple criteria in.</div>