Make an awesome Project Management Dashboard with Excel (Free download ~ Part 1)
ฝัง
- เผยแพร่เมื่อ 11 ก.ค. 2024
- 💥Ready to use Excel PM Templates - chandoo.org/pmt/pmt-index-1.html
You are going to LOVE 😍 this awesome Excel Project Management Dashboard. In this detailed and hands-on tutorial, let me teach you how you can create an interactive, dynamic & beautiful dashboard for managing your projects.
In Part 1 of 3, we are going to cover the project plan Gantt chart part.
Watch Part 2 👉 Project progress charts - • Traffic Light Chart in...
and the finale (Part 3) here 👉 • Make a Fully Interacti...
💻👉Please download the files from here
================================
chandoo.org/wp/interactive-pr...
⏱Video timestamps:
==================
0:00 - Introduction to Wendy's PM Dashboard Problem
0:42 - Overview of the Gantt Chart
1:59 - Person & Activity data setup
2:40 - Calculation worksheet
4:20 - Gantt chart sheet setup
6:24 - Displaying Gantt with Conditional Formatting
7:53 - Filling missing columns with lookups & more CF rules
9:06 - Highlighting the current date column
10:03 - Making the Gantt interactive
10:48 - Next steps for the PM Dashboard
💻👉Get ready to use Excel PM Templates:
====================================
chandoo.org/pmt/pmt-index-1.html
#ExcelDashboards #ProjectManagement - วิทยาศาสตร์และเทคโนโลยี
Hi all... Hope you found the video useful.
Like this comment if you want to see an "ADVANCED" version of this gantt chart with progress indicator, switch between daily / weekly and option to exclude weekends.
LOL... 'This' is the simple version?? (great vid, just very complex to the junior user).
Great vid, Could be possible to add actual progress to that Gant chart?
Lovely Video clip! Sorry for the intrusion, I am interested in your initial thoughts. Have you ever tried - Weydaniel Organized Dominator (do a search on google)? It is a good exclusive guide for getting 7000 plus project management and business templates minus the hard work. Ive heard some amazing things about it and my m8 after a lifetime of fighting got amazing results with it.
This is fantastic, thanks!
Features that I'd like to see:
1. Exclude weekends and holidays
2. Effort estimation
3. Fill the activity Gantt chart with planned vs actual progress
4. Move completed activities to history and always view activities in progress and upcoming activities first
5. Select the Week for which you want to view the data ( to view past activities)
You are welcome Gaurav...
You can easily customize the file to add these things.
1. Use workday() instead of +1
2. Effort = duration x hours per day
3. This is something I plan to cover in an advanced video
4. You can use Power Query to remove old & completed activities when they reach pivot table.
5. Add a form control and link it to either start.date or change the formula logic.
All the best.
Wonderful. Years ago I used Excel for Projects because it gave me more flexibility that MS Project and was more accessible for others. This is a more robust PM than the ones I created long ago. Thank you!
Thank you Elizabeth... Excel offers flexibility and creativity that other tools lack.
Every time I get stuck, I know you will have great resources to help. You rock!
I must have been living under a stone that I didn't notice your youtube channel before. Your tools and tips on the internet have helped me so many times over the last 2 decades. You are brilliant!
Thank you Mo. So good to have you here too.
You've been my go to right form 2011 but I used your website alone not your videos.
I'm loving your videos, you keep me entertained long enough to learn things!
You're an "excel"lent teacher. Thanks a ton, Chandoo!
Awesome stuff ...can't wait for your ADVANCED deck :)
Awesome video, great work. You make it look so easy 😃
The Lego mini-figures absolutely made my day! I just found your channel while looking for info about Power BI and I'm already excited to watch more! Keep up that wonderful sense of humor 😁😁
Thanks DB. I love 😍 LEGO and it was adding a bit of that to this video series. Welcome to my channel btw...
Fabulous! Thank you!
WOOOOW!! Brilliant!! Thank you
You are welcome Laura...
Hey @Chandoo. I just came for first time on your channel. I subscribed.
This is gold !
Thanks !
Welcome aboard!
Chandoo, trust me you are everybody’s friendly bandhu 🙃
Thanks for making life a lil easy through your informative content.
This is great. Will definitely use this in future.
Thanks Purna!!!!!!!
Thank you Pektus...
Awesome. Can't wait for part 2.
Thanks Vincent... won't be long.
Awesome video as always Chandoo, this will help be massively. Thanks.
Excellent tutorial and thanks for the freebies!!!
Thank you that in the current lock down I happen to visit one of your videos and could connect with you in this life of mine ..... I do work a Lot in excel and I am aware of many short cuts and tricks but you are really amazing ! Thanks for enriching the life of poor souls ! My compliments and prayers for you ! Thanks a ton ! I must have watched many videos of yours by this time and look forward for all new uploads.
Thank you Dharmendra for your love and appreciation. Stay safe.
Awesome Chandoo! Lots of great tips and tricks in this video. Thanks for sharing :)) Thumbs up!!
My pleasure 😊
Amazing... a lot of tips ! thanks!
Thank you.
Amazing, as usual!
Thank you Margarett.
Thank you man for this great work.
You are welcome Ben
Thank you much Chandoo , great video as always. Looking forward for your 2nd part of this video 👍🏼⭐️⭐️⭐️🙏⭐️👍🏼
Won't be long Rekha...
Wholesome and informative
Thank you so much, Chandoo! This is very useful for me. Downloaded your templates on your website! Thank you so so much!
You are welcome Rainymay...
Amazing, I’m definitely gonna use this bro
Thank you & Let me know how it goes...
Really appreciate
This is an awesome tutorial. Although, when the start and end dates on the 'people and data' page are empty (before starting a project for me), the pivot table removes the min/max columns. Is there any way I can keep these here even when the date fields are empty on the other table. Thanks!
Спасибо, очень полезно.
Awesome !!!!
😊
This is so good to learn, straight subs to your channel. Thanks a lot for that simple explaination.
Thanks brother...:)
Great video!
You are welcome Mohammed.
Liked the episode. Humor is good.. Concept and teaching is amazing.
Thanks Priti. :)
Chandoo the man! Our very own Wiz ✌️
Thank you ...
Great and simple, thanks alot
Glad you liked it!
Excellant explanation. Thanks a lot sir.
You are awssome!
😍😀
Thank you so much. This tutorial made Gantt chart making a piece of cake. Amazed by the procedure, I visited your website which turned out to be a gold mine. Kudos🤩🤩
Thank you Chandrani. Welcome to my channel and site. 😀
Amazing
Excellent one quick and superb fast. Thank you Chandru ❤️
Thanks Kannan
love this.. very clear explanation
Glad you think so!
This is awesome my friend !! Liked, commented :) and subscribed!! Cheers
Thanks Chandra and welcome to Chandoo community.
Awesome....
很清楚的教學，感謝您!!~
Many thanks Chandoo
You are welcome Mehran
Thank you very much. I will share it in my circle.
Thanks for the share.
A great video Chandoo. Thinking of marketing campaign tracking I would add a days remaining column, highlight weekend and public holidays.
Great share ! thx much ! How would you insert a ◄ at the start and ► at the end of the gannt bar. I can do it comparing the activity start date to the date on top of that col ; ie activity start date = the col date then col = ◄, and similarly for the end of the bar. But since the bar is made of format control it doesnt fit in. The entire bar also has to be made up of shapes like ▇ ; any thoughts ?
This is awesome work, Chandoo. Now I am thinking on how to use PTs from another sheet to do calculations at the next level
Thank you.. Go for it!
Excellent, thank you for showing more power of excel. +1 sub
Welcome to the club...
You are awesome
What a trick sir, never thought about using color filter like that.
Glad you liked it
Brilliant 👍
😍
wow you are amazing explain
Thank you! 😃
Super, Chandoo
Thank you Venkat.
Hi Chandoo! this video is gold! I have 2 questions:
1.- is there a way to get the into the Gantt "in progress" tasks and
2.- how can we red flag delayed tasks?
this is awesome thank you
Glad you like it!
I can't make column person adn colum % with Xlookup formula. can you help?
Bhai, You are awesome!
Thank you Salahuddin... 😊
You are amazing
Thanks for the love and super Matoell 😍
Thanks Sir really it will helpful to all of us👍
You are welcome Taizoon.
💥💯 BoOM
Thank you and a bear hug, Poorna.
Hugs to you....
Thanks.. pls make video on inventory management dashboard
Hi Chandoo , thanks bro , I came here when I saw your smile on the thumbnail ) ...
So nice of you
Hi there Chandoo🌈🌞, I really learned a lot from you videos, and is currently following your Project Management Dashboard Excel Demo. I tried applying IF Formulae to get rid of those Blank Date after the slicer applied, it was not working. I wonder if you could teach me how to do that (About those 0 value in your video shown at 10:40 clips). Thank you very much!🌈🌞
I learned to apply multiple conditional formatting on single cell in which order matters.
Good work
Thank you...
Thank you for the great video! Can you have in the same progress bar in the Gantt Chart view, but with different color, the Actual Start and Actual End dates of the project?
You are welcome. I am hoping to make v2.0 of this template with some additional features. It would be released in future. Stay tuned.
Sir u r gr8
Thank you Saurabh...
Do your templates provide monitoring for key stages and dependencies?
Sir this is simply insane. I'll surely use it to track my own work performance 😀😍😍 blessed that I came to know about you❤❤❤❤
Thanks Raghavendra.
Chandoo bhai ko mein 10 years sey track kar raha hoon. From his website
Yes mate, a Chandoo a legend
@@arindamdutta9371 Thanks Arindam for all your love and support :)
Anna.. Baahu and Bhalla made me chuckle .. too much creativity boss!! You can even create an analysis to find why did katappa kill baahubali!! Lol..
Jokes apart.
I love the way you deliver your lectures. More power to you, keep going!!
Hello all... The full series is out now. Watch the next videos here -
Part 2 - Project progress charts - th-cam.com/video/C0F_VKlTX1c/w-d-xo.html
Part 3 - Preparing the final dashboard - th-cam.com/video/Exlj5qH0rhc/w-d-xo.html
Chandoo, Thank you for this video! is it possible to have 3 different teams (in my case architects, engineers and construction teams) then create projects or jobs for those teams as well as a actual team members for each job?
Hi Alex.. you can tweak this file to make such project plans. Alternatively, you can create a base version with all team & people data and then split to three files (and may be use Power Query to bring relevant data to each file) and use that to make the dashboards.
👉 *Read this If you have trouble with MIN & MAX dates* in Pivot tables:
This seems to a problem with Excel 2016. You can use Power Pivot measures to get the same result.
Right click on the table name in pivot table field list and select "add measure" option. Then name the measure as "start of activity" or something and use the =firstdate(activity[start date])
Do the same for max, but use the DAX formula =LASTDATE()
How can you do this on Excel for Mac since there is no relationship function available?
Nice but very fast. Thankyou brother
You are welcome Sam.. may be you are on a 1.5 speed setting? Adjust the speed or pause the video at various points to take notes.
Chandoo great tips. Thanks for sharing your knowledge. I am looking for a way to stay organized in my day today things to do lists. Any tips on where and how to manage “ things to do” tasks while on a job. I use pen and paper becoz I can be flexible the way I want yo use it. Being an IT consultant I sometimes draw a process a flow diagram to capture my thoughts in things to do list and so on. Any suggestions is welcome. Thanks Chandoo.
Thanks Ari... I use notebooks and have built a collection of them over years as I am _kinda_ old school. But for more intense projects, I use One Note and add events to my calendar. These days, for my TH-cam videos, I have been making a list of *things to do* on my paper notebook and check them off as I progress. Hope that helps.
Hi sir I am using Excel 2013 and I am not able to Right click on the table name in pivot table field list , so what should i do now ??
This is awesome! How would I break up the dates into AM and PM sections?
You are welcome.
Instead of =previous date + 1, try this pattern
first cell after start.date = previous date + 0.25
next cell = previous date + 0.75
now repeat this.
@@chandoo_ Thanks!!
💜💜💜💜 How how, the Best
Thank you....
Your pronunciation of 'Gantt' and referencing it to Wendy... got me rolling on floor.... overall I enjoyed the tutorial...
Hi, can you also include planned start and end dates along with Actual start and end dates. Also if the Gantt chart can show the highlights of the dates or the lag that is caused.
Thanks for the suggestions. I will consider these features when making v2.0 of the gantt chart.
Hi,
Can you please help to provide one formula to calculate Interest on Balance but based on different Slab.
Since INT is daily, so Amount has to be divided by 360 at end.
My formula is working fine on positive balance but not on negative balances.
Thanks in advance!
Sir very informative. Can you help me link to download. Also can we show target vs Actual in Gantt chart ?
i like this video, you can add the display week by using scroll bar option , it will easy to display by weekly progress.
Thanks Ramasubbu... Yes, this can be easily enhanced with many other features.
Man you are oooosssum...
🙏 Thank you.
I'll have watch from your first video to understand this rocket science
You won't regret the journey. Awesomeness to you.
@@chandoo_ sure
will go through all relevant latest videos as u’ve been doing this for a really long time
Great content…
Satisfying…
If i Arrange 3rd row in week format, how can i highlight current week similar to what you have used =J$3=TODAY()
This is amazing Chandoo!!! I just have a question. I have my Gantt Chart arranged in mmm-yy format, instead of your format. How do I highlight the current month column?
Thanks KLrishnan... Instead of =TODAY(), check for =AND(MONTH(date cell) = MONTH(TODAY()), YEAR(date cell) = YEAR(TODAY()))
@@chandoo_ Thanks so much Chandoo!! It worked :) but I didn't start my formula like how you showed in the video with =(date cell)= .... it wasnt working when i did that... it worked only when i started the formula with =AND(........) .... I hope that wont cause any issues...
hello chandoo thanks for the videos and your website is filled with great lessons currently I''m working on excel form for daily production. which is for my manufacturing plant. and i learnt lot from you ... thank you once again . PEACE
What is the benefit in relationship, I do not see that we have used here, am I right? please advice
Hi, I am having trouble with the AND function - it is not returning an TRUE values. - Mr. B
Chandoo, great video. Question regarding the section where you're changing the Count of Start to date(Min) Count of Finish(Max). I can't seem to apply that as on mine they are grayed out, please advise
Thanks Juan...
This seems to a problem with Excel 2016. You can use Power Pivot measures to get the same result.
Right click on the table name in pivot table field list and select "add measure" option. Then name the measure as "start of activity" or something and use the =firstdate(activity[start date])
Do the same for max, but use the DAX formula =LASTDATE()
Did you make it Juan?? I was not available to do it. 😢 my formula appears as wrong
Hi Chandoo, can you add 2 things in the gantt chart as per project requriement.
1. Plan dates & Actual dates
2. Critical pathes of the process.
Please advice
Thanks Nischal for the comments. Adding Plan vs. Actual view is straight forward. You can do it as a challenge.
Adding Critical path is tricky with Excel formulas alone. I suggest not using Excel for performing CPM / PERT analysis.
@chandoo
Hi Sir, Hope you are doing great!
I want to make my career in data management. Pls suggest which excel reports should I need to prepare for interview.
Kindly guide 🙏
Thanks Rameshwar. All good here and hope you are doing well.
I suggest brushing up on concepts like data preparation with Power Query & connections, basic lookup & summary formulas, business uses of Pivot tables and example reports. Refer to below videos.
Power Query - th-cam.com/video/PiFAa_jjaEI/w-d-xo.html
Top 10 formulas - th-cam.com/video/Q-XGJbHG8Vo/w-d-xo.html & th-cam.com/video/P7w6JeNS_SA/w-d-xo.html
Pivot tables overview - th-cam.com/video/Kg7aZBtAWd0/w-d-xo.html
Sample dashboards & reports - chandoo.org/wp/excel-dashboards/
All the best.
How about the Gantt chart with timelines?
Isn’t the first row of date would show as #.?
If you add new activity would the link in the gantchart sheet got updated automatically?
You can hide that row or make it disappear with ;;; trick.
Once you add the activities, just hit REFRESH in data ribbon and they will flow to the dashboard.
I want excel to tally using VBA please guide