Multiple Project Tracking in Excel with common tasks

July 18, 2020 12:42 PM

I'm trying to set up a high-level project management dashboard in Excel to track 40 projects, with a common broad set of 10-15 tasks (but some options among the subtasks, and some tasks not necessary in certain projects), some subcontracted out, and three levels of prioritization which will dictate the due date for each type of task. Specifically, how can I use formatting or text to let a single Excel cell reflect whether a task is needed or not, due on a certain date or not, and completed or not?

Right now this is what I'm doing, but it's kind of clunky.
Blank cell - task hasn't been analyzed yet
N - task not needed
Y - task needed but due date not assigned (or it's such a simple task we don't need a due date)
MM/DD, highlighted yellow - need to assign to subcontractor by date. This also triggers a follow-up task
MM/DD, highlighted green - assigned to subcontractor by date
X - complete

I'm feeling that the Y, N, X is not intuitive enough. I don't want to use dates for all completed tasks because in some cases the completed date is truly irrelevant. Unfortunately the nature of the projects is such that we can't analyze the necessity of tasks up-front.

I'd also appreciate any general tips from anyone who does something similar. I have looked at Gantt charts but I need to see every project on one screen (some right-scrolling is ok), and I'm not sure that a Gantt chart could accomplish that. And my time frame is that I work in semi-annual periods.

posted by acidic to Technology (5 answers total)

I have done something like this with a lot of conditional formatting and vlookups across multiple sheets, and I would highly advise against kludging excel into a project management tool (I have done it. A number of times. Don't be me). Both Trello and Asana have freemium versions and will do what you need with far less PITA factor and far more user-friendliness.
posted by aspersioncast at 1:08 PM on July 18, 2020

Response by poster: For many good reasons, the file definitely has to be in Excel (which has been working very well for my purposes for a while-- I'm just trying to make it better). Thanks!
posted by acidic at 2:00 PM on July 18, 2020

Then conditional formatting is your friend - don't bother doing any highlighting manually, because you can't sort highlighting. You'll want separate sheets within the workbook, cross-referenced with each other. Have a column for your dates and/or a column for your codes, and set up the highlighting rules based on dates and codes.
posted by aspersioncast at 6:44 PM on July 18, 2020

because you can't sort highlighting
You can sort and filter by either font color or fill color.

Can you explain more about why you need the status and dates in the same cell?
posted by soelo at 10:20 PM on July 18, 2020 [2 favorites]

In one sheet I suggest having the following columns: unique ID*, project ID, task ID, subtask ID, project name, task name, subtask name, priority level, assign to contractor by date, assigned to contractor by date, complete?.

The needed/ complete columns would either be a drop down menu with yes/ no or a checkbox. The Priority level column would have the following drop down menu options: high, medium, low, none (aka not needed).

Conditional formatting would be used for color coding cells.

The default value for the date columns is NA, but could be filled in with dates as needed.

There’s a fair amount of ways you can view this data within the tab (e.g. filters, hiding columns. However, if you need a separate dashboard tab consider using cell linking, vlookup or index/match, or the countif function.

*created by concatenating project ID through Sub task ID with a common separator (e.g. P01.T01.a)
posted by oceano at 10:42 PM on July 18, 2020