Make an awesome Project Management Dashboard with Excel (Free download ~ Part 1)

meet wendy a busy project manager of the new project mega something she needs to stay on top of her game with the good no wait awesome project management dashboard in this video series let's help wendy kick ass at her work by creating a fully interactive dynamic project management dashboard using excel here is the final project dashboard we will be making this video is part one of the journey why let me tell you a secret that is not real but the dashboard is real let's go the card chart forms main component of our project management dashboard let's take a quick look at what it can do and then understand the process for constructing it here our gantt chart displays two level detail the category of the project activity and the activities themselves along with who wants the activity what is the start and finish date of the activity and how much progress has been made it also visually indicates which activities are completed with the green tick mark you can drill down this gun chart to look at activities that are assigned to a specific team for the purpose of this example i've put two teams and i can go into an individual team to see the activities that are assigned to the team members and their current status the yellow colored bar here shows the current date and it will give you a visual indication of which activities are ongoing as of today it also displays the overall project plan for 60 days with the view of current day previous 13 days in the next few weeks this is all dynamic so if you open it in a future date automatically all of this will be adjusted and the activities will move forward as needed now let's go ahead and build this one from the blank data file here i have my blank file which has the person data as well as the activity data you can download this excel file from the video description below are the cards that are linked up there now these two tables form the core of our gantt chart logic and visualization before we proceed any further let's go ahead and link up the person column here to the owner column there because it's the same data go to data ribbon and click on relationships and from here make a new relationship select the table people person and then activities owner and click ok this will create a mapping relationship between the people table and the activity table let's build the necessary calculation worksheet for our grant chart so i will add a new worksheet name it as calculations inserting pivot table because we have already linked the tables the pivot table creation defaults to workbooks data model which will feed on those two tables that we have already linked so i'll start by adding category and all the activities within each category we will get like a two level hierarchy and then i'll put start date and end date here first up i'm gonna change my pivot table design to an outline format so that category and activity take up separate columns next i'm gonna right click on the start date and then summarize this as minimum start date so that we'll get the earliest start date for each activity we'll summarize this end date by the maximum finally i'm going to add subtotals at the top so we can see for each individual category itself when is it that we can start it and when does it end the next step is to define the starting date for our gantt chart visual so in a blank cell type start date and specify today minus 14 as that value what this does is it will always start our gantt chart two weeks prior to the current system date and it will give you a visibility of what has just concluded and what is going to happen in future you can also customize this calculation based on your needs let us quickly name this cell just select the cell use the name box in the corner to define the name start date all the necessary calculations for our gantt chart are done let's go ahead and build the gantt chart start by adding a new worksheet we will call this as gantt chart we would like to show category activity person assigned to that start and percentage done and display the gantt chart in the successive cells here so we go here to the cell right about and then link this to start date quickly change this to a date format so we could see when our grant chart is going to begin next cell is going to be simply plus one you could use either plus one or alternatively you can also use the workday function comma 1 to pull the next working day once this is done i'm just gonna drag this for 59 times we will then make copies of these dates with a simple reference value so that we have two more rows with the same information you will soon understand why we need to do this then we'll select this middle row of dates apply formatting on them using the custom format code of d so that it will show me just the day portion of the date we'll repeat that for this as well but here instead use mmm so that we can see the first letter of the month so i can see 18 april first may 6th may now let's go ahead and pull this information from our calculation worksheet go back to calculation select all your pivot table data copy ctrl c go back to the garn chart right click and then paste as link so we'll get a link to the calculation worksheet category and activity are fine i'm just gonna select and move this there are a lot of zeros showing up because wherever the pivot table value is blank that will appear as zero here we can quickly fix this by adding some if condition around it and apply that formula so all the zeros are gone now we'll select these two columns and quickly change them to the date formatting we'll worry about person and percentage done in a minute but for now let's go ahead and create the necessary logic to display our garn chart and it will basically turn the cell into a color if the activity here is happening on that date we could do this in any number of ways i'm just gonna use the end formula here and simply say is this date prior to that date this date after that date and let's quickly adjust some of these references this will become dollar f6 because this needs to always look in column f that would be j dollar two so this would be either true or false depending on whether a certain activity is happening on a date and then we can just quickly copy paste this for the entire range select this entire range go to conditional formatting add a new rule if my cell value is true highlight it will use only cells that contain equal to true format fill and let's pick a color that is not too strong but still visible and i can see my garn chart coming into shape but doesn't look like the card shot i showed you at the start of the video but that is easy to fix step one will make all these true and false values disappear from the cells select everything press ctrl 1 to format use custom and type the code semicolon semicolon semicolon this is how you make things disappear in excel step 2 select all these columns and make them narrow enough already our garn chart is looking great step three let's go and fill up the person names and the percentage done look up the activity in the activities table and get the owner if there is no owner found because the activity is blank then we will simply print empty space let's do the same for percentage done now let's go ahead and add the visual indication of how much percentage of activity is done select this entire range go to conditional formatting data bars and apply a green color bar because green indicates progress let us quickly adjust it so only the bar information is shown make this column narrow and in the next column just get the previous value select this column and apply an icon rule of the tick mark the default rule doesn't work so edit it and change the rule so that the green tick is displayed whenever the value is greater than 1 and we will print no cell icon for other rules so it will show me a tick mark along with the percentage finally make the percentage disappear with that three semicolon rick and make this one also narrow our garn chart is nearly ready we have got two things left to do number one highlighting the current date and number two applying some formatting so that it looks pretty as well select this entire range including the dates on the top and apply a new rule to highlight the current date this rule will be equal to the cell in the row number three is equal to today notice the style of referencing here we are always locking it to row number three so that it will refer to the values here and apply a format condition to highlight the current name it will highlight that in a vertical column like this go back to conditional formatting and select the rule that is highlighting the today's date and push it down so that we will only see this if there is no activity happening if it's an activity going on we will see the activity color there so we'll get something like this let's apply some formatting here okay we finished formatting our garn chart now let's make it interactive so i can slice it by an individual theme because all of these numbers come from pivot table if i add a slicer on my pivot it will also apply on this data into our pivot table and right click on the team add it as a slicer let's cut this slicer and move it to our gantt chart worksheet now if i click on my slicer it will automatically show me only the activities that are done by that team just go down and check if there is any garbage appearing as you could see when i slice some of these dates appear as zeros so again we can apply our formula logic here to prevent that so that is how you create the interactive grant chart wendy can't wait to use that awesome gantt chart what about you what additional features would you add to this gantt chart tell me in the comment section in part two of the series we will create beautiful project progress charts if you want another idea for a gantt chart check out the video linked on the screen now wendy and chandu out [Music]

Channel: Chandoo

Views: 101,784

Keywords: chandoo, chandoo.org, Excel, excel dashboard tutorial, Project Management Dashboard with Excel, How to make a PMO Dashboard using Excel?, Excel dashboard tutorial 2021, interactive dashboard in excel, gantt chart for project management, gantt chart excel, gantt chart excel tutorial, two level gantt chart with excel, Project progress dashboard, project status dashboard with excel, dashboard in excel, excel dashboard, gantt chart tutorial

Id: FXnyKU6xZeI

Length: 11min 27sec (687 seconds)

Published: Wed May 12 2021