[thelist] Tip: Issue Tracking using MS Excel

John.Brooking at sappi.com John.Brooking at sappi.com
Thu Apr 29 15:46:20 CDT 2004

Just building up my credit... (I have been working on this and thought
others might find some of it useful.)

<tip type=" Issue Tracking using MS Excel" author="John Brooking">
MS Excel makes an excellent "database" in which to keep a list of all issues
and tasks you are working on. Here are some ways I have found to make it
work very nicely for me:

* I define columns for Status, Priority, and Type, to be able to filter and
sort by these fields. I define these values with both a leading number and a
keyword, such as, for Status, "1 Active", "2 On-Going", "3 Hold", "4
Finished", and "5 Cancelled". The numbers ensure they will sort in the order
I want, but the words keep them meaningful to me. (I also color-code them
for easier visual scanning.) I add a Comment (Insert|Comment) to each header
listing these labels, so I can remind myself of them by hovering the mouse
over the header.

* The Type column is for differentiate between bugs, to-do's, enhancements,

* Excel's "Auto-Filter" feature (Data | Filter | Auto-Filter, when the
cursor is on the heading row) will add a drop-down to each column header
containing a distinct list of all values in that column. Selecting one will
filter the rows so that only those with that value show. Selecting a value
in multiple columns filters for all selected values using "AND". This is
handy for seeing only active issues, or finished ones, or just bugs, etc.
This works well with the "Freeze Panes" feature, to keep the header row
always in view.

* Since I work on and support several different projects, I have a "Project"
column as well. Combined with the auto-filter feature, I can either look at
my list of issues one project at a time, or, by selecting all projects, see
all my issues in order of, say, priority, independent of the project. This
is handy for when I finish one thing and am considering what to tackle next.

* I assign each issue a sequential number. To keep track of which number is
available next (not obvious if you are filtering and/or sorting), I insert a
row above the header row showing the next available number, by using the
formula "=MAX(A3:A999)+1" (my sequence numbers are in column A, and the data
starts in row 3). Whenever you add a new number, this cell is updated to
show the next. And if you have panes frozen, this number is always in view.

* For larger projects, I add additional worksheet tabs to track history of
each project (conversations with people, requests received, issues resolved,
etc.), with a timestamp for sorting ascending or descending.

The only big drawback to this system is that the data is not easily
shareable with other people or systems, due to Excel's file-level locking,
and the fact that everyone has a slightly different set of concerns. But for
just keeping track of your own issues, it's the best combination of
versatility, simplicity, and ease-of-use that I have yet found.

- John


This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like. 

More information about the thelist mailing list