Graphed LogoGraphed
Login

How to Create a Maintenance Dashboard in Excel

Cody Schneider•

Tracking equipment maintenance, team schedules, and work orders using scattered spreadsheets and email chains can quickly become chaotic. A well-designed maintenance dashboard in Excel can centralize this information, turning a messy backlog into an organized, at-a-glance command center. This tutorial will walk you through, step by step, how to build your own interactive dashboard to track critical maintenance KPIs.

First, Why Build a Maintenance Dashboard in Excel?

Before we get into the nuts and bolts, it's helpful to understand the goal. A maintenance dashboard isn't just about creating pretty charts, it's about providing instant answers to important questions:

  • What is the current status of all open work orders?

  • Which pieces of equipment are breaking down most frequently?

  • How is my team's workload distributed?

  • Are we focusing more on planned preventive maintenance or reactive emergency repairs?

  • What are our maintenance-related costs over time?

Answering these questions quickly helps you move from a reactive "firefighting" mode to a proactive, data-informed strategy that reduces downtime and saves money.

Step 1: Gather and Structure Your Maintenance Data

A dashboard is only as good as the data feeding it. This step is the foundation of your entire project. If you mess this up, nothing else will work correctly.

What Data to Collect

Your goal is to create a single source of truth for all maintenance activities. Start a new Excel sheet named "Maintenance Log" or "Raw Data." Create columns for every piece of information you want to track. A robust log usually includes:

  • Work Order ID: A unique number or code for each task.

  • Request Date: When the maintenance task was opened.

  • Completion Date: When the task was closed.

  • Equipment Name/ID: The specific machine or asset being serviced.

  • Maintenance Type: Categorize tasks (e.g., Preventive, Reactive/Repair, Inspection).

  • Task Description: A brief note on the issue (e.g., "Replace fan belt," "Annual oil change").

  • Assigned Technician: Who is responsible for the work.

  • Status: The current state of the task (e.g., Open, In Progress, Completed, On Hold).

  • Priority: The urgency of the task (e.g., High, Medium, Low).

  • Hours Logged: The amount of time spent on the task.

  • Cost of Parts: The cost of any materials used for the repair.

The Most Important Step: Format as a Table

Once you have your columns and a few rows of sample data, you need to format it as an official Excel Table. This is non-negotiable for flexible dashboards.

  1. Click anywhere inside your data set.

  2. Go to the Insert tab on the ribbon and click Table.

  3. Ensure the "My table has headers" box is checked, and click OK.

Your data will now be formatted with colored bands. More importantly, this structure is now dynamic. When you add new maintenance logs to the bottom, the Table will automatically expand, and any charts or PivotTables connected to it will update instantly upon refresh - no more manually adjusting data ranges!

Pro Tip: Keep data entry clean and consistent. Use drop-down lists for columns like "Status," "Priority," and "Assigned Technician" to avoid typos and variations ("Complete" vs. "Completed"). You can do this using Data > Data Validation.

Step 2: Summarize Your Data with PivotTables

Your Raw Data sheet is great for logging information, but it’s terrible for analysis. PivotTables are Excel’s engine for summarizing thousands of rows of data into a concise table in seconds. We will use a separate sheet to house our PivotTable "calculations."

Create a new sheet and name it "Analytics Engine." We will create several PivotTables here to power our final dashboard.

Example PivotTable 1: Work Orders by Status

  1. Click on your data Table in the "Maintenance Log" sheet.

  2. Go to Insert > PivotTable.

  3. In the dialog box, make sure your Table name is selected and choose to place the PivotTable in an Existing Worksheet. Click the selector and choose cell A1 in your "Analytics Engine" sheet.

  4. The PivotTable Fields pane will appear. Drag and drop the fields:

    • Drag Status into the "Rows" area.

    • Drag a unique field like Work Order ID into the "Values" area. Make sure it reads "Count of Work Order ID." If it says "Sum," click on it and change it under "Value Field Settings."

You now have a simple summary table showing the total count of work orders for each status.

