How to Create a Maintenance Dashboard in Google Sheets

Tracking equipment maintenance can easily become a chaotic mess of sticky notes, forgotten calendar alerts, and messy spreadsheets. A centralized maintenance dashboard turns that chaos into clarity, giving you a live look at what needs attention, what’s overdue, and how your team is performing. This guide will walk you through building a powerful, automated maintenance dashboard right inside Google Sheets, no complex software required.

Why Google Sheets is Perfect for Your Maintenance Dashboard

Before jumping into spreadsheets and formulas, let's talk about why Google Sheets is such a great fit for this task. While dedicated maintenance management software exists, it's often expensive and overly complex for many teams. Google Sheets, on the other hand, offers a practical, powerful, and accessible alternative for several reasons:

It’s Free and Accessible: Anyone with a Google account can use it on any device with an internet connection. There's zero cost to get started.
Collaboration is Built-In: Your whole team can view or update the dashboard in real-time. A technician can mark a task complete from their phone, and you’ll see the update instantly at your desk.
It's Infinitely Customizable: You're not locked into a rigid system. You can build the exact dashboard you need, tracking the specific metrics that matter to your operation, whether you're managing a vehicle fleet, office equipment, or manufacturing machinery.
Automation is Simple: With a few basic formulas, you can automate status updates, due date calculations, and notifications, saving you countless hours of manual data entry.

In short, Google Sheets provides all the core functionality you need without the hefty price tag or steep learning curve of specialized software.

Step 1: Build Your Maintenance Log

Every great dashboard starts with well-structured data. Your maintenance log is the foundation of your entire system. This is where you’ll list every task, assignment, and detail. Create a new Google Sheet and name the first tab "Maintenance Log."

Set up the following columns for your log:

Task ID: A unique code for each task (e.g., M-001, M-002). This helps in tracking individual jobs.
Task Description: A clear, concise description of the work (e.g., "Change oil and filter," "Inspect fire extinguishers").
Asset/Equipment: The specific item the maintenance applies to (e.g., "Ford Transit Van," "Rooftop HVAC Unit #3").
Location: Where the asset is located (e.g., "Warehouse B," "Server Room").
Assigned To: The person or team responsible for the task.
Maintenance Type: The category of work. Common types include Preventive, Corrective, or Inspection.
Frequency: How often the task should be performed (e.g., Weekly, Monthly, Quarterly, Annually, Ad-Hoc).
Last Completed Date: The last date the task was successfully completed. This will be updated manually.
Next Due Date: This will be automatically calculated based on the frequency and last completed date.
Status: The current state of the task (e.g., Pending, In Progress, Completed, Overdue).
Notes: A catch-all column for any extra details, like parts used or observations.

Using Data Validation for Cleaner Data

To avoid typos and keep your data consistent, use dropdown menus for columns like Maintenance Type, Frequency, and Status. This makes data entry faster and your formulas more reliable.

Create a new tab called "Lists." In this tab, create your lists. For example, in column A, list your statuses: Pending, In Progress, Completed, Overdue.
Go back to your "Maintenance Log" tab and select the entire Status column (column J).
Navigate to Data > Data validation.
In the criteria field, select "List from a range."
Click the grid icon and select the range of statuses from your "Lists" tab (e.g., 'Lists'!A2:A5).
Click "Save." Now, every cell in the Status column will have a neat dropdown menu.

Repeat this process for the Maintenance Type and Frequency columns. Your core data structure is now complete and robust.

Step 2: Automate Due Dates and Task Statuses

This is where the magic happens. Instead of manually calculating dates and checking for overdue tasks, you can use formulas to let Google Sheets do the heavy lifting. This turns your static log into a dynamic tracking system.

Calculating the "Next Due Date" Automatically

In the "Next Due Date" column (column I), you’ll use a formula that adds the correct amount of time to the "Last Completed Date" based on the specified "Frequency."

Click on cell I2 and enter the following formula:

=IF(H2<>"", SWITCH(G2, "Weekly", H2+7, "Monthly", EDATE(H2,1), "Quarterly", EDATE(H2,3), "Annually", EDATE(H2,12)), "")

Let's break this down:

