RESOURCES
Issue Tracker Spreadsheet
SYNOPSIS
Need a way to manage and resolve competing issues? Use our Issue Tracker spreadsheet template to plan, assign, manage, report, and track the progress of multiple issues. An issue list compiles all open tasks, which are then visualized via kanban, dashboard, and Gantt chart views. Issue types are color-coded and fully customizable to track the most important recurring issue areas. Potential issues could be new features to develop, bugs to be resolved, recurring tasks or user experience overhauls.
PREVIEW
TOOL HIGHLIGHTS & EXPLANATION
How to create an "issue tracker" template in Excel
So how can you make your own issue tracker in Excel? For an issue tracker that works like Jira, you need to plan out, assign, manage, report, and track the progress of a series of competing issues, whether they be new features, bugs, recurring weekly tasks or user experience overhauls. Issue trackers are vital for any industry, as a recent Gartner survey found eighty-eight percent of boards regard cybersecurity as a business risk rather than solely a technical IT problem. (Source)
But you don't need fancy issue tracker software to manage your workflows. Below, we're going to show you how you can make your own issue tracker template in Excel or Google Sheets and what components to include to either use with, or replace, an existing issue tracker project management software tool like Jira. To save time, you can also download and customize our Issue Tracker template.
Coordinate your team
This is an Issue Tracker spreadsheet template we created and is meant to complement whatever issue tracker software system you use. To make ours, we started by making an issue list to track open tickets, then divided that list into a reporter section for whoever opens the ticket and a resolver section for whoever is assigned the ticket. Reporters can either be internal team members or external customers who experience issues. A chart at the top shows resolver availability so managers know whose schedule is the most open to assign new issues. Each ticket also has its own ID key, which can be defined by us, or by the ticketing software we use.
Then, the issues' type is listed, whether it's a bug, new feature, task, sub-task, story, or epic, which is an issue ticket that represents an overarching project that is broken down into specific tasks which are called user stories. "Epics" help team leads to manage the user journey and bucket-related tasks, while "user stories" are requests and needs in a customer's user journey. So if you're a PM who needs to manage a massive project, you place it in an epic, broken down into stories, tasks, and sub-tasks.
Define your top issues
Your issue tracker issue list should sort issues by type. In our example, we define these inputs in the Fields tab here. Your fields tab should also define the priority levels of your issues, who can open a ticket, the stages of your workflow, and the status of tickets. In our variation, each issue type is color-coded, and type names are editable in the Fields tab.
Prioritize issues by stage
Now that you've loaded all the issues onto an issue list, you need a way to track all these issues visually. In our example, we provide three visualizations: a bug board, a dashboard, and a Gantt chart - that is fully customizable.
So, let's say you want to follow the agile framework that Jira uses. This bug board utilizes a kanban visualization that works with the agile management style to sort issues by workflow stage. Following the kanban style, they are color-coded by priority. In Agile, the simplest workflow format is to have a backlog stage, an in-progress stage, a code review or quality assurance stage, and then a complete stage. You should also provide some filters as we did at the top, especially if you are a manager or working with multiple team members so individuals can filter for their most important metrics.
Visualize key metrics
Speaking of key performance indicators, you'll also want to create some sort of dashboard to make issue tracking easier. In our template, we created pie charts to track issues by their status and priority, as well as charts to track issue age, how many of each issue type are open, and all sorts of other helpful metrics for PMs.
These metrics as vital, as Gartner predicted that at least 50% of C-level executives will have performance requirements related to cybersecurity risk built into their employment contracts by 2026. So tracking these KPIs is vital not just for you but for your boss, your boss's boss… and maybe even your boss's boss! (Source)
Visualize your timeline
An alternative visualization to kanban is a Gantt chart. Gantt charts visually track the progress of your workflow across time, not just across stages, to assess how long each issue takes across each stage. In our Issue Tracker template, each issue type is color-coded, and type names are editable in the Fields tab. As team members make progress on their individual issues, they can edit the progress bars of the active stage by changing the percent to completion next to the bar. This provides accountability for the whole team, so progress is made (and documented) on active tickets in real time.
Here are a few more charts that are included in our issue dashboard, including pie charts for "Issues by type," to view how many of each issue you have to do, a bar chart for "Time since issue," as well as one for "Resolution time" that tracks how long it takes for the average issue to be resolved, and bar charts that sort issue by priority, stage, or whatever other metric you want to review each issue type by.
And that's all you need to create your own issue tracker template in Google Sheets or Excel. If you prefer to save yourself time and hours of work, just download the free version of our template and try it out. For more tools to increase your team-wide productivity, go check out our To Do List spreadsheet template right now.
RELATED
To Do List spreadsheet template
Task Tracker presentation template