Dynamic Dashboard Linking

1. The Foundation: A Structured Workbook

Before we start linking, let's reinforce a crucial concept for building robust and maintainable dashboards: the three-sheet structure. Keeping your data, calculations, and presentation separate is a professional best practice that makes your work easy to update and troubleshoot.

To understand this structure, please review the following short reading.

To summarize, your Incident_Tracker.xlsx file should be organized like this:

1. Tracker Sheet (Raw Data): Contains your main Excel Table with all the raw incident data. You should never build charts directly from this sheet.
2. Calculations Sheet (Chart Data): A dedicated sheet where you will use formulas like SUMIFS, COUNTIFS, or PivotTables to aggregate the raw data into summary tables. This sheet acts as the engine for your dashboard.
3. Dashboard Sheet (Presentation): This is the final, user-facing sheet. It should only contain your charts, KPI cards, and slicers. Every element on this sheet will be linked to the Calculations sheet.

2. Displaying KPIs with Linked Text Boxes

Key Performance Indicators (KPIs) are the high-level numbers that demand immediate attention (e.g., "Total Open Incidents"). The best way to display these on a dashboard is with Text Boxes, which we can link directly to cells on our Calculations sheet.

Let's walk through the process.

Step 1: Calculate the KPI on your Calculations sheet.
For example, to calculate "Total Open Incidents," you would find a blank cell on your Calculations sheet and enter a formula like:
=COUNTIFS(Incident_Table[Status], "<>Closed")
(Assuming your data table is named Incident_Table)

Step 2: Insert and Link the Text Box on your Dashboard sheet.
This process is simple but very powerful.

The video below demonstrates this simple linking process.

Pro Tip: Combining Text and Numbers

Sometimes you want more context than just a number. Instead of just 42, you want to display "Open Incidents: 42". You can achieve this by creating the full text string in a cell on your Calculations sheet and linking your text box to that cell.

The best way to do this is with the TEXT function, which allows you to format numbers correctly within a text string.

For example:
="Open Incidents: " & TEXT(C5, "0")

Here, C5 is the cell with the calculated number of open incidents.

The article from Chandoo.org also reinforces this technique for creating dynamic KPI tiles.

3. Linking Charts to a Dynamic Data Source

The principle for charts is the same as for KPIs: charts on the dashboard should get their data from summary tables on the Calculations sheet. This keeps your dashboard fast and easy to manage.

For example, if you want a pie chart showing "Incidents by Status", you would first create a small summary table on your Calculations sheet like this:

The 'Count' column would contain COUNTIFS formulas. Then, you would:

1. Select this small table on the Calculations sheet.
2. Go to Insert > Charts and create your pie chart.
3. Cut the chart (Ctrl+X) from the Calculations sheet and Paste it (Ctrl+V) onto your Dashboard sheet.

Now, whenever the source data in your main tracker is updated, the COUNTIFS formulas will recalculate, the summary table will update, and your chart will change automatically.

This next video is a masterclass in this technique. It shows how to create a "data preparation table" that dynamically responds to a user's selection from a dropdown list, and then links a chart to it.

4. Your Turn: Start Building Your Dashboard

It's time to put theory into practice. Open your Incident_Tracker.xlsx file, which should already contain your Tracker sheet and the wireframe you planned on a new Dashboard sheet.

1. Create a Calculations Sheet: Add a new worksheet and name it Calculations.
2. Calculate Your KPIs: On the Calculations sheet, create the formulas for the four KPIs you identified in your wireframe (e.g., Total Incidents, Open Incidents, Average Resolution Time, "High" Severity Incidents). Place each calculation in a separate cell.
3. Create and Link KPI Cards:
   • On your Dashboard sheet, insert four Text Boxes in the locations you designated for KPIs.
   • For each text box, select it, go to the formula bar, and link it to the corresponding KPI cell on your Calculations sheet.
   • Format the text boxes (font size, alignment, color) to look like professional KPI cards.
4. Prepare Chart Data: On your Calculations sheet, create a small summary table for a chart (e.g., "Incidents by Status"). It should have two columns: "Status" and "Count". Use COUNTIFS to populate the "Count" column.
5. Create and Link Your First Chart:
   • Select your new summary table.
   • Insert a Pie or Bar chart.
   • Cut the chart and paste it into its designated spot on your Dashboard sheet.

Take your time with these steps. This is the foundational work that makes a dashboard truly dynamic.

Conclusion

Congratulations! You've taken the first and most important step in building a functional dashboard. You've bridged the gap between raw data and visual presentation, creating elements that will update automatically as your data changes.

Key Takeaways:

• The Three-Sheet Structure is Non-Negotiable: Always separate your Raw Data, Calculations, and Dashboard sheets for a scalable and maintainable report.
• Link, Don't Re-calculate: The dashboard is for presentation only. All visuals should be linked to pre-calculated values and summary tables on the Calculations sheet.
• Text Boxes for KPIs: Use the formula bar (=Sheet!Cell) to link text boxes to your key metrics, creating dynamic KPI cards.
• Charts Feed from Summary Tables: Build charts from small, dedicated summary tables on your Calculations sheet, not from the raw data table.

You've now created the static structure and linked it to dynamic data. In our next lesson, we will make it interactive. You will learn how to connect a single Slicer to multiple PivotTables and PivotCharts to control the entire dashboard, allowing your users to filter all the visuals at once.