Every business, no matter how big or small, has to deal with a certain level of operational issues. More often than not, those operational issues are a direct consequence of malfunctioning or broken physical assets and infrastructure.

Be it a leaking HVAC, broken bulldozer, or a conveyor belt issue that stopped the production line, the fastest approach to solving asset management problems is by using an efficient maintenance ticketing system.

Now, for a ticketing system to be considered “efficient”, it has to be digital. In an ideal scenario, a business would invest in a CMMS or fleet management software as both solutions have ticketing systems as one of their core features.

Unfortunately, a paid solution is not an option for everyone at this moment. For those organizations, we created a free maintenance ticketing system based on Google Sheets.

In continuation of this article, we will outline how we developed our templates for those that want to create their own.

For everyone else, feel free to just download our ticketing guide below. It contains the templates you need and detailed instructions on how to use them.

The life cycle of a maintenance ticket

The life journey of a standard maintenance ticket goes something like this:

1. Equipment (mis)use results in an equipment malfunction.
2. Someone reports the problem by submitting a maintenance ticket/request.
3. The maintenance manager/planner/supervisor reviews the ticket and assigns it to a maintenance technician.
4. The maintenance technician comes to diagnose and correct the issue.
5. When the asset/item has been fixed, the ticket is closed and the maintenance requester has been notified that the issue has been corrected (if needed).

You can see the same process outlined in the image below.

Creating a ticketing system in Microsoft Excel or Google Sheets for such a simple process sounds easier than it is. This is because things don’t always go according to plan. Some tickets are declined, some work has to be delayed, some other unique situation might arise. The ticketing system has to be simple to use, but still offer a way to manage those deviations from the standard process. Above anything, the ticketing system has to be a useful tool for everyone involved, not an additional burden.

Why did we use Google Sheets for our templates?

We ended up using Google Sheets for the following reasons:

• It’s free. The only thing you need to edit a document is a free Gmail account.
• It’s easy to use. It doesn’t take much training to learn how to select a field and write some text in it.
• It’s cloud-based. Anyone with the link can access the templates and see the changes in real-time. This is key for team collaboration.
• It’s editable. Different organizations have different workflows. Maintenance managers and supervisors can add/remove certain fields and columns based on their needs.
• It has useful formulas. We can automate certain actions and even build simple reports by using available formulas.
• It allows you to give different levels of access. Some workers should only be able to view the documents while others should be able to both view and edit. Google Sheets allows you to give different levels of access to different people.

These templates are not a replacement for a CMMS system. However, we are aware that not all organizations are ready to adopt CMMS software but are nonetheless in dire need to better organize their maintenance activities.

This type of barebone maintenance ticketing system can be a temporary solution for:

• smaller maintenance teams (up to 10 or so people)
• maintenance teams that do not have any kind of ticketing system in place
• maintenance teams that still rely on sticky notes, chalkboard, or smartboards
• maintenance teams that want to be more organized but do not yet have the capabilities to implement a modern CMMS

Requirements for implementing a digital maintenance ticketing system

Regardless of the simplicity of this maintenance ticketing system, you can’t just spring it upon the employees and say that “starting today, you need to use this system”. That’s a recipe for disaster.

Switching from chalkboard and sticky notes to a digital system has to be closely followed by appropriate workflow changes. And we all know how resistant to change a maintenance department can be.

To make this transition as smooth as possible, here are a few things you should have in place before you implement a digital maintenance ticketing system.

Getting the necessary buy-ins

You should search for approval and commitment from three different parties:

1. top management
2. the maintenance team
3. maintenance requesters

It is unwise to make bigger changes in any department without the blessing from top management. Usually, that is because you want to ensure appropriate funding for the project at hand. While we are talking about a free maintenance ticketing system here, you might need to invest in mobile devices or a wifi network to make full use of it. Additionally, workflow changes will cause an adjustment period which is bound to result in some loss of productivity in the short-term. So, it makes sense to make your plans clear to the people in charge of the maintenance budget.

One concern with implementing any kind of maintenance software is that maintenance techs are never going to fully adopt it. If you have technicians that are not using or are misusing the solution, you might as well go back to sticky notes. To ensure everyone gets on board, discuss the implementation a few weeks in advance. Show which problems you are trying to eliminate and why this is the way to do it. Lastly, set new workflows in collaboration with the technicians to ensure they make sense in practice and won’t create any bottlenecks.

The last party you should get the buy-in from are the people who report maintenance problems and create maintenance tickets. Instead of picking up the phone or sending an email, they will now have access to a Google Sheet which they can use to report their maintenance problem and track the progress of their work request. For this to work, they should actually use the prepared template.

Basic understanding of Google Sheets (advanced if you’re creating templates from scratch)

To use the templates we provide with this article, you only need to have a very basic understanding of Google Sheets/Microsoft Excel. Selecting a field to write a comment is the only action a technician or maintenance requester needs to know. This should be easy to explain even to someone who has never seen a spreadsheet in their life.

As a maintenance manager or facilities manager that will manage the templates, it is helpful to have more experience with creating advanced formulas and calculations, especially if you want to heavily edit provided templates or create your own from scratch.

