How to Create a Project Tracker in Excel (2 Scenarios)
ฝัง
- เผยแพร่เมื่อ 2 ส.ค. 2024
- In this video, you’ll learn step-by-step how to create a Project Tracker in Excel to track the number of tasks completed and tasks remaining for multiple projects. First, you’ll learn how to set up the tracker, then how to insert checkboxes into cells, and lastly, how to create the progress bars to visualize each project's progress.
We’ll look at two different project scenarios in this video: one that tracks projects with the same number of tasks and another that tracks projects with different numbers of tasks. The first scenario visualizes the number of checked checkboxes using REPT Bar Charts, and the second scenario visualizes progress by calculating the percent of checkboxes that are checked and then applying Data Bars.
By the end of this video, you’ll be able to insert multiple checkboxes at once, calculate the percent of checkboxes checked, visualize the number of checkboxes checked using Data Bars and REPT Bar charts, AND have a Project Tracker to stay on top of tasks.
📖RESOURCES
- Download this workbook and follow along: excel-dictionary.com/pages/yo...
-More info on Excel Checkboxes: Simplify data entry and reduce errors with checkboxes in cells (microsoft365.com)
🤓SHOP EXCEL DICTIONARY
- COURSES: excel-dictionary.com/collecti...
-GUIDES: excel-dictionary.com/collecti...
-MERCH: excel-dictionary.com/collecti...
-TEMPLATES: excel-dictionary.com/collecti...
📩NEWSLETTER
-Unlock the full potential of Excel and PowerPoint with expert tips delivered to your inbox each week: www.excel-dictionary.com/subs...
📺SUBSCRIBE
-Subscribe to never miss a video: / exceldictionary
🕰️TIMESTAMPS
‘0:00 Intro
‘0:19 Set up the Project Tracker
‘0:30 Insert checkboxes in cells
‘0:52 Using checkboxes in formulas
‘1:27 Create progress bars using REPT Bar charts
‘2:01 Count the number of checkboxes checked
‘3:39 Project Tracker limitations
‘3:52 Create progress bars using data bars
‘4:02 Calculate the percent of checkboxes checked
‘4:46 Apply data bars
‘5:08 Wrap up
#excel #exceltips
I don't have a checkbox option under insert.
Hi there! They are rolling out to everyone soon and are available for Beta users. To join the Beta channel to get the checkboxes, go to File, Account, sign up for the Microsoft 365 insider program, and then switch to the Beta channel. Here is more information on the program/how to join: insider.microsoft365.com/en-us/join/windows
I found the check box in the Developer tab under the insert options under Form Control.
Hi there,
You can replace checkboxes with “x” and adjust the formula accordingly. For progress bars, use: =REPT("|",COUNTIF(C4:G4, "x")). You can scale the number times argument, for example: =REPT("|",COUNTIF(C4:G4, "x")*5). Apply this to the remaining columns.
For percentages, use: =COUNTIF(C4:G4, "x")/COUNTA("x"). If there are multiple columns, divide 100 by the number of columns. For instance, with 6 columns, the formula would be: =COUNTIF(C4:H4, "x")/COUNTA("x")*0.166. This is based on the calculation: 100/6=16.66.
I hope this helps!
@@bellaastropics, thank you!!!!
i did too but it's not the same as the one in the vid. @@bellaastropics
Thank you!!
This is really valuable to me, thank you.❤😊
Thanks for help!
You are great, thank you
thanks a lot
This was absolutely great! I had to figure out a few steps in between by YouTubing them but once I got it, perfect. THANK YOU!!
Can you show how to make a credit card expense report with the addition of expense categories as a drop-down menu that can be summarized with a pivot table/chart that is shown on a dashboard?❤❤
Is it possible to limit the selection to only one checkbox?
Thank you for teaching us this new skill and how to create it Project tracker in Excel. Can this be done in Google sheets? I'm in a group project in my MBA course and I'd like to create this for our group so everyone can utilize it while doing our project.
Yes! I can create that tutorial! But in the meantime, here is a short I made showing how to create it: th-cam.com/users/shorts_t6yQwJJnb0?si=kZyLmOh-HJndj1F8
Hi, have the font stencil and playbill installed yet after changing the font they do no turn into bars
To remove the visible ‘True/False’ labels in the cells, change the font size to 1.
Will it also be suitable for the projects with different number of tasks. For example some might have 5 tasks some might have 2. Will it still show the right progress?
Scenario 2 shown at 3:52 will work with projects with different number of tasks!
Can't wait for Microsoft to roll out the full version! I have a work account and don't have access to the beta.. =/
hello,
many thanks
what is the bar symbol to write in command Excel !? is that letter (I) ?!
thank you again.
To insert the bar symbol you press SHIFT \
I was able to create floating check boxes but I do not see the lines in the progress bar and i inserted the formula correctly and tried adjusting the sizing.
Hello! Could u please find this function for Google Sheets? THX
Thanks for this vid! I had to rewatch a bunch because my checkboxes do not automatically show true or false. Now that I figured it out, it's awesome!
How did you do it?
It ended up being an issue with the my company not allowing me to add the checkbox, but once they enabled that feature it worked just like the video showed. Not sure if that is helpful.@@hlca143pr
How did you do it?
@@kyrianofodum2216 I had to have my work enable that feature in Excel. Normally it's easily available, but we had a weird setting and now that worked fine.
I need to add a progress tracker in text data ( not number ) is the formula same for all? Actually what is this 10 stands for ? I 've a table with text data ,not numbers ,i really wanted to do this plz help😢
I've managed to do this manually (added check box to the Developer tab, added individual check boxes and then linking each one to it's cell), is this new functionality or am I missing something?
Hi there! They are rolling out to everyone soon and are available for Beta users. To join the Beta channel to get the checkboxes, go to File, Account, sign up for the Microsoft 365 insider program, and then switch to the Beta channel. Here is more information on the program/how to join: insider.microsoft365.com/en-us/join/windows
Hello. Thanks for this.
My checkboxes aren’t being marked True/False, so the rest of the formulas aren’t working. How do I fix that?
Same here
I figured out how to link the cells. Now how to remove the ‘True/False’ label that shows up in the cell?
I am seeing “true” and “false” statement on my checbox cell when I choose it, how can I get rid of them?
I dont have check box in office 2016
I cannot find option insert checkbox im my insert menu...
Hi there! They are rolling out to everyone soon and are available for Beta users. To join the Beta channel to get the checkboxes, go to File, Account, sign up for the Microsoft 365 insider program, and then switch to the Beta channel. Here is more information on the program/how to join: insider.microsoft365.com/en-us/join/windows
I didn't find check box
The bars don't show up after I add the countif command 😢
Did you make sure to update the font to stencil or try the data bar method used in scenario two?
How to get a Bar symbol, please tell Ma'am
It’s in the video…
My Excel ribbon doesn't include checkboxes under the Insert tab. When I try to insert a checkbox from the Developer tab, it doesn't work. Is there another way to achieve the same results?
Same issue here
Merhaba harikasınız ama bende hücreleri seçip onay kutusuna tıklıyorum seçtigim hüçrelerin hepsine onay kutusu cıkmıyor 1 tane cıkıyor sebebi nedir açaba ? şimdiden teşekkürler