You might have seen a viral TikTok where there is a progress bar and a growing sort of garden of flowers or potted plant that's growing as you check off these task bars tasks. So I want to walk through what this formula, the combination is how it's really cool it
How To Make That Viral Growing Plant Progress Chart - YouTube
(00:00) You might have seen a viral TikTok where there is a progress bar and a growing sort of garden of flowers or potty plant that's growing as you check off these task bars tasks. So I want to walk through what this formula, the combination is how it's really cool it and I am doing this by the way, without.
(00:22) Seen exactly the formula used. This is my own idea and concept of this, so I haven't. Looked at the formula that they use. But I do wanna share with you how to do this because you might want to create your own task list that has a growing something, right? And I'm gonna show you how you can edit the images that are used and edit the pieces so that you can be more flexible with this in any of your task lists that you want to add this to.
(00:49) And. In addition to images, once you know 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 sheets. It's really flexible and I wanna share with this with you. So I wanna show you an. First, so here's a list of tasks.
(01:10) Maybe you have them on Monday, Tuesday and each day of the week perhaps, or weekly tasks that you do at the first week of the month, second week of the month, no matter what. And if you start at. As you start, you have, nothing growing, right? And then you add one task, you're like, okay, I got something growing.
(01:29) 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? 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. But let's go through this.
(01:46) Let me show you this and you can see how it changes as the number of tasks are done. If you go from zero to one, it adds a little plant. If it goes from one to two, it adds, makes a bigger plant. And then even a bigger plant after that. And actually after this it's na. We haven't gone, I just wanna show you this simple one and then you can add more as you go.
(02:08) 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. Image. I will show you all of this, but first I wanna show you how to set this up. One is you just have a set of let's actually copy this and delete everything, duplicate it, and we will delete this one.
(02:30) Let's just delete everything here and even these check boxes. So the first thing is create some tasks and add these check boxes. So we go up to insert checkbox, and we're gonna just do that each and every column here, as many as you need. Now we have a blank canvas. But we don't necessarily have a blank canvas.
(02:51) We have to do one more thing to set this up. We need the images. I put these on a sheet called plants just so that I would know the. The word that you'll see later when we type this out, some simple word. It is literally just an image to a PNG here. Actually, one of them is a jpeg. I just found these online.
(03:11) You can do this, but I would actually recommend hosting your own images, especially one thing if you are going to create a task. A management system and sell it to someone else, a hundred percent. You should be hosting your own images. If you are just 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:34) 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 URL of the actual image, not just click the download button. I had to go to the download button and click find and find the actual image address url. So you put those here and what we're gonna be doing, Is manifesting this image function.
(03:55) This image function is really cool. We can do image literally equals image, and you can have any URL online that is an image and it will show up here. It can be a chart, it can be a piece together, api. You can sometimes get a URL from an api or you can get a static image that is hosted somewhere like here on clip art Library image is really powerful function and formula inside of Google Sheets.
(04:16) That's the, that's one next setup. So two things. We have our check boxes. Yes, we have our plants the images that we want to show. And specifically there's a gr they're in an order. There's The ground, and then there's a little sprout, and then there's a potted plant with a bigger sprout.
(04:34) And then there's alar much larger sprout. So you can use multiple images here that are of the same thing that is just growing and it'll look a little neater than this. I wanted to show that there is a different though so I did this sort of on purpose badly to show that there is a real difference between when you click and see the different images.
(04:52) Okay, so let's. The first thing we're gonna do is type in the equal sign, and we're gonna do switch. 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.
(05:12) In the case of switch, they give you expression, which is going to be, this is actually probably the hardest part of this whole thing is count if the range is going to be B B2 here, B2 to B seven. The criterion is gonna be true. We just wanna count if it's true. Check boxes are really cool because they're automatically true or false, and that is it.
(05:34) You can actually change that and that's 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. It is simple. It is built in. It is baked in, it's awesome. So that's it. We use the count. If now it's gonna count any time that there are Check boxes.
(05:52) Let me delete this switch and just show you real quick what this is gonna do here. Zero, because nothing is checked. We. One check that changes to one. This changes to two when we have two, three, and if we uncheck it, it goes back down to two. So this count if is just gonna give us the count of how many trues there are, how many check boxes are checked.
(06:11) Okay, let's wrap that back up with switch. There we go. Now that's our expression. We know, okay, it's gonna be zero to N, 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. If it's one show, this image we do comma, and now we have case and value case one, value one.
(06:32) In the case of zero, we literally type. Zero. Nothing else. We don't have to put in quotes. We don't have to do any formulas here. We literally put in the number zero and 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.
(06:50) So we're gonna type an image and we know that it is on the plants. Sheet. So we're gonna just type plants, exclamation mark and a one that is the URL of the very first plant that we wanna show. And we're gonna just end it right here, and I'll show you the next steps once we see this. So now we have this image of ground.
(07:12) It's gonna go away and we're actually gonna get an error if it, we actually have one, but we can copy and paste this all the way over. But why is it not working? What happened here? It's because our. These are relative the formula the arrays. Are relatives, so they are changing. Look, we have B one here, which we didn't do.
(07:32) C2 here to C seven. We need to bake these in. We need to keep these the same. So we need to lock them in with this dollar sign. We're gonna put a dollar sign in front of the BS and in front of the A and the one I think we wanna do as well, we might actually want to, I'll bake it in the B2 to B seven just in case we wanna put this somewhere else.
(07:52) But now we copy and. Perfect. We are absolutely on the way to having this done all over, but again, we have a problem once we. This what? What's going on? Nothing's happening, right? We want something to happen. We want it to grow. All right, go back to our image, add another comma. Now we're in case two.
(08:14) Okay, so we have case one, which is zero case two, which is just the number one. We just wanna know, okay, if it's one thing checked image, we're gonna retype plants. And instead of. One, we want a two and we're gonna end that parenthesis there. So let's look at that. Let's just hit enter and if we have one, boom.
(08:34) It is. A sprout. 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 gonna be an error. What? Whatever we got, we fi fixed the error already. Once we start testing this stuff out, right? And now we have our one, but our two isn't working, what do we do with our two? How do we get that to work? Same exact thing.
(09:00) We add a. And we add the number two, which is now case three. So we have case one, which is zero case one, which is one, case two, case three, which is now two. And we go image and we type in plants and we can do a, with the dollar signs and we can do a three. It is the third row on the plants a column. Now we can just, let's just add the fourth one.
(09:25) I think we. Four. 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 four. There we go. So now as we click this, let's just test this 1, 2, 3. Now we get an error here, but you can see the logical progression if you want to add more images, if you want to add more plants.
(09:50) Or what's more interesting too, is you can change this case instead of zero, instead of one. Maybe it is nothing and it's 1, 2, 3. Four, we're gonna get an arrow when it's zero because we don't have a case of zero. So maybe we add this here, zero, and we just have nothing. We have no dirt at all, right? We, if we have nothing, if we have one, now we have some dirt.
(10:15) We started till, and we started this motion, right? We might want that. And that's how you do that. If you add that zero and then have nothing inside of there, it'll show nothing If it's. So let's say though, you want one and two to be the same image. So one thing you can do is just change that a two to a one.
(10:38) So now we have 0 1, 2 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. So we can change these A two to A two, and now we have more options. We can do five and we can. Image plants a three, I think. So now we have a much slower growth. We have nothing.
(11:03) If there's zero, now we have some ground plant. And then potted plan. Right now we have some error when we have six, so we can keep adding to this if we want. Maybe we don't want any change. Maybe we have, 10 tests we need to do and only five images. But sometimes we want, incremental changes so you can.
(11:22) Take your pick. You can choose either. Okay. Every single checkbox checked is gonna have some feedback, which is actually really important to have and really good to have that every single action has some feedback and some visceral real feedback. I think this is a really cool progress. Chart really what great way to visualize your own progress, especially around habits or daily tasks, that in that help great habits.
(11:45) And I hope that this video helped you understand how this works and how you can implement this, and also gives you some flexibility of changing those images to something else. Changes the images also changes the way you use those images if you want to do 'em very fast or very slow based on multiple criteria instead of.
(12:05) 0 1 2 always changing. You can have the same image in multiple ones, so it has a slower curve and hopefully this combination has been very helpful. We have switch, count if and image all in here. hope You enjoyed watching that and understanding literally how it's built that viral. Ticktock. Sheet. Uh, if you're looking to actually get better at Google sheets every single day, check out this video where I share some tips and tricks generally about how to get better.
(12:34) And if you're looking to become a Better Sheets member, you want to get inside of Netflix for Google sheets, essentially check out this video. I'll show you. What's inside.
Google Sheet Formula Frustrations Solved
Find every formula you'll ever need in Google Sheets here at Better Sheets. Whether you're a beginner or an advanced user, I’ve got you covered with a comprehensive guide of 504 formulas.
Are you struggling to find a specific value in a column of data? Look no further than the powerful VLOOKUP formula. Or maybe you need to calculate the sum of values that meet specific criteria - try out SUMIF. And when it comes to frequency of values, COUNTIF has you covered.
Step-by-Step Google Sheet Tutorials
Find step-by-step tutorials for any formula here on Better Sheets. Every formula page comes with links to written blog posts and Better Sheets tutorials featuring the exact formula.
Looking for a way to segment data based on specific criteria? The FILTER formula is perfect for you.
At bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level.
There’s no shortcuts to becoming a Google Sheets pro! Learn more here at Better Sheets
Watch the magic for yourself. Video tutorials at every turn here at Better Sheets.
Your spreadsheets will never be the same again.