How to Create a Ticketing System with Microsoft Excel

What Is the Ticketing System?

A ticketing system is a platform or a software application used to open, track, and close trouble tickets. IT managers and customer service teams primarily use it to track and resolve requests. These requests can be in the form of complaints, suggestions, or questions from customers. The ticketing system allows customer support to prioritize and respond to these requests in an organized manner.

In this post, you'll learn about the benefits and importance of a ticketing system. We'll also look at the limitations attached to ticketing systems and how to build both a ticket tracker and submission form in Excel. And hopefully, you'll take away something that you can use to improve on customer satisfaction and efficiency in your current and future business(es).

Benefits of a Ticketing System

Managing customer support with a ticketing system or help desk has several benefits. Some of the benefits are as follows:

• Improve efficiency and organization: The customer support team will be able to track and manage requests in a centralized and organized manner. This simplifies the process for teams to respond to requests in an efficient and effective way.
• Improve customer satisfaction and relationship: By providing a more efficient and organized way of handling customer support requests, a ticketing system can help improve customer satisfaction and loyalty. This can ultimately lead to better outcomes and increased revenue.
• Enhance collaboration and communication: A ticketing system helps strengthen and improve collaboration and communication among customer support teams. For example, it will be easy for team members to discuss and share tickets, as well as assign tasks to each other.
• Track, analyze, and resolve customer suggestions: With a ticketing system, there are ways to analyze and resolve customer requests efficiently.
• Gain better visibility and reporting: IT managers can easily and quickly access real-time data reports on the status and resolution of customer support requests.

When Is It a Good Idea to Track Tickets in Excel?

Excel is not an out-of-the-box ticketing system. But with some help, you can adapt the spreadsheet tool to serve as a ticketing platform for your team. However, you will likely want to invest in a dedicated ticketing tool as your company grows and scales. Here are a few examples of when you might be able to use excel for tracking tickets:

• Excel might be acceptable for tracking tickets if your tickets are small in number and don't require collaborating on the resolution with a large number of people.
• Excel can be used for organizing and tracking information, and it allows sorting and filtering of data to view it in different ways.
• Using Excel helps you to keep track of your team's progress in resolving customer issues.

Limitations Attached to Tracking Tickets in Excel

There are challenges using Excel for tracking tickets. For instance, Excel can be time-consuming and error-prone. Additionally, it can be difficult to keep track of all the information related to a large number of tickets. Excel also lacks the functionality and features found in a dedicated ticketing system. In short, Excel is not a ticketing software.

How to Build a Ticketing Intake Form Using Excel 

Creating an issue intake form is crucial, as it allows customers to document and file their issue.  Included in the form will be standard information like the date, description of the problem, name, and email address.

To create an intake form in Excel, a new spreadsheet is opened with the required information inputted, as shown below.

To create a form in Excel similar to the one referenced above, locate the quick access toolbar at the top left corner of the Excel dashboard. Afterward, look for the Customize Quick Access Toolbar circled in the image below.

Click on the toolbar and look out for the More Commands option that will lead you to the page shown below.

Change Popular Commands to All Commands and search for Form, which will give you this:

The New button of the form is used to create a new request submission. To delete an already-logged request, use the Delete button. Find Next is for finding the next request from the form, just as Find Prev is for finding the previous request. Finally, the Criteria button allows us to search within the form request. You can search with any of the standard information.

How to Set Up a Ticket Tracker in Excel

The IT manager or the team lead is in charge of making decisions about implementing this ticketing system. Some fields are required when setting up a ticket tracker in Excel. The required fields are as follows:

• Serial Number: Numbers specifically added to differentiate between issues.
• Ticket ID: This uniquely assigned number serves as an individual ticket identifier.
• Task Name: For easy identification of a specific task.
• Task Description: Describe in detail what the requests are about.
• Category of Issues: This is a list of items that comprises hardware, software, mechanical, electrical, and system items.
• Priority: A drop-down list that consists of critical, high, medium, and low priority.
• Assignee: List of technicians' names so that one can be assigned to each request.
• Status: There are different stages in the ticket to show the status of the tickets, as well as a drop-down menu that consists of open, fixed, resolved, ongoing, and closed.
• Due Date: Date given by the manager to the assignee/technician to complete a given task.

