Menu
How to create a fully interactive Project Dashboard with Excel – Tutorial
In this article & video series, learn how to create a fully interactive Project Dashboard with Excel, as demonstrated on the right.
You will learn:
Part 1 - Project Gantt Chart
Gantt chart is a classic way to visualize a project’s plan & current status. That is why it forms the corner stone of any Project Management Dashboard.
In Part 1 of this tutorial, let’s create an interactive, multi-level gantt chart using Excel. Here is a demo of what we shall create.
Resources for the Gantt Chart
Excel files for practice
Use the below files to practice the concepts.
Gantt Chart - Video Tutorial
I made a fun & detailed video on how to create this Gantt chart with Excel. Watch it below or on my YouTube channel.
Getting the data
Any project is a combination of people & tasks. So in order to create a project plan gantt chart, you need both people & project activity data. Here is the data for our Project Mega Something.
Project Plan Data - Preview
Activities Table
People Table
Set relationship between tables
Once we have the data ready, connect people & activity tables. You can create a relationship from Data ribbon > Relationships.
We want to connect Activities Owner column with People Person column.
Tip
No need to use X or VLOOKUPs anymore, you can connect tables on a column with Excel.
Calculations Worksheet
As our tables are related, we can now calculate all the necessary numbers needed for our gantt chart.
In a new worksheet,
- Insert a pivot table (Excel will create the pivot from your table relationship data model)
- Add Category & activity to row label area
- Add start date and end date to values area
- Set start date “summarize values by” to Min
- For end date, set it to Max
- Set up pivot table in outline format and add sub-totals on top.
- Remove any grand totals.
Your final pivot table should be like below.
Specify Gantt Start date
In a blank cell in this new worksheet, define starting date for our gantt chart visual. You can use a formula like =TODAY()-14 or something else for this.
Name this start date cell as start.date
Gantt Chart Worksheet
Add a new worksheet and name it Gantt Chart.
In this sheet, set up your gantt chart grid. We will use 67 columns in this fashion.
- Category name
- Activity
- Person assigned to the task
- Start
- End
- % done
- % done
- 8 to 67 – Dates (narrow columns)
Load up values by either linking Pivot Table values a references or using lookup functions.
Tip
You can use XLOOKUP function to get the person & % done values.
Gantt Chart Formulas
We need to set up a formula in our gantt chart grid to show TRUE when a date in the top row is between start and end dates.
You can use AND formula for this. Fill this formula for the entire range.
In my spreadsheet I had 105 rows x 60 columns.
=AND($F7<=J$3,$G7>=J$3)
Conditional Formatting Rules
Our gantt chart is nearly ready. We need to add two conditional formatting rules to highlight the project dates & current date.
- Rule #1 : Highlight all TRUE values in the gantt grid
- Rule #2 : Highlight the column that corresponds to TODAY()
Examine the rules from below screenshot.
Finally select the AND formula range and apply custom number format of ;;;
Tip
You can use hide values in a range of cells by applying the custom format code ;;;
At this stage, your gantt chart should look like below:
To add % done data bars:
- Select % done column and add data bars.
- Set the bar rule to show bar only
- In the 7th column (which has % done value duplicated), apply a cell icon of
when the % done is 100% and no cell icon for rest. - Hide the contents of this column with custom code ;;;
Tip
Learn a few more tricks about conditional formatting here.
Team selection slicer feature
Go back to the calculations sheet and add a team slicer on your pivot table.
Cut and move this slicer to the gantt chart page.
Your gantt chart is now interactive!!!
Tip
Excel slicers offer a great deal of interactivity in your reports. If you have not used them before, I suggest learning a bit about them. Use this handy guide.
The final gantt chart...
Here is the final gantt chart at this stage.
Excel files for downloading
Use the below files to practice the concepts.
More Gantt Charts for you...
Part 2 - Beautiful Progress Charts
In Part 2 of this tutorial, we will build beautiful project progress charts with Excel.
There are 3 common ways to visualize a project progress.
- Traffic lights
- Thermometer
- Gauges
In this section, I will explain how to make a traffic light chart with Excel, as demoed on the right. In the video tutorial, you can learn how to make all 3 charts.
Resources for Project Progress Charts
Excel files for practice
Use the below files to practice the concepts.
Video Tutorial - Project Progress Charts
I made a video explaining how to make all three project progress charts. Watch it below or on my YouTube channel.
Project Traffic Light - Instructions
This will be the shortest part of the tutorial. Imagine you have the project progress value in the cell L3.
Use Conditional formatting on the cell to fill Red color if the value is under 50%, Amber color if the value is under 80% and GREEN else.
See below illustration to understand the rules setup.
Turning this cell to Traffic Light 🚦
Now that L3 can change colors, let’s make it a pretty traffic light.
First, make the % value in L3 disappear with custom cell format code of ;;;
Tip
You can use hide values in a range of cells by applying the custom format code ;;;
Next, copy L3 and paste it as Picture Link. This will give you a live snapshot of the traffic light cell.
As we now have a picture, we can apply picture format options to turn this rectangle to a circle shaped traffic light.
- Make the picture larger
- From Picture Format ribbon, crop > aspect ratio > 1:1
- You will get a perfect square
- Use crop > crop to shape > oval
- You will get a circle. If any of the edges look straight, just adjust your crop settings.
- Your traffic light is ready.
See this animated GIF to understand the process.
Finally, add a box behind the traffic light picture to frame it. Apply some shadow effect on the traffic light to get a nice look.
Group everything so you can move or position the traffic light where you need it.
The finished project traffic light
Here is how my finished project traffic light looks.
In the download file, you can find this and two other charts (thermometer and donut progress chart). Refer to my video above for instructions on that other two.
More Project Status Charts for you
Part 3 - The Dashboard
So far we have created an interactive Gantt Chart and a traffic light progress chart. In the final part of this tutorial, we will fill missing gaps and complete the dashboard.
You will learn,
- Colors, fonts & icons used in the dashboard design
- Making a dynamic upcoming activities / issues table
- Setting up dashboard tiles
- Adding gantt chart snapshot
- Creating resource loading information
- Finalized project dashboard
Resources for Part 3
Excel files for practice
Use the below files to practice the concepts.
Video Tutorial - Part 3 of PM Dashboard
I created a detailed video tutorial explaining how the dashboard is constructed. Watch it below or from my Youtube Channel.
Colors, Icons & Fonts for the Dashboard
It is a good idea to decide the color scheme and visual design of your dashboard before you get too far. In our case, I used below.
Colors:
- Excel default color scheme +
- #FF555A & #638EC6 for data bars in resource loading chart
Fonts:
- Open Sans Extra Bold for headings
- Calibri for everything else.
Icons: You can add FREE icons from Insert > Icons in Excel 365.
Create Dashboard Sheet
Next, we will create a new worksheet, name it dashboard. Create a blank layout like below.
We will fill this up with various charts & tables.
Dynamic Activities / Issues Table
In this part of the tutorial, we will create an interactive Activities / Issues table, as shown below.
Step 1: Issue Tracker Table
We already have activities table. Time to add issues data to our project file.
This table is already available in the blank data file. Just add it to data model by setting up a relationship between Issue[Onwer] and People[Person].
Step 2: Set up a form control
In the dashboard sheet, add a combo-box form control (Developer Ribbon > Form Controls) and set it to show one of these two values –
- Activities
- Issues
Link this control to a cell in calculations worksheet (J8).
Tip
Form controls are great for making your charts & dashboards interactive. You can find them in the Developer ribbon of Excel.
Step 3: Calculating Upcoming 10 Activities & Issues
To calculate upcoming 10 activities / issues, we need this logic:
- Activities: start date is after today()
- Issues: close date of the issue is blank
We need to limit both lists to first 10 items.
We can use FILTER() formula to get such lists.
'note: $I$6 has TODAY() date
10 activities
=INDEX(FILTER(activities,activities[Start Date]>$I$6),SEQUENCE(10),{1,2,4})
10 issues
=IFERROR(INDEX(SORT(FILTER(issues, issues[Date Closed]=""),4,1),SEQUENCE(10),{1,2,4}),"")
Step 4: Picking one of the lists based on form control input
We can use IF formula to select either of these lists based on what user picked in the form control. Since J8 cell in calculations tells us what the choice is, we can use that to write our IF formula.
Once we have the results, just copy the values and paste them as reference in dashboard. Our dynamic activities / issues table will be ready.
Dashboard KPI Tiles
Next we will create the dashboard tiles.
Start by placing 4 rounded rectangles with fill color #404040 on the dashboard worksheet.
Next, copy paste the traffic light (part 2 of this tutorial) on first tile.
Project KPI calculations
We need to calculate 3 numbers for the remaining tiles on the dashboard. They are,
Days remaining
Number of days left to the project completion. This is the difference between last project activity End date and today.
=MAX(activities[End Date])-TODAY()
On-going Activities
This is the number of activities that have already started and yet to reach completion.
=COUNTIFS(activities[Start Date],"<="&TODAY(), activities[% Done],"<1")
Pending Issues
How many issues are yet to be closed.
=COUNTIFS(issues[Date Closed],"")
Adding KPI values to the dashboard tiles
On the dashboard, add three text boxes, one per each of the above three numbers. Using formula bar, link the text box value to the calculation worksheet values.
Adjust formatting of the text box. Also, add the icons to signify each KPI tile.
At this stage, our dashboard tiles will look like this:
Bring in the Gantt Chart
We already created a fully interactive gantt chart in part 1 of this tutorial. Just head over to the gantt chart tab on the dashboard, copy a big enough range, go to the dashboard and paste it as linked picture.
This will give you a live snapshot of the gantt chart in the dashboard tab.
Adjust the size of the gantt chart picture and add any necessary border around it.
Cut & move the team slicer to the dashboard tab.
The Resource Loading Chart
In the calculations worksheet, create a pivot table to show number of tasks & activities assigned to each person.
Copy the pivot table data, go to dashboard and paste it as link in the correct place.
Adjust formatting and apply databars on issue & activity columns.
Customize the data bar colors as per our color scheme.
And our dashboard is DONE!
The completed dashboard
Feel free to download the full dashboard workbook from here.
Also, check out the video playlist to learn how I constructed this. The videos will give a ton of useful Excel tips as a bonus.
More on Project Management...
Project Management is one of my passions and I talk about it extensively on my site & Youtube.
Please visit below pages to learn more about other cool PM stuff you can do with Excel.
Ready to use Project Management Templates
Create awesome project management dashboards and reports using my templates. Trusted by 20,000+ project managers all over the world, these templates are designed to make you look awesome. Create Gantt charts, timelines, time sheets, issue trackers, risk trackers, project dashboards and portfolio dashboards using my templates.
Click here to get them today.
Got something to say?
Get FREE Excel & Power-BI Newsletter
One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.
Online Classes
© All rights reserved.
18 Responses to “How to create a fully interactive Project Dashboard with Excel – Tutorial”
I'm not getting PowerBI pay dates since long . Please advice.
Want to handle multiple projects which are going at multiple sites. Some activities are common and some are unique. HOW to Handle the same in one sheet at one place where multiple stakeholder can update the progress
Thank you! I appreciate all you do.
Chandoo
How do you get subtotals on top in a tabular pivot, I can only add to the bottom if I select tabular, on top if I use compact?
Cheers
Martin
Sorry for the confusion. I am also using OUTLINE layout.
Hi Chandoo,
We have a attendance for civil labour at site. we need to maintain and give payment at the end of every week. the problem is, it the wages varies from Mason to Helper-1 , Helper-2 etc. Also we used to change the site as per the need. Now the task is we need to calculate their wages as per their presence and also need to site wise work detail. Can you help me in this regard.
Note: If required I can share the file.
Regards,
Sethumadhavan
Hi Sethumadhavan
If not resolved till date, share me the file so that I may be of any use to your issue.
Danny
Excellent and easy! Congrats
great thanks a lot 🙂
Thanks for the excel dashboard how-to pointers! Dashboard is indeed something important to learn if you want to visualize your data in excel
I recently attended an Excel training program here https://www.ted.com.my/it-training/ms-excel-2013-2016-basic-intermediate/. I didn't expect that you can actually create project management info using Excel or a spreadsheet. Good info!
Dear Chandoo,
Great dashboard!
I would like to add a dynamic progress S-curve for planned and actual progress from Gantt chart. Could you help with it?
Awesome Bro..
Dear Chandoo
I have been following you since 2011.
THANK YOU for hour amazing work!!!
Thank you so much for your sharing. It's useful a lot for me.
hi,
it gives me in porcentage this error #NAME?
how ?
In the Step 3 Project Dashboard Prep sheet, workbook 'Calculations', the formula in H16 is not calculating. I have been unable to repair in my copy. Do you have any suggestions, please?
If you are getting a CALC error it means there are no upcoming activities as per your data. Either change the data (add some future activities) or change the cell I6 to an older date.