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:
- Gantt charts for project planning
- Progress charts for status visualization
- Upcoming tasks & issues
- Preparing final dashboard
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.
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.
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 gridRule #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 ;;;
TipYou 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 onlyIn 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 ;;;
TipLearn 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!!!
TipExcel 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...
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 Chandoo.org - Learn Excel, Power BI & Charting Online.
source https://chandoo.org/wp/interactive-project-dashboard-with-excel/
0 Comments