Interactive Excel Project Management Dashboard - FREE Download

hi I'm Mindy Tracy from my online training hub I'll be taking you through how to use Excel to build this interactive project management dashboard that you see behind me at the top the dashboard header gives a high-level overview of tasks progress and budget conditional formatting gives a visual indication of the progress of each task over time and it's color-coded to reflect tasks not started in progress and completed the slices at the top allow us to filter tasks for specific projects and managers and as we make selections in the slices you'll notice the headline charts also updates now the scroll bar up here allows us to scroll horizontally through the dates we can move one day at a time or clicking in the middle of the scroll bar will jump a week at a time before we get started I just want to set your expectations for this tutorial first of all it's going to be at a fast pace you won't have time to follow along step-by-step the first time you watch it there's a link in the video description where you can download the workbook and data and of course you can re-watch the video as many times as you like pausing and rewinding as required now I'll be using office 365 and one of the new dynamic array functions but there are alternate formulas available for earlier versions of Excel so what you see here can be done in all versions of Excel from 2010 onward okay let's get started building the dashboard from scratch here I have a new file sheet one is where I'll build the dashboard now I want to make it clear that this is just a regular Excel worksheet I've put some formatting and text in the header just to save me some time but otherwise it's just a regular sheet you don't need an add-in or anything fancy to build this dashboard so now you know that I'm going to rename this sheet dashboard now my project data is on the datasheet you'll notice that it's formatted in a tabular layout with one column for each field and a single row for each task now I want to point out the formula in column F so let's just view the formula bar this calculates the end date and it uses the workday International function and that allows me to ignore weekends if we look at the syntax we can see that it takes the start date which is the project start date then I've taken one day off because we want to include from the beginning of the start date in the project duration as opposed to from the end of the start date and then it adds a number of days which is the duration of the project and then the last document we've got here is for the weekend now this is a number based on a list so let's just bring up this list so you can see number one specifies that my weekend days are Saturday and Sunday but you can choose from different combinations and even just have one day off obviously if your project runs seven days a week then you don't need to use a workday international you can just take the start date plus the duration I need one for Saturday and Sunday now the last argument of workday International is to account for holidays so if you have holidays that you want to factor in that won't be worked on then you can refer to a list I'm going to leave it simple and just ignore weekends for this example so that's how my end date is calculated now the first thing we want to do is ensure the source data is formatted in an Excel table because this will help me build the dashboard quickly but also means that updating can be done with the click of a single button and the keyboard shortcut to format data in a table is ctrl T brings up the create table dialog box and my table has headers is checked which applies here so I'm going to click OK you can see it's now formatted in a table it's got the color scheme and the header row has the filter buttons we also have this contextual table design tab and you can see my table name is called Table one I'm going to leave it at that but it is a good idea to give your tables a proper name that's going to make it easy to reference when you build your dash so I'm now ready to insert my pivot table and we can do this directly from the table design tab summarized with pivot table so let's go ahead and do that this pimp table is going to be the main pivot table on my dashboard so I'm going to choose an existing worksheet which is my dashboard and I'm going to pop it in there and click OK now the first thing I want to do is right click the pivot table and format the options to turn off auto fit column widths on update I've already set my column width so I don't want the pivot table messing with that as I build it now I'm just going to zoom out let's put it at 85 percent it's just going to give you a bit more view of the overall dashboard strictly speaking when building dashboards always build them at 100% and make your charts and fonts smaller rather than zooming in and out it's just going to render those fonts and charts more clearly whereas when you use the zoom the images can become distorted okay so this pivot table has all of the fields so in the project the tasks of the manager I want the start date and then the end date now I'm just going to drag this field list out so we've got a bit more room I also want the day's completed and the duration but because these are numeric fields it will want to put them in the values area so I'm going to drag them down into the row labels just to make sure they go where I want and likewise the progress that's also a numeric field the budget and the actual can go into the values area now I'll just dock the field list again let's format this pivot table I want it to be in a tabular format so on the design tab we're going to go report layout show in tabular form and we'll turn off all of the subtotals I also don't want any of these expand and collapse buttons so on the analyse tab I'm going to turn the buttons off now the pivot table is automatically grouped my dates so I'm going to right-click and ungroup that's the start date and this is the end date and now we have the data with any grouping of my dates you can see the actual individual tasks I've got a bit more formatting to do I don't want these labels to say sum of budget and sum of actual so I'm just going to type over them now I can't use budget because it's already in the field list so I'm going to trick it and just add a space to the end and we'll do the same for actual let's also write align those so that they're aligned the same as the numbers speaking of the numbers let's format them so I'm going to right click value field settings number formats here I want thousand separator and no decimal places now I recommend you always format your pivot table value fields using the value field settings number format rather than the number formatting on the Home tab because that way the number formatting will remain with that field it won't matter what cells those fields are in it will always have the number formatting whereas if you use the number formatting up here and your pivot table changes shape or grows the number formatting may not remain with the field so always use the value field settings alright next I want this pivot table to look a bit more in keeping with my theme which is going to be Gray's so let's set the style to this gray color we'll also remove the gridlines so that our dashboard looks a bit cleaner okay let's take a look at the dashboard to see what's next we've got these charts up here so let's go ahead and we'll start creating the analysis for those charts so on my workings tab I've got some labels here ready to go what I want to do here is count the number of tasks that haven't started are in progress and are completed so we can use counter if for this not started now I could reference the data but because my dashboard will be at times filtered based on the slicer selections I want my count to reflect the data in the pivot table so I'm going to count the progress of the pivot table tasks and we'll just extend down to row 50 to allow for some growth obviously you can extend your if further than that if you think you're going to have more growth so the criteria is where the progress equals zero the project hasn't started so close parentheses so there are four projects that haven't started let's count those that are in progress now this time I need count FS because I need multiple criteria again we're referencing the progress field of the pivot table and we're going to extend it past the end to allow for growth so the first criteria is where the count is not equal to zero because obviously we want to count projects that are in progress and again will reference the progress is less than one if it's one then the project must be finished it's 100% so this is our projects that are in progress where the count is not zero and less than one lastly we want to count how many projects are completed so this is again just a simple count if there's only one criteria and that is where the progress is 100% so where it equals one so now we have our break down between not started in progress and completed we can see there are 37 projects remaining that is the not started plus in progress and 40 tasks in total the completed plus the remaining now we want to insert a bar chart for these not started in progress and completed so on the insert tab I'm going to go into recommended charts and then all charts this way I can choose bar I want the stacked bar this is the default but this is the one I actually want and this is the only place I can choose it so I'll click OK and now we'll just do a little bit of formatting for this chart let's go ahead and we'll turn off the axes we're going to use labels instead we don't need gridlines and let's put the legend at the top next I want to control one to open the format pane and actually I want to format the bar itself so we're going to the series options here I want the gap width to be zero but now I want to change the size you'll see that it just enables the bar to fill up the space of the chart rather than having lots of white space so and it 3.73 by nine and we'll left align the legend and the chart title let's give it a title now I'm going to paste this chart into my dashboard so I'm going to ctrl X to cut it and then let's go back up or paste it in ctrl V to paste now I need to format it because I don't want the fill in there so on the format pain we're going to change the shape fill to no fill and the shape outline to no outline let's make this font white so that it stands out on the gray background right let's take a look at what the next chart is we want this donut chart here that calculates the percentage of days completed versus the days remaining so I'll go ahead and create that on the workings tab I'm going to insert a new pivot table here we'll use the data again that's insert a pivot table we're going to pop it on the workings tab just here and I'll click OK so this one looks at the sum of the days completed versus the sum of the total duration and I want my values in the row labels now I need to reference this pivot table because I can't insert donut chart purely from this because it will sum these figures and that's not what I want so let's just create some label stays completed and days remaining and it's simply a case of referencing the pivot table you'll notice it puts in the get pivot data function so when define percentage days completed are of the total duration and then we'll just calculate the balance which is 100% - the days completed let's format them in centage amyl insert a donut chart now if you know me you know I'm not a big fan of donut charts but everyone seems to love them so I'm going to go with the flow in this particular example so let's go ahead and format it we don't want a chart title the legend is actually going to be the title so put it at the top and let's resize it because it's enormous and it's not really conveying a lot of information so we're going to make it three point seven three by three point two five now we have to faff about with the donut to control one to open the formatting pane and I want to make the hole size 60% and I need to add a label that shows the percentage of the day's completed now I could use data labels but in my experience they don't stay put when you only want one and I want it to remain in the center of the donut hole so with the chart selected I'm going to insert a text box I'm just going to draw it over the top and then select the outside of the text box in the formula bar type equals click the cell containing the day's complete a percentage and press Enter so now I can format it a little bit we'll change the font color to this a little more subtle gray and we can make the font a bit bigger let's just make that box bigger so that we can see the whole percentage so now I've completed that shot let's format it with no fill and no outline or ctrl X and we'll paste it in here now I need to format this with white font and I need to move it up slightly so that it sits correctly over the grey area and then of course all the fiddling happens because the sizes don't remain the way that you want it will make the donut a little bigger as well okay so there's my first painful donut chart what's next let's take a look what I haven't done is format my overall task progress bar to show the different fill colors and in fact my whole dashboard is not using this color scheme so let's spend a few minutes doing that up on the page Layout tab in the colours I've got a custom color scheme for project management so I'll select that and it automatically updates everything so I haven't had to do a lot of work but I'm still going to have to customize these colors so ctrl one to open the format data series on the paint bucket I want this not started category to have no fill and I want the border to be a solid pink outline now it's quite a thin line so I'm going to increase it to 1.5 this one here is correct already but now that I've put a border around they're not started it doesn't look so good so let's add a border to this as well in the same color but we'll give it 1.5 as well and we'll do the same for this one this one's going to have a solid fill in this green color but it also needs a border and needs to be 1.5 that way they're all the same size along the length of the bar I'm going to go ahead and give this font white color just so it stands out a bit better on the pink you can do the same for this green and we'll leave the 4 as is otherwise you won't be able to read it now I'm just going to bring this down a little bit okay those two charts are looking pretty good let's take a look at what's next so we need to create some pivot tables to calculate the percentage of budget that's spent and to show us the total budget versus the actual so let's go ahead and do that again from the data tab I'm going to insert a pivot table on the working sheet now if you know me you'll know I recommend that you always put pivot tables on their own sheet just so they don't overlap each other accidentally when you're building and updating your dashboards and sheeting a little here so bear with me but you really should abide by that rule and only put one pivot table per sheet all right here I need to know what the actual is and the budget now I want to format these fields to millions so I'm going to right click value field settings number format and here I want a custom number format so I want 0.0 and then to round to millions you put in two commas and I obviously need to classify it as millions so let's type in m4 millions after the backslash that's the positive value semicolon the negative value will be the same except it has a minus sign and we'll leave it at that and click OK and ok again I need to do the same for the budget let's go in but now that I've created the custom number format we'll find it at the very end and I can just simply select it from the list and click OK so now we have the actual and the budget I need to change these labels and add a space to the end now this is going to be from my bar chart so I might just add a column in here and let's give it a label bar chart and I need to also create a donut chart for the percentage so it's going to calculate the percentage of actual over the budget and then the balance is one minus the actual percentage so we'll format these as a percentage sign all right I'll insert the donut chart first now this one has got a title but no legend and we'll change the size so that it fits on our dashboard it's three point seven three by three point two five again we need to change the donut hole size to 60% and we'll give the chart a title budget spent and always see the fonts a bit big so let's make it a bit smaller and make it white while we're here next with the chart selected I'm going to insert a text box to house a percentage so draw that on select the outside of the text box in the formula bar equals and then the percentage let's format this a little bit make it a bit bigger and we'll give it a lighter gray all right now I'm going to cut this out and then we'll make the donut bigger size that control X to cut control V to paste it in if you've got to take out the fill and the shape outline all right so I'll move this label up a little bit and now we can make the donut a bit bigger so I'm just left clicking and dragging the pull handles okay that's maybe a bit big let's do it a little smaller all right so we want it to sort of stay in keeping with the size of this one so they're roughly the same size I won't waste too much time because you get the idea and you can make those changes yourself the next chart I need is the bar chart that looks at the budget and actual so back on the workings this one is a pivot chart so with a pivot table selected I can go on to the pivot table tab and insert pivot chart on a bar chart and I'll click OK all right first thing I want to do is right-click and hide all the fill buttons then we're going to get rid of the axes we'll give the chart a title I want data labels no gridlines and no legend because I'm going to select my data labels and then we'll go in and we'll format them so that they show the series name and let me just get rid of that so you can see so I've added the series name I'll get rid of leader lines just in case and I want the series name and the value on their own line so I'm going to select new line we need to rinse and repeat for the actual okay now I need to separate these bars a little so my series overlap is going to be minus 30 and my gap width is going to be 0 makes it look enormous so let's resize the chart remember it's three point seven three high and this one is six wide we'll fix the chart title I'll bring it over to the left so we'll get rid of the fella in the outline and control X and they'll paste it into the dashboard we need to change that font color to white I can bring it over a bit so there our headline charts completed what we want to do now is insert some interactivity in the form of slices so with my pivot table selectors on the pivot table analyze tab I can insert slicer or you can do it via the insert tab I want slices for the projects and the managers so there's my two slices let's bring them up here we'll give them five columns and let me resize them I'll bring this on up beside let's just align them to the top okay so they're my slices but right now they're only connected to this pivot table I need to also connect them to this one and this one so all I need to do is right-click the slicer go into report connections and select the other pivot tables from the list we'll do the same for this one now when I select a project you can see that it feeds through to the other charts as well likewise if I select managers you can see they're all updating now something I want to do is format the number style for this bar charts at control one what I want to do is if the number is zero I don't want to show it so I'm going to create a custom number format in fact I've already got one there that I've used before so I want one digit for the positive minus the digit for the negative and the zero format is left blank which means it will be hidden so I'll apply that and I need to do it one at a time and we'll choose the same one for each of the labels okay so now if I choose a manager that doesn't have for example in this case Ladd hasn't got any completed projects you're not seeing a zero for the label on the completed segment of the bar all right now I can actually move these over a little bit we can see them all in one screen only just okay the next thing I want to do is give my user a little bit of information about the date range that my dashboard is currently looking at so I'm going to clear all the slices so that my dashboard is showing all of the data and then we're going to insert a text function here that finds the minimum date from the start date and I'm going to extend it down to allow for growth now the text function wants to know how that date should be formatted so I'll just type in my date format it doesn't have to be in capitals it's just I've got a caps lock on so we'll do date month year and then I want to join the start date to some text that says two with a space either side and then we're going to find the end date so we're going to convert it to text and find the maximum of the end date again allowing for growth formatted the same so day month yeah if you're in US then obviously you'll change that formatting to month day year and and I press ENTER we get the date range and if I select a different project you can see that the formula up here and adjusts accordingly let's just take a look at what's next in the dashboard you can see here we're missing all of the information about the task progress and we need to add in these conditional formatting bars so up to the conditional formatting stage but remember when I click on the scroll bar the dates here adjust so I need to insert the scroll bar so let's go ahead do that now on the Developer tab you'll find insert and then form controls and this is the scroll bar if you don't have the Developer tab right-click any of the tabs go customize ribbon and you can check the box for developer then you'll have the Developer tab and be able to insert a scroll bar you can see my mouse has changed to the cross and I can just left-click and drag to draw it in now I need to assign this scroll bar to an output cell I'm going to right click format the control here I can specify the current value I'm going to put in one just so you can see what happens when you link it to a cell the minimum value will be zero that's fine the maximum value I'm going to go with 30 that's 30 days incremental change one day at a time when you click the ends of the scroll bar and page change will be seven one week when you click inside the scroll bar and I'm going to link it to a cell on my working sheet so click OK now if we take a look at that cell you can see the cell link is returning one so let's give this a label so we know what this field represents if I go back to the dashboard and deselect the scroll bar now when I click on it it moves accordingly and if we look at the workings now it's at position zero the next thing I need to do is insert a list of dates in this row now I'm going to use the sequence function for this this is a new function available in office 365 if you don't have office 365 then there are alternatives and I do cover those in my Excel dashboard course so I want to automatically create a sequence of dates I only want them in one row so the first argument is 1 how many columns of dates well I'm going to grow out 26 columns that's nearly 4 weeks that will do the start well what's the earliest date I need to use the min function to find the earliest dates in my list here and allowing for growth but remember we've got this scrollbar in here so we want to take account of the scroll position I'm going to add the scroll bar position and then the next argument is simply this step so here I want the step just to be one day at a time so close parenthesis and you can see it's added in the dates they're not will format it correctly so let me go ahead and do that we use a custom number format day day month month month and click OK all right let's also format them so they're all centered in that row so then my dates now if you keep an eye on them as I click in the scroll bar the dates are updating that's because it's adding the value from the scroll bar all right we're ready to do the conditional formatting so first let me add some data bars to the progress and that will just give us a nice visual indicator of the progress rather than having to read the percentages so conditional formatting data bars I will just do this on a solid fill now don't particularly like the color so I'm going to go in and manage the rule double click to open the editing dialog or I can select this color which is in keeping with my theme and we'll click ok so that's quick and easy and as this changes these data buds will automatically adjust now I need to continue this formatting across my column in fact I haven't centered those so let's do that as well and we'll use conditional formatting to do that that way if this grows then the conditional formatting will adjust as well rather than hard keying the formatting if you like so go to conditional formatting new rule here I need to use a formula to determine which cells to format and the formula is really simple you just want to check whether this cell here and I need to change the absolute reference so that it's only fixed on the row and not the column is not equal to blank so give it this gray fill and I just want to distinguish each day's column area so we're just going to use white and we'll give it a border vertical border and that will just give it a more defined look the other thing we need to do is put in a freeze pane so on the View tab just going to freeze row five and that way when we scroll down all of the data in our header remains in place we're ready to apply the conditional formatting for our tasks so the first thing I want to do is select all the cells that I want the formatting applied to and we'll go out to column AJ I've allowed for growth going down to row 50 but obviously you can allow for more growth if you need so here again Home tab conditional formatting new rule it's a formula based rule so here I want to let me move this across here highlight which days in this calendar our weekend dates so we're going to use the weekday function for this but I need multiple criteria so I need the and function and then weekday so weekday returns the day number based on a calendar that you specify similar to the workday international function so I need to select the first date and then just absolute the row and the calendar that I want to use is the second calendar that specifies my weekends are Saturday and Sunday so all I need to do is test that the weekday number is greater than five when you specify two as the weekday type it numbers Monday as one through to Sunday at 7:00 so Saturdays six and Sunday is seven therefore any days greater than five other weekend dates the other criteria I want to check is that I actually have a task on this row because as we scroll down rows 47 through 50 have no tasks but I don't want the weekend format to flow through to those days unless there's actually a task so I'm going to absolute just the column and check that it's not blank so we'll close the and and let's go ahead and format them with a pattern that just hashes out those dates I'll click OK and you can see my weekend and now formatted the next formatting is for the completed days so again conditional formatting new rule based on a formula again equals and so here I want to test which days have tasks that are in progress and color them with a green fill so we need to check whether the date and I just want to absolute the row is greater than or equal to the start date and I want to absolute just the column and I need to check that this date is greater than or equal to the current day is completed so we're going to use workday international like I did to calculate the end date of the project taking the start date absolute the column plus the day's completed absolute the column using the date type 1 which specifies Saturday and Sundays as my weekend's minus 1 because we need to include the start date in our range and if I don't minus 1 from this calculation it will calculate from the start date not inclusive of it so we'll check that it's greater than or equal to the current date absolute just the wrote close parentheses on my end now I can go ahead and apply the format so I want this green fill color and I'm going to apply a white border top and bottom just to give a bit more definition to the fill and I'll click OK so now we have our completed days notice that it's sitting behind the weekend dates that's okay we're going to fix that in a little while the next conditional format we need to apply is for the in progress so let me just select all the self again and I'll scroll back up to the top alright conditional formatting new rule based on a formula again you'll notice that they also use a similar formula so we need multiple criteria again I need and and I'm checking whether the date in the first cell absolute just the row is greater than or equal to work day international taking the start date absolutely column plus the day's completed absolute the column based on type one and that the progress absolute the column is not equal to one because it's not complete it's in progress and the date absolutely the row is less than or equal to the end date absolute the column now I'm just using F for two absolute those fields close my parentheses and let's go ahead and format it again we'll use a white top and bottom border and the fill color this time is pink I click OK and ok again we've got our completed in green out in progress in pink and we need these not started fields conditionally formatted next so a new rule using formula equals and so need to check that the date absolute the row is greater than or equal to work day international taking the start date absolutely column plus the day's completed will salute the column based on type one and progress absolute the column is equal to zero we're looking for projects that aren't started and the current date absolute the row is less than or equal to the end date absolute column close parentheses and then we just format it no fill but we want the pink border click OK and ok again now we can't see it and that's because the order of our conditional formatting isn't correct so I'm going to go into the conditional formatting and manage the rules so here I need to bring the weekend rule to the top and click stop of true and when we do this you'll notice that these Barr's go away so click apply now I need to also set this one as stop as true and that way it will be applied before this fill color and now you can see the formatting for the not started is applied and then this one is never evaluated because it stops if it's true I'll click OK and that's our conditional formatting almost done one thing I'd like to do is continue on this gray fill across the grand total row but because that grand total row can change depending on which project you have selected I need to use conditional formatting for that as well so again I'm going to go down and select the fields that I want formatted and then conditional formatting new rule based on a formula here I want to check whether this row this cell here absolute the column only contains the text grants total and here I want to apply the formatting this is going to have this gray fill and we'll give it a top border I think it's this darker gray here click OK and ok again and you can see now it's formatted with the grey fill and if I change my filter you can see that that formatting moves with the bottom of the pivot table one thing I forgot to do was just abbreviate this label so that it fits in more easily of course with dashboards like this the data has been updated all the time and by formatting the source data in an Excel table you're making that job super easy for example let's say I have some new tasks to add to project failure where you can see at the moment there are only five tasks all I need to do is add that data to the bottom of my table or I can insert new rows I've got some data copied to my clipboard so I'm just going to ctrl V to paste it in you can see the formula has copied down likewise for the progress now if I go back to the dashboard all I need to do is go to the data tab and click on the Refresh all button and you'll see not only will the pivot table update but so all of these charts click refresh all and now my two new tasks have been added and this also applies to any changes you make to existing tasks progress so you can see updating your dashboards when you build them with Excel tables and pivot tables is super fast now I know I ran through that quite quickly but remember you can download the file at the link in the video description you'll find it contains step-by-step instructions with screenshots as well as more resources including links to tutorials on the functions and conditional formatting used to build this dashboard and as a bonus you'll find some tips on dashboard protection now if you like this video please give it the thumbs up and subscribe to my channel and click the Bell icon to be notified when I post my next video and please share it with your friends and co-workers thanks for watching

Channel: MyOnlineTrainingHub

Views: 1,140,861

Rating: 4.9705162 out of 5

Keywords: excel project management dashboard, project management dashboard, excel dashboard

Id: 5qtSioTE2wY

Length: 40min 4sec (2404 seconds)

Published: Tue Mar 24 2020