Progress tracker with animated image in Google Sheet - Excel Tips and Tricks - video Dailymotion

RobertBarone

Discover I can create a progress tracker with animated image in Google Sheet.  I will not be using an animated gif in cell, but use logical IF to shuffle the image. I will be using checkbox to replace traditional progress bar for project tracking and habit tracking. Did I day this can be used for habit tracking too?   Here are the steps as outlined on this video.   Add Checkboxes  1) Highlight A2 to A21  2) Insert ~ Checkbox   Calculations  1) Count completed task     =COUNTIF(B2:B21,TRUE)  2) Count incomplete task     =COUNTIF(B2:B21,FALSE)  3) Percentage completed     =B25/SUM(B25:B26)   Image Repo  1) New Sheet  2) Select A1  3) Insert ~ Image ~ Insert image in cell  4) Upload tab  5) Browse  6) Select your picture  7) Open  8) Do for the remaining of the images.  Repeat steps 2 to 7  9) Resize cell height (153px)   Growth Image  1) Select Cell B23  2) =IF(B27=1,'159_IMG'!E1,"")))))  Note these are my boundaries  =1  3) Resize row 23 height    google sheets animated gif in cell,google sheets progress bar checkbox,google sheets animated chart,google sheets progress bar with text,  progress tracker with animated image in google sheet free,progress tracker with animated image in google sheet app,google sheets animated gif in cell,google sheets progress tracker template,google sheets progress bar checkbox,google sheets animated chart,animate chart in google slides,google sheets progress bar with text,

Transcript

00:00If you want to create a nice animated version of a project tracker like this,

00:03this is how you do it. First, let's drop a bunch of checkbox, highlight the areas

00:07that you're going to drop those checkbox in, and you go insert checkbox, just like

00:12that. You have all the checkbox introduced in here. Next, we're going to

00:15count the number of checkbox that are checked in this cell here, and the formula

00:20is count if, and the first argument is all the checkboxes here like this, and

00:24we're looking for true status. Right now it's showing zero because nothing is

00:29checked. The second thing that we want to do will be to count the number of

00:33checkboxes that are not checked in this cell here, and the formula will be count

00:38if, and the first argument will be all the checkboxes again, and this time around

00:44we're looking for false state like this. So now anytime you check like that, you can

00:48see those numbers increment and decrement appropriately. Next, we're going to

00:54calculate the percentage completed. So basically the formula will be equal to

00:58number of completed tasks or checked box divided by sum of this two cell here, like

01:06that. And basically we're going to display this in percentage. I'm going to click on

01:12this icon here to make it percentage, and we'll make it no decimal places just like

01:17this. So that completes, and then let's say let's do a test here. You can see that the

01:21percentage increase as you start to put more checks in there, which is how it

01:27should be working, and they're correct. Next, we're going to create a database of

01:32images here. Let's do it on a second sheet. I've already got the second sheet

01:36prepared here called 159 underscore IMG. Click here, and let's introduce or add images

01:42on cell A1, and you go sideways this way. So to add an image, select the cell A1, and go

01:49insert image, insert image in cell, and make sure you are in upload here like this

01:57because you are putting in the image. Then go browse. I've really prepped up five

02:02images for this demonstration here. We're going to start with the very first image

02:06here, this one. And then if you don't see the image down here, just increase the

02:14height of that cell, and you can see that image. I'm going to make it a bit bigger.

02:17And the next one, we're going to do the same thing here. Go insert image like that,

02:23upload, browse, and the second image like this. I'm going to go ahead and do the rest

02:30of the three images here like this quickly. Okay, you've got all the five images now here

02:41on this separate sheet. Now I'm going to go back to the original sheet where the table

02:45is, and over here is where I'm going to display the image. So the formula to display or shuffle

02:52the image as this thing increases. This is the formula that you use. If you don't see

03:04your image or you see your image really small, just increase the height of this

03:08cell like this, and you can see the image coming up relatively well here. So let's

03:14look at the formula here in a bit higher optics here. Basically, I've created a

03:19boundary where if it's less than 0, you show the very first image from the

03:24previous sheet. And then if it's less than or equal to 0.25, I'm going to show the B1

03:30cell image. And if it's 0.5, I'm going to show C1 image from the other sheet. And if

03:37it's less than 1, I'm going to show D1. And then if it's more than or equal to 1,

03:41which is the max value, it's going to show E1 here. This is the sheet. And your A1, B1, C1, D1, E1,

03:47let me show you. If you go back to the other sheet, it's from here. A1, B1, C1, D1, E1.

03:53Let's go back to the sheet here. And that's how we do it. So as you start to

03:58introduce checkbox here, you can see that the plant sort of start to animate and

04:04start to grow, just like that. And you're done.

Progress tracker with animated image in Google Sheet - Excel Tips and Tricks

Category

Transcript

Be the first to comment

Recommended