How to create a fully interactive Project Dashboard with Excel – Tutorial

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 - interactive & dynamic

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.

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

Download the blank data file.

Project Plan Data –


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


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

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.


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


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


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


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


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.

  • Blank data file
  • Completed Gantt Chart file

More Gantt Charts for you…

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.

Part 2 – Beautiful Progress Charts

In Part 2 of this tutorial, we will build beautiful project progress charts with Excel. Stay tuned.

Got something to say?

The post How to create a fully interactive Project Dashboard with Excel – Tutorial appeared first on – Learn Excel, Power BI & Charting Online.

Original source:

Leave a Reply

Close Menu