fbpx

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:

project management dashboard - finalized

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.

gif - gantt chart demo

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. 

Download the blank data file.

Project Plan Data - Preview

data - activities

Activities Table

data - people

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.

table relationships - project gantt chart

Tip

No need to use X or VLOOKUPs anymore, you can connect tables on a column with Excel.

Learn more about Table Relationships in 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.

pivot table to support gantt chart calculations

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.

gantt start date

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.

gantt chart set up - explanation

  1. Category name
  2. Activity
  3. Person assigned to the task
  4. Start
  5. End
  6. % done 
  7. % 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.

All about XLOOKUP function.

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.

grid formula for checking activity dates
				
					=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.

conditional formatting rules

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 ;;;

How to hide values with number formats.

At this stage, your gantt chart should look like below:

gantt-chart-after-addin-cf-rules

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 ;;;

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.

Excel slicers – complete guide.

The final gantt chart...

Here is the final gantt chart at this stage.

gif - gantt chart demo

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. 

Project Management - Traffic Light - DemoThere 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.

conditional formatting rules for traffic light

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 ;;;

How to hide values with number formats.

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.

  1. Make the picture larger
  2. From Picture Format ribbon, crop > aspect ratio > 1:1
  3. You will get a perfect square
  4. Use crop > crop to shape > oval
  5. You will get a circle. If any of the edges look straight, just adjust your crop settings.
  6. Your traffic light is ready.

See this animated GIF to understand the process.

creating a circle picture link for traffic light

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.

formatting traffic light picture link

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.

Project Management - Traffic Light - Demo

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-fonts-icons used in the project management dashboard

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.

Get free icons in Excel 365

Create Dashboard Sheet

Next, we will create a new worksheet, name it dashboard. Create a blank layout like below.

pm dashboard - canvas

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.

demo-interactive-activities-issues-table

Step 1: Issue Tracker Table

We already have activities table. Time to add issues data to our project file.  

project-issues-table

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 controlcombo box form control is perfect for showing choices in dashboards

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.

Learn more about Form Controls and how to use them.

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.

demo-interactive-activities-issues-table

Dashboard KPI Tiles

Next we will create the dashboard tiles.

Start by placing 4 rounded rectangles with fill color #404040 on the dashboard worksheet.

pm-dashboard-kpi-tiles-setup

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:

completed project dashboard kpi tiles

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.

pivot table with resource loading information

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

project management dashboard - finalized

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.

Project Portfolio Dashboard - Chandoo

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?

9 Responses to “How to create a fully interactive Project Dashboard with Excel – Tutorial”

  1. Shweta Bandarkar says:

    I'm not getting PowerBI pay dates since long . Please advice.

  2. Rajnikant Katira says:

    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

  3. Denise Z. says:

    Thank you! I appreciate all you do.

  4. Martin Lewis says:

    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

  5. Sethumadhavan V S says:

    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

  6. Thierry Santhi says:

    Excellent and easy! Congrats

  7. Tetonne says:

    great thanks a lot 🙂

  8. Daniel says:

    Thanks for the excel dashboard how-to pointers! Dashboard is indeed something important to learn if you want to visualize your data in excel

Leave a Reply