Help Desk Ticket Tracker Excel Spreadsheet

Help Desk Ticket Tracker

Tracking SLAs are the biggest challenge for anyone managing a help desk. It is important that the manager is on top of SLAs, resource utilization, and queue management. This excel help desk manager, makes it easy to create, delete, manage and track tickets.

Help Desk Ticket Tracker Excel Spreadsheet

About The Excel Help Desk Tracker

• The free tracker for each ticket has a ticket number, description, assignee, category, SLA, time received, SLA target, Ticket Age, Breached.

• Has a queue, reports, and config section for better control.

• SLA can be set as per category, which provides flexibility to assignee different SLAs to different types of tasks.

• SLA target, Ticket Age, and Breached are auto-calculated.

• Easy to use buttons for adding and deleting a ticket.

• An SLA refresh button sorts the tickets in order of priority.

• Has a mini dashboard that shows total tickets, breached tickets, and max-age of the oldest ticket in the queue.

• The report also highlights the number of tickets each agent is working on, allowing effective resource management.

• When calculating the SLA, the excel ticket tracker considers only working hours, weekdays, and holidays defined in the Config tab.

• Has a report section that shows the ticket status and reports for categories and agents. This can be easily copied into a PowerPoint or email to send a status update.

• Tickets that have breached SLAs are highlighted in red.

Using The Ticket Queue

Using the excel help desk queue

• Add New tickets - Use to add a new ticket to the queue.

• Delete a ticket - This allows deletion of an existing ticket.

• Refresh SLAs – sorts the queue to bring the tickets expiring soon on the top.

• Total tickets – the number of tickets in the queue currently.

• Breached – the number of tickets for which SLA is breached.

• Max Age – shows the highest age of any ticket. This number tells how old is your oldest ticket.

• No – ticket number. Automatically populated by the add new ticket button or can be manually updated.

• Description – a brief description of the ticket.

• Assignee – Drop downfield, which is populated from the agent's list on the config tab.

• Category – A way to categorize the tickets. Very critical as SLA is based on the categories.

• Urgency – A classification of severity of the issue. Not used in any calculation.

• SLA – SLA time in hours.

• Time Received – a timestamp for the ticket was received.

• SLA Target – displays the time by which the ticket needs to be resolved.

• Ticket Age – the number of days the ticket has been present in the queue.

• Breached – a yes or no value to indicate if the ticket had breached SLA.

Add A New Ticket

• Place the cursor on a cell with the active ticket. This is mandatory as the add new ticket functionality copies an existing ticket to create a new ticket.

• Click on the Add New Ticket button, and Excel will show a popup to confirm your action. Click yes if you want to proceed.

• A new row will be added with all the cells identically except two – No and Time received.

• No (or Ticket No) is auto-calculated by taking the highest number in the No column and adding 1.

• Time received is defaulted to the current system date and time. As it defaults to the current time, you recommend that you add new tickets during business hours.

• If you have received a ticket that actually came in earlier but was not recorded, please follow the above steps but update the time received to the correct time.

• Click the refresh SLA button for the queue to be sorted in order of priority.

Delete A Ticket

• Place the cursor on the ticket to be deleted, and press delete a ticket.

• Excel will ask for confirmation to delete by showing the ticket number.

• If you are sure, then press yes. Excel will then delete the row.

• Click the refresh SLA button for the queue to be sorted in order of priority.

Using The Report Tab

Help Desk Report

• The reports tab contains a service desk dashboard that shows the total tickets, how many breached, and mag ticket age.

• It also contains a report for Categories and Agents. For each of the categories and agents, totally open and breached tickets are shown.

• The report can easily be copy-pasted into documents or emails for status updates.

• For the agents, the number of tickets is highlighted in amber if they have zero tickets. This indicates to the manager that he has to assign some tasks to that person. This feature allows the team manager to effectively manage team tasks.

Using The Config Tab

Using the Config Tab

• The config tab has all the config related to using the spreadsheet.

• Categories are the different types of tickets your team will work on. If you have only one, then you can have just one category. But then you will also need to have only one SLA.

• If you want to be able to configure multiple SLAs, then you should have multiple categories.

• The following can also be defined urgency types,

• Beginning and End working for the team. This impacts the SLA calculation and how the SLA target field in the queue is calculated.

• Holidays is the holiday as per your location.

• Agents are the team member who is going to work on the tickets. This list will feed into the assignee list on the queue.

Disadvantages Of The Template

• The template cannot handle sorting for other fields and can only handle the SLA target.

• If a new ticket is added during non-business hours, then the tracker updates the SLA incorrectly.

• The template is limited to handling a max of ten agents and ten categories.

• Add new ticket can behave strangely if the cursor is not placed on a ticket as it uses copy.