If you're creating new Excel workbooks, or making extensive changes to existing spreadsheets, track the project tasks in Excel. This helps you co-ordinate with your clients or a few co-workers, so everything gets done. Use this sample workbook to list the project tasks, estimated time, person assigned, and other details. Pivot tables show you the project task summary.
I've used this system to track project tasks, when making major changes to Excel workbooks. It also works well when you're building new workbook, or a set of connected Excel files. Keep track of the tasks that you, your clients, and your co-workers need to do.
This task tracker could help you
For Excel projects that require more than a few simple changes, I like to use this Excel project task tracker. It's nothing too fancy – just a list of what needs to be done, broken into steps.
The project tasks are in a named Excel Table, with 12 columns. As you add tasks, the table will expand automatically, to include the new rows. Here are the first 6 columns in the Excel Project Task Tracker:
And here are the remaining 6 columns in the project task list:
If needed, you could add more columns for project task information, such as:
To make it easier to enter the task information, the project task list has data validation drop down lists in 4 of the columns -- File, Sheet, Task Type and Task For.
The source lists for those drop down lists are stored on a separate sheet, in named Excel Tables.
To see the steps in creating a named Excel Table, you can watch this short video.
At the top of the task sheet, there are cells that summarize the estimated and actual task times. That gives you a quick overview, while you enter the tasks, or update them with actual times.
In the project task list, times are entered as minutes. The summary formulas sum all the times, then divide by 60, to show the results in hours
This formula in cell C1 calculates the total estimated hours:
This formula in cell H1, calculates the total actual hours:
In the Project Task Tracker sample file, there are two pivot tables on the Summary sheet. These show a summary of the project task information that has been entered.
NOTE: Refresh the pivot tables after updating the Project Task list
The pivot tables show the estimated time and actual times, in minutes:
You could create more pivot table reports, to show the project task summaries that you need.
For example, add Task Type to the Row area, to show Task Type per Employee
To see the steps for setting up a pivot table, you can watch this short video.
I don't like setting up shared Excel workbooks, but I've found that it works well to upload a file to Microsoft's OneDrive site, and everyone can edit the task list there, using Excel Online.
After you upload the file, use the Share options, to send a link to all the people involved in the project.
In OneDrive, you can download a copy of your project task file, after you make changes, to store as a local backup. Or, go back to look at previous versions of the file.
Download the Excel Project Task Tracker workbook. The zipped file is in xlsx format, and does not contain any macros.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: October 2, 2020 1:37 PM