Setting Up a Ticket Tracker in Excel

Serial Number

The first field in the template is the serial number field, which is designed in such a way that by entering the task description, the serial number is generated automatically. An Excel formula is used to achieve this goal; however, you will input the first serial number manually: =IF(D10="","",B9+1).

Ticket ID

Just like the serial number above, the task description field also automatically generates the ticket ID, but with a different formula (shown below).

Task Description

This is a brief description of what the request is about.

Category of Issues

This field displays the range of issues categories in our tracking ticket. First, all the fields must be turned into a table by pressing Ctrl + T on the keyboard.

Highlight all the cells you need to convert to a drop-down menu as shown below:

Locate the tabs at the top of your Excel dashboard.

Find the Data tab, then scroll to the right and click on the Data Validation icon.

A page will pop up with a drop-down list. Click on the drop-down, select List, and input all the categories of issues.

Priority Field

This field is responsible for displaying the priority level of the logged request. Just like the category issues field, it has a list of items that include critical, high, medium, and low.

Highlight all the cells you need to convert to a drop-down menu as shown below:

Locate the tabs at the top of your Excel dashboard.

Find the Data tab, then scroll to the right and click on the Data Validation icon.

A page will pop up with a drop-down list. Click on the drop-down, select List, and input all priorities.

‍

‍

Assignee Field

This is a field that lists the names of all technicians for request assignment by the IT manager.

Highlight all the cells you need to convert to a drop-down menu as shown below:

Locate the tabs at the top of your Excel dashboard. Find the Data tab, then scroll to the right and click on the Data Validation icon.

A page will pop up with a drop-down list. Click on the drop-down, select List, and input all technician names.

‍

‍

Status Field

This is a field created to show the status of the request at a given time. Open, fixed, resolved, ongoing, and closed are the statuses of this ticketing system.

Highlight all the cells you need to convert to a drop-down menu as shown below:

Locate the tabs at the top of your Excel dashboard.

Find the Data tab, then scroll to the right and click on the Data Validation icon.

A page will pop up with a drop-down list. Click on the drop-down, select List, and input all the statuses of the ticket (i.e., open, closed, ongoing, and resolved).

Date Field

The IT manager will give the due date to the technicians to complete each ticket.

There is also room to add more fields to this template and adjust it according to your needs.

An Automated Approach to Ticketing

As we’ve demonstrated, it is possible to use Excel for ticketing, but there are many drawbacks. Modern teams are distributed across time zones and require tools that easily enable collaboration. Excel is a brilliant tool for data analysis and financial modeling, but it falls short when managing a backlog of IT ticketing requests. There is a better way. 

Wrangle is the quickest way to automate ticketing and approvals in Slack. It provides a better solution than Excel's ticketing system because it has functionality and features found in a dedicated ticketing system. Unlike traditional ticketing platforms, Wrangle brings ticketing into your Slack channel, maximizing collaboration and efficiency. 

It’s packed with the features and tools you need to manage ticketing efficiently without forcing your teams into a separate ticketing tool. Wrangle manages your ticketing issues by:

• Turns any Slack message into a ticket
• Instantly allows users to see the status of a ticket
• Gathers context with an intake form
• Automatically assigns tickets to the right agent
• Automates reminders to keep agents moving
• Provides powerful reporting to uncover bottlenecks

Wrangle’s tickets are also coupled with powerful workflows that allow agents to automate the mundane tasks that are often required when using ticketing platforms.  You can also set custom workflows to tailor Wrangle for your use cases. 

To get started, add Wrangle to your Slack workspace or schedule a demo with our team. 

Conclusion

In this write-up, we established the benefits as well as the importance of a ticketing system. We briefly talked about those who need one and the limitations attached to an Excel ticketing system. We also looked at how to build a ticket tracker in Excel and how to build a submission form.

This post was written by Kamaldeen Lawal. Kamaldeen is a frontend JavaScript developer that loves writing a detailed guide for developers in his free time. He loves to share knowledge about his transition from mechanical engineering to software development to encourage people who love software development and don’t know where to begin.