Search

# 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.

### 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.

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.

### 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.

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

How to hide values with number formats.

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

### 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.

Use the below files to practice the concepts.

## 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.

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

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.

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.

## 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
• Finalized project dashboard

### 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 +

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.

Â

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

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.

### 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.

Got something to say?

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

1. Shweta Bandarkar says:

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

• Chandoo says:

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,

• SA Daniel says:

If not resolved till date, share me the file so that I may be of any use to your issue.

Danny

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

9. Med says:

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!

10. Tom says:

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?

11. ArunKumar Subramanian says:

Awesome Bro..

12. Ines says:

Dear Chandoo
I have been following you since 2011.
THANK YOU for hour amazing work!!!

13. Benjawan says:

Thank you so much for your sharing. It's useful a lot for me.

14. salim says:

hi,
it gives me in porcentage this error #NAME?
how ?

15. Courtney says:

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?

• Chandoo says:

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.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.Â