Create a Custom Project Dashboard with a Gantt Chart

Microsoft 365 and Office
1

I'm looking to create a complex Gantt chart-style dashboard to track a 4-year project with 10 commissioning events (milestones).

I have a single data sheet to track deliverable items, with columns for item, stage, discipline, importance level, status, and required date tied to each commissioning event.

I want to create a separate sheet for the Gantt chart-style dashboard.

The project involves 135 deliverable items per commissioning event, each with a single date ranging from 130 days before to 130 days after the event, including 10 items required on the event date, which I want to highlight.

I'd like to create a dashboard with buttons or a slicer to pull data from 7 other sheets, showcasing different project stages.

There are two levels of requirements: main requirements for commissioning and stage completion/closeout. The deliverable items are categorized into 7 disciplines for system updates.

I want to display a go/no-go traffic light system, showing green if 95% of level 1 items are delivered and red if 5% are late.

I also want to display the number of type 1 deliverables sent and the number of late items.

Additionally, I want a bar chart with 4 status inputs: pending, complete, not applicable, and late, broken down by discipline.

Can someone guide me on how to build this dashboard and what formulas I'll need for each input?

Thanks

2

This is quite an ambitious dashboard you’re looking to build. It sounds like you have a solid idea of what you’re aiming for, so let’s break it down step by step. This will take a few steps, so bear with me.

Step 1: Data Organization

First, ensure your data sheet is structured to be easy to work with. You mentioned columns for item, stage, discipline, importance level, status, and required date. An extra folder with 10 sheets for each commissioning event might help in keeping your data organized. You can also create a separate sheet to hold the Gantt chart and dashboard elements.

Step 2: Gantt Chart Generation

For the Gantt chart, Microsoft Excel has a couple of options:

  1. Bar Chart: You can create a bar chart with item names on the x-axis and dates on the y-axis. You can use the start and end dates to set the width of each bar.
  2. Timeline Chart Add-in: If you have the Microsoft Office 365 ProPlus or Microsoft 365 ProPlus licenses, you can use the Timeline Chart add-in to create a Gantt-style chart.
  3. Third-Party Add-ons: Consider third-party add-ons like SmartArt, Lucidchart, or Gantt Excel, which offer more advanced Gantt chart capabilities.

Step 3: Logic and Formulas

For the traffic light system, you can use conditional formatting and formulas. Here’s a rough outline:

  1. Count Functions: Use COUNTIF and COUNTIFS to count the number of delivered items and late items across the different stages. Apply these counts to your go/no-go traffic light system.
  2. Conditional Formatting: Format the traffic light cells to green if the count of delivered items is 95% and to red if the count of late items is 5%.

For the bar chart with status inputs, you can use the following:

  1. COUNTIFS: Use COUNTIFS to count items by status (pending, complete, not applicable, and late) and by discipline.
  2. Chart Wizard: Pull the counts into a table and then use the Chart Wizard to create a bar chart.

Step 4: Buttons or Slicers

To pull data from the 7 other sheets, you can use either buttons or slicers. For buttons:

  1. Hyperlinks: Use hyperlinks to navigate to the respective sheets with the required data.
  2. Filter Buttons: Create buttons linked to filters that change the view of your data to show the desired stage or discipline.

For slicers:

  1. Insert Slicers: Add slicers to your dashboard sheet. Link these slicers to your data to filter the information displayed based on selected stages or disciplines.

Step 5: Integration and Customization

  1. Consolidate Data: Use Power Query (available in Excel 2010 and up) to pull data from the 7 other sheets into a single data source. This will simplify your data analysis and reduce complexity.
  2. Format and Size: Adjust the layout of your dashboard to be easy to read and navigate. Use appropriate coloring, fonts, and spacing to visually distinguish different components.
  3. Conditional Formatting: Apply conditional formatting to highlight important items like the 10 items required on the event date.

Final Touches

  1. Testing: Rigorously test all components to ensure the data pulls correctly and the visuals update as expected.
  2. Iterate and Refine: Based on user feedback and practical use, refine the dashboard and make adjustments to enhance its usability.

That’s a basic roadmap for your complex dashboard. It’s a significant undertaking, but with this guide, you should be well on your way to building a functional, informative, and visually appealing Gantt chart dashboard.