IF(H2<>"", ..., ""): Checks if the "Last Completed Date" (H2) is not empty. If it is, the formula does nothing. If not, it proceeds.
SWITCH(G2, ...): Looks at the "Frequency" in G2 and returns different calculations for each:
- "Weekly", H2+7: Adds 7 days for weekly.
- "Monthly", EDATE(H2,1): Adds one month.
- "Quarterly", EDATE(H2,3): Adds three months.
- "Annually", EDATE(H2,12): Adds one year.

Once you enter the formula in I2, click the small blue square in the bottom-right corner of the cell and drag down to apply it to all rows.

Automatically Updating the "Status" Column

You can also automate the Status column to flag tasks as "Overdue" when their due date has passed. While you've already created a dropdown for statuses, this formula can act as a safety net. For a fully automated approach, use this in a new column (say, column K, "Calculated Status"):

=IF(J2="Completed", "Completed", IF(AND(I2<>"", I2<TODAY()), "Overdue", J2))

Here's how it works:

IF(J2="Completed", "Completed", ...): Keeps "Completed" if manually set.
IF(AND(I2<>"", I2<TODAY()), "Overdue", J2): If not completed and the "Next Due Date" (I2) has passed, marks as "Overdue", otherwise, keeps the current status.

Step 3: Create Your Dashboard Tab

Now for the fun part: building the visual dashboard. Create a new tab named "Dashboard." This sheet will pull summarized information from your "Maintenance Log" and display it in an easy-to-digest format.

Your dashboard should answer key questions at a glance:

What is the overall status of all maintenance tasks?
How many tasks are overdue and need immediate attention?
Which tasks are coming up soon?
Is the workload distributed evenly across assets or team members?

To do this, add KPI scorecards, charts, and dynamic lists of critical tasks.

Step 4: Add Formulas and Charts to Your Dashboard

This is where you bring your data to life with simple formulas and visualizations.

Creating KPI Scorecards

Set up a small area on your dashboard for key metrics:

Total Tasks: =COUNTA('Maintenance Log'!B2:B)
Completed Tasks: =COUNTIF('Maintenance Log'!J2:J, "Completed")
Overdue Tasks: =COUNTIF('Maintenance Log'!K2:K, "Overdue")

Format these cells with a large, bold font to stand out.

Visualizing Task Status with a Pie Chart

Create a small summary table with headers "Status" and "Count."
List statuses: Pending, In Progress, Completed, Overdue.
Next to each, use formulas like =COUNTIF('Maintenance Log'!J2:J, "Pending") and so on.
Select this summary data.
Insert > Chart. Change chart type to Pie chart if needed.
Customize colors and labels for clarity.

Listing Upcoming and Overdue Tasks

Use the QUERY function to generate dynamic lists.

Overdue tasks:

=QUERY('Maintenance Log'!A:I, "SELECT B, C, F, I WHERE K = 'Overdue'", 1)

Tasks due in the next 7 days:

=QUERY('Maintenance Log'!A:K, "SELECT B, C, F, I WHERE I >= today() AND I <= today()+7 and J <> 'Completed' ORDER BY I ASC", 1)

Step 5: Enhance Your Dashboard for Better Usability

Adding Conditional Formatting

Highlight overdue tasks:

Go to "Maintenance Log" tab.
Select all data.
Format > Conditional formatting.
Custom formula is: =$K2="Overdue"
Set fill color (light red).
Apply.

Locking Cells to Prevent Errors

Protect your formulas:

Right-click the "Dashboard" tab, select "Protect sheet."
Set permissions or ranges to prevent accidental edits.

Keep it Clean and Simple

Remove gridlines: View > Show > Uncheck Gridlines.
Use consistent colors.
Label charts and tables clearly.
Keep the layout clean for quick understanding.

Final Thoughts

Building a maintenance dashboard in Google Sheets transforms a simple spreadsheet into a powerful, automated management tool. With your data log and formulas in place, you gain a real-time, shareable overview of your maintenance schedule—helping prevent failures and keep operations running smoothly.

While creating these trackers is highly useful, integrating data from other apps can be complex. To simplify, we built Graphed to connect all your data sources automatically. You can build real-time dashboards and reports by asking in plain English, turning hours of manual work into seconds.