Help Desk Ticket Tracker Excel Spreadsheet Free Download

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


Help Desk Ticket Tracker Excel Spreadsheet
Help Desk Ticket Tracker Excel Spreadsheet

About the Excel Help Desk Tracker

  • The free tracker for each  ticket has 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 which sorts the tickets in order of priority.

  • Has a mini dashboard which shows total tickets, breached tickets and max age of the oldest ticket in the queue.
  • The reports also highlights the number of tickets each agent is working on which allows 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 which shows the ticket status and reports for categories and agents. This can be easily copied into a PowerPoint or email to send an status update.  
  • Tickets which have breached SLAs are highlighted in red.

Using the ticket queue

Using the excel help desk queue
Using the excel help desk queue
  • Add New tickets - Use to add new a ticket to the queue.
  • Delete a ticket - Allows to delete an existing ticket.
  • Refresh SLAs – sorts the queue to bring the tickets expiring soon on the top
  • Total tickets – number of tickets in the queue currently.
  • Breached – 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 down field which is populated from the agents list on the config tab.
  • Category – A way to categorize the tickets.  Very critical as SLA are based on the categories.
  • Urgency – A classification of severity of the issue. Not used in any calculation.
  • SLA – SLA time in hours.
  • Time Received – time stamp for the ticket was received.
  • SLA Target – display the time by which the ticket needs to be resolved.
  • Ticket Age – number of days the ticket has be 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 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 to it.
  • Time received is defaulted to current system date and time. As it defaults to current time it is recommended that you add new tickets during business hours.
  • If you have received a ticket which 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
Help Desk Report
  • The reports tab contains a service desk dashboard which 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 total 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
Using 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 has an impact on 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 are going to work on the tickets. This list will feed into assignee list on the queue. 

Disadvantages of the template

  • The template cannot handle sorting for other fields and can only handle 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 be behave strange if the cursor is not placed on a ticket as it uses copy.
Swapnil Wale

Written by

Swapnil Wale is an IT Professional based in Sydney, Australia with over 10 years of experience in technology and project management. He is a passionate blogger and focuses on project management and BRMS articles.

0 comments:

Post a Comment

 

© 2013 Free Project Management Templates. All rights resevered. Designed by Templateism

Back To Top
Real Time Web Analytics