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.

• Blank data file
• Completed Gantt Chart file

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)
```
```

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

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.

Excel slicers – complete guide.

The final gantt chart…

Here is the final gantt chart at this stage.

Use the below files to practice the concepts.

• Blank data file
• Completed Gantt Chart file

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.