Repeat this process to create other essential summaries on the same sheet. Leave some empty columns and rows between each one:

  • Tasks per Technician: Rows: Assigned Technician, Values: Count of Work Order ID.

  • Maintenance by Type: Rows: Maintenance Type, Values: Count of Work Order ID.

  • Costs per Equipment: Rows: Equipment Name/ID. Values: Sum of Cost of Parts and Sum of Hours Logged.

Step 3: Visualize Your Data with PivotCharts

Now, let's turn these boring summary tables into clean, easy-to-read charts.

  1. Go to your "Analytics Engine" sheet and click inside your first PivotTable (Work Orders by Status).

  2. Go to the PivotTable Analyze tab on the ribbon and click PivotChart.

  3. Choose a chart that best represents the data. A Pie Chart or a Donut Chart is great for showing parts of a whole, so let's pick a Pie Chart. Click OK.

A chart will appear directly on your sheet. Repeat this for your other PivotTables:

  • Tasks per Technician: A Bar Chart works well to compare workloads.

  • Maintenance by Type: A Pie Chart or Doughnut Chart is effective here.

  • Costs per Equipment: A Column Chart is good for comparing costs across different machines.

Cleaning Up Your Charts

The default PivotCharts are often cluttered. Clean them up to make them more professional:

  • Right-click the gray field buttons on the chart (like "Sum of...") and select "Hide all field buttons on chart."

  • Click on the chart, then click the "+" icon that appears on the right to remove unnecessary elements like the Legend or Gridlines.

  • Give each chart a clear title (e.g., "Work Orders by Current Status").

Step 4: Create the Dashboard and Make it Interactive

This is where everything comes together into a single-view report. Create one more new sheet and name it "Dashboard." This is the only sheet your end-users will interact with.

Moving Your Charts

  1. Go to your "Analytics Engine" sheet.

  2. Click on one of your cleaned-up charts.

  3. Cut it (Ctrl+X or Cmd+X).

  4. Go to your "Dashboard" sheet and Paste it (Ctrl+V or Cmd+V).

  5. Repeat this for all your charts, arranging them neatly on the dashboard canvas. You can hold the 'Alt' key while moving or resizing charts to snap them perfectly to the grid.

Adding Slicers and a Timeline for Interactivity

Slicers are user-friendly filter buttons that make your dashboard feel like a professional application.

  1. Click on any chart on your dashboard to select it.

  2. Go to the PivotChart Analyze tab.

  3. Click Insert Slicer. A dialog box will pop up with all of your data fields. Check the boxes for the fields you want to filter by - good options are Assigned Technician, Maintenance Type, and Equipment Name/ID. Click OK.

  4. The slicers will appear on your sheet. Move and resize them to fit your layout.

  5. To filter by date, click on a chart again, go to PivotChart Analyze > Insert Timeline, and select your Request Date field. This creates an intuitive timeline filter.

Connecting Slicers to All Charts

By default, a slicer only controls the chart it was created from. The key to a great dashboard is connecting one slicer to all your charts.

  1. Right-click on your first slicer (e.g., Assigned Technician).

  2. Select Report Connections.

  3. You'll see a list of all the PivotTables in your workbook. Check the box for every single one of them. Click OK.

  4. Repeat this for every slicer on your dashboard.

Now, when you click a technician's name, every single chart will instantly update to show data only for that person. You have successfully built a fully interactive dashboard.

Final Thoughts

Building a maintenance dashboard in Excel provides a powerful lens into your operations, pulling everything together into one view. By structuring your raw data in an Excel Table and using PivotTables and Slicers, you can create a professional, interactive report that saves time and highlights opportunities for improvement. The key is consistent data entry and refreshing the data when new logs are added.

While this manual Excel workflow is a huge step up from scattered spreadsheets, it still requires regular effort to maintain. The process of pulling data, refreshing PivotTables, and ensuring everything is up-to-date can be time-consuming. This is precisely why we created Graphed. We connect directly to your data sources (even Google Sheets where you might export a system log to) and automate the entire process with live, real-time dashboards. Instead of clicking through menus to build charts, you just describe what you want to see - "Show me a pie chart of work orders by status for last month" - and we build it for you in seconds.