Progress tracker with animated image in Excel - PART 3 - Excel Tips and Tricks
Discover I can create a progress tracker with animated image in Excel.
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.
Enable Developer Ribbon
1) Right-click anywhere in the ribbon
2) Customized the Ribbon...
3) Check on Developer
4) OK
Add Checkboxes
1) Developer ~ Controls
2) Insert ~ Form Controls ~ Check Box
3) Right-click ~ Edit Text
4) Delete text
5) Right-click ~ Format Control
6) Cell Link A2
7) OK
8) Repeat steps 2 to 7 for all rows
9) Check all checkboxes
10) Change cell font to white.
I recently posted a video on how you can make an animated habit tracker using Google sheet like this.
But a few of my subscribers were requesting if I can do the same thing in Microsoft Excel.
So... I just have to try it. And this is how you can do it.
After that, we're going to place a check on every check box and change the font colour of the cell to white.
Just note that your loader index boundary might be different than mine.
In the next part we will finish off this tips and tricks... stay tuned.
Calculations
1) Count completed task
=COUNTIF(B2:B21,TRUE)
2) Count incomplete task
=COUNTIF(B2:B21,FALSE)
3) Percentage completed
=B25/SUM(B25:B26)
4) Loader Index
=IF(B27＜=0,1,IF(B27＜=0.25,2,IF(B27＜=0.5,3,IF(B27＜1,4,IF(B27＞=1,5,"")))))
Note these are my boundaries
＜=0
＜=0.25
＜=0.5
＜=1
＞=1
Image Repo
1) New Sheet
2) Enter 1 to 5 on rows A1 to A5
3) Adjust height of the row
4) Select B1
5) Insert ~ Pictures ~ This Device
6) Select picture
7) Insert
8) Repeat steps 4 to 7 to add the remaining pictures
Import Image
1) Ctrl+C on any picture
2) Go to main sheet
3) Change height of row 23
4) Select cell B23
5) Ctrl+V
Animation
1) Formulas ~ Define Name
2) Set Name to "GROW"
3) Refer to as
=XLOOKUP(186!$B$28,186_IMG!$A$1:$A$5,186_IMG!$B$1:$B$5)
For older Microsoft Office
=INDEX(186_IMG!$B$1:$B$5, MATCH(186!$B$28,186_IMG!$A$1:$A$5,0))
In this formula:
Cells $B$1:$B$5 contain the plant image
Cell $B$28 is Loader Index value
Cells $A$1:$A$5 contains list of Loader Index
4) OK
5) Select plant image
6) Change address in the formula bar to =GROW
7) Enter
Previous Videos
Progress tracker with animated image in Google Sheet - Excel Tips and Tricks
• Progress tracker with animated image ...
Progress tracker with animated image in Excel - PART 1 - Excel Tips and Tricks
• Progress tracker with animated image ...
Progress tracker with animated image in Excel - PART 2 - Excel Tips and Tricks
• Progress tracker with animated image ...
Progress tracker with animated image in Excel - PART 3 - Excel Tips and Tricks
• Progress tracker with animated image ...
#tip #excel #microsoft #shorts #short #shortvideo #shortsvideo #howto #how #google
