Building an Incident Dashboard with Excel

1. Planning and Setting Up Your Dashboard

A good dashboard is well-planned. The goal is to present the most important information clearly, without overwhelming the user. A common best practice is to place high-level KPIs at the top, key charts in the main body, and interactive controls (like filters) to the side.

Let's avoid some common design mistakes from the start.

Now, let's set up the canvas:

1. Create a new sheet in your workbook and name it Dashboard.
2. Go to the View tab and uncheck Gridlines and Headings. This immediately gives the sheet a cleaner, more report-like appearance.
3. You can optionally set a background color for the entire sheet (e.g., a light grey or dark blue) by selecting all cells (Ctrl+A) and using the Fill Color tool on the Home tab.

2. Creating Key Performance Indicator (KPI) Tiles

The KPIs on your Summary sheet (Total Incidents, Open Incidents, etc.) need to be displayed prominently. We'll create "KPI tiles" at the top of our dashboard using shapes and linked text boxes.

The video below demonstrates this technique clearly. You will add a shape for the background and then place a text box on top, which you will dynamically link to a cell in your Summary sheet.

Your Steps:

1. On the Dashboard sheet, go to Insert > Shapes and choose a shape (like a Rounded Rectangle). Draw one for your first KPI, "Total Incidents". Format its color as you like.
2. Go to Insert > Text Box and draw it on top of the shape.
3. With the text box selected, click in the Formula Bar, type =, navigate to your Summary sheet, and click the cell containing the value for "Total Incidents". Press Enter.
4. Format the text (font size, color, alignment) to make it stand out.
5. Add another text box for the label (e.g., "Total Incidents").
6. Group the shape and text boxes together (select them all, right-click > Group).
7. Duplicate this group for your other main KPIs: Open Incidents, Resolved Incidents, and Average Resolution Time. Remember to update the formula link for each new text box.

3. Building Interactive Charts with PivotCharts

While our Summary sheet is great for high-level numbers, we need more detailed, filterable visuals. The best way to do this is with PivotCharts. A PivotChart is a chart linked to a PivotTable, which allows it to be controlled by Slicers.

We'll create a few PivotTables from our main IncidentLog table to generate charts for:

• Incidents by Status
• Incidents by Priority
• Incidents by Owner

The following video from Kevin Stratvert is an excellent, comprehensive guide to this entire process. We will break it down into manageable steps.

Your Steps:

1. Create a new sheet and name it DashboardCalcs. This will keep your workbook tidy by holding the PivotTables that power your dashboard.
2. Chart 1: Incidents by Status
   • Click anywhere inside your IncidentLog table.
   • Go to Insert > PivotChart.
   • In the dialog box, choose to place the PivotTable on your DashboardCalcs sheet.
   • In the PivotTable Fields pane, drag Status to the Rows (or Axis) area and IncidentID to the Values area. Ensure it's set to "Count of IncidentID".
   • Choose a chart type (a Pie or Donut chart works well here).
   • Clean up the chart: Add a title "Incidents by Status" and hide the field buttons (Right-click a button > "Hide all field buttons on chart").
3. Chart 2 & 3: By Priority and By Owner
   • Repeat the process above to create two more PivotCharts on your DashboardCalcs sheet:
     • Incidents by Priority: Use Priority in Rows and Count of IncidentID in Values. A Bar chart is a good choice.
     • Incidents by Owner: Use Owner in Rows and Count of IncidentID in Values. A Bar chart is also suitable here.
4. Move Charts to the Dashboard:
   • Select each chart one by one, cut it (Ctrl+X), and paste it (Ctrl+V) onto your Dashboard sheet. Arrange them below your KPI tiles.

4. Making the Dashboard Interactive with Slicers

This is where the magic happens. Slicers are user-friendly filter buttons that can control multiple PivotCharts at once.

Your Steps:

1. Click on any of your PivotCharts on the Dashboard.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. In the dialog box, check the boxes for Priority, Status, and Owner. Click OK.
4. Arrange the three new Slicer objects on your dashboard, perhaps on the left or right side.
5. For each slicer:
   • Right-click the slicer and choose Report Connections.
   • In the pop-up window, you will see all the PivotTables you created. Check all the boxes to connect the slicer to every chart.
   • Click OK.

Now, test it out! Click on a priority, status, or owner in a slicer. All of your charts should update instantly.

Your Turn: Assemble the Final Dashboard

You now have all the components. It's time to put them together into a polished, one-page report.

1. Open your Incident Management Excel file which contains the IncidentLog and Summary sheets.
2. Create the Dashboard and DashboardCalcs sheets. Set up the Dashboard sheet by removing gridlines.
3. Build the KPI Tiles: Create at least four tiles at the top of your Dashboard for Total, Open, and Resolved Incidents, and Average Resolution Time. Link them to your Summary sheet.
4. Create the PivotCharts: On your DashboardCalcs sheet, create three PivotCharts based on your IncidentLog data:
   • Incidents by Status (Pie or Donut chart).
   • Incidents by Priority (Bar chart).
   • Incidents by Owner (Bar chart).
5. Move and Arrange: Cut and paste your charts onto the Dashboard sheet. Arrange them neatly below the KPI tiles. Use the alignment tools (Shape Format > Align) to ensure they are perfectly lined up.
6. Add and Connect Slicers: Insert slicers for Priority, Status, and Owner. Position them on your dashboard and use Report Connections to link each slicer to all three of your PivotTables.
7. Final Polish: Adjust colors, fonts, and sizes to create a visually appealing and professional-looking dashboard. Ensure everything fits on one screen without scrolling.

Conclusion

Congratulations! You have successfully constructed a fully interactive, one-page dashboard. You've combined several powerful Excel features—Tables, COUNTIFS, PivotTables, PivotCharts, and Slicers—to build a professional-grade reporting tool. This ability to transform raw data into actionable, visual insights is a core skill for any MIS Executive.

In our final lesson for this capstone project, we will focus on using this system for ongoing reporting. You will learn how to prepare a weekly incident summary report by refreshing the data and extracting key insights, putting your new dashboard to practical use.