Mobile devices

The advantage of digital maintenance tickets is that they can be accessed anytime and from anywhere, as long as you have a device that is connected to the internet. To make full use of the new system, it is advisable to equip workers with mobile devices. This way, they will be able to close work orders, leave completion notes, and plan their next maintenance activity without the need to constantly go back to that one computer terminal available on the plant floor.

If you ever switch to a CMMS, the benefits of having mobile maintenance software will become even more exaggerated.

The basic functionality you need (even in a free maintenance ticketing system)

For a maintenance ticketing system to work properly, you need to have a straightforward and efficient way to receive and manage tickets. Ideally, you should also be able to generate a few basic reports.

The templates we created were guided by these notions. If you opt-in for our free templates which you can find at the beginning of this article, you will get two Google Sheets. One is designed for receiving tickets, and the other one is used to manage work orders and generate reports.

Here is a sneak peek into how they work.

Receiving maintenance tickets template

You need one place to hold all of the maintenance requests that come in. This way, it is unlikely you’ll lose any and it will be easier to notice duplicated maintenance requests.

We decided to have a separate sheet for submitting maintenance requests so you can share the link to this document with everyone who might need to submit a maintenance ticket.

Here is how the “Submit ticket” template looks like in use:

You have all of the standard info like date, problem description, and contact info. We also added a column that informs the maintenance requester about the status of their ticket. We think it is a neat addition, but you can delete that column – if the maintenance planner/supervisor/manager can’t keep it up-to-date, there isn’t much sense in having it at all.

Managing maintenance tickets template

Managing maintenance tickets can be done in a few different ways. We designed the templates so they can be used to schedule all maintenance operations, not just address incoming maintenance service requests. In that sense, this aims to be more of a work order management system than just a maintenance ticketing system.

In the template, we included the following fields:

• Ticket ID: Serves as a unique identifier (ID) for each ticket. It auto-fills after you enter “name of the task”.
• Name of the task: Give each ticket a name for an easier overview of assigned work.
• Work description: A place to write a description of the work that needs to be performed.
• Technician: A drop-down list you can use to select a technician you want to assign the work to.
• Due date: Set a due date by which the assigned ticket/Work Order should be completed.
• Priority level: Choose a priority level for the ticket. It helps technicians decide which work needs to be done first.
• Status of the ticket: A drop-down list where you can select the current status of the ticket.
• Completion notes: Here technicians and/or supervisors can leave any important notes regarding the work that was just completed.
• Completion date: Marks the completion date for the assigned work order. Auto-fills after the status of the ticket is changed to “Completed”.
• Type of work: Choose which type of work (reactive or preventive) is to be performed. It is used in the reporting section.
• Completed in time?: This field auto-fills after checking if the ticket was completed on time or not. It will show as “Yes” or “No” after the ticket is marked as completed. We had to implement this field to be able to calculate the Work Compliance percentage in the reporting section.

We wanted to automate the process as much as possible. This is why we incorporated a few different drop-down lists and created formulas to auto-fill certain fields. If you plan to develop your own templates, you should look to do the same. After all, the end goal is to make the maintenance department as efficient as possible.

Here’s how the template looks like in practice (this is all in one row but we had to split it into 2 screenshots to make them readable):

Generating maintenance reports

With these kinds of templates, reporting will be limited. We were able to create three custom reports based on the data used in the table:

• Breakdown of task priority: A simple report that shows how urgent are the maintenance tasks you assign on average. It’s nothing special but some might find it useful.

• Breakdown of type of work: A report that shows how many of your tasks were planned (part of routine or preventive maintenance) and how many were unplanned (reactive). It also shows how many total tasks you have performed so far.

• Total work compliance percentage: Overall work compliance shows the percentage of tasks that were completed on time.

There might a way to generate more useful maintenance reports. However, that usually means expanding the ticket management template with additional fields. We believe the average user will not have the need for that. However, a great thing about these templates is that you can adjust them according to your needs.

CMMS is the ultimate maintenance ticketing software

We sincerely hope that some of you will be able to put this free maintenance ticketing system to good use. That being said, spreadsheets can only get you so far. They are a great step forward, but can’t be a long-term solution. Eventually, you will need to switch to a mobile CMMS if you wish to maximize operational efficiency, reduce maintenance costs, and hit your maintenance KPIs.

For those that feel ready to make that switch sooner rather than later, we invite you to play around with Limble CMMS and explore its features (without any commitment). To do that, here are your options:

• SCHEDULE A DEMO: Contact us and someone on our team will give you a walkthrough and get you familiar with all of the major features.
• TRY OUR SELF DEMO: Clicking on this button will refresh your browser tab and load a simulated test environment where you check how Limble works at your own pace.
• START A FREE TRIAL: You can sign-up for a free 30-day trial to evaluate if Limble CMMS is a good fit for your organization.

Do you have any questions about Limble or about the templates we presented in this article? Have some suggestions on how they could be improved? Do not hesitate to start a discussion in the comment section below!