Author: Debra Dalgleish

If you’re working on an Excel project with a client, or other employees in your company, how do you keep track of everything that needs to be done? If emails are flying back and forth, with questions, and comments, and specification changes, it’s easy for things to slip through the cracks. Then, 5 minutes before the deadline, someone realizes that a key piece is missing!

Nobody wants that to happen, so it helps to have a system for keeping track of everything that has to be done.

Task List in Excel

For projects that require more than a couple of simple changes, I usually create a task list in Excel. It’s nothing too fancy – just a list of what needs to be done, broken into steps.

For example,

• if the project requires changes to several files, there are columns to record the file and sheet names.
• task types can be entered, so the time estimates can be grouped by those categories
• task description – as detailed as necessary – break large tasks into separate steps
• time estimate, in minutes, to get a rough idea of how long the project will take

You can see a few more columns, in the next screen shot.

• person the task is assigned to
• date the task was completed
• actual time to complete the task
• notes or questions about the task
• person who should answer the question
• replies to the questions

For other projects, you might need additional information, such as:

• task due date
• reference number
• department

What else would you need to record for each task?

Drop Down Lists

To make it easier to enter the tasks, I use data validation to create drop down lists in some columns.

The source lists are stored on a separate sheet, in named Excel Tables.

Total the Task Times

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.

The times are entered as minutes, so the formula sums all the times, then divides by 60

=SUM(tblTasks[Time Est Minutes])/60

Project Task Summaries

After the tasks are entered, you can create a pivot table or two, to show the estimated and actual times. The times can be summarized per person, or per task category, or any other grouping that you’d like.

Share the Task List

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. The only strange thing that I’ve noticed is that after you select from a drop down list, the cell below is selected, instead of staying in the same cell.

After you upload the file, use the Share options, to send a link to all the interested parties. I save the link as a bookmark in my browser, so it’s easy to get back to the file again.

You can download a copy of your 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 Project Task Tracker Sample

You can download a copy of my project task tracker file, from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0069 – Excel Project Task Tracker.

The zipped file is in xlsx format, and does not contain macros.

_________________

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:

• COUNT
• COUNTA
• COUNTBLANK

For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:

=COUNTBLANK(A1:A5)

More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:

• COUNTIF
• COUNTIFS
• SUBTOTAL
• AGGREGATE

For example, to count only the visible numbers, after filtering and/or manually hiding rows in a list, use a SUBTOTAL formula. This example uses 102 as the second argument, so it counts numbers only, in the visible rows (filtered or manually hidden).

=SUBTOTAL(102,B2:B10)

In the screen shot below, there are 5 visible numbers in cells B2:B10, and that is the result in cell B15, where the SUBTOTAL function is used.

The COUNT function, used in cell B12 in the screen shot below, returns 8 – it counts numbers in the hidden rows too.

Watch the Slide Show

To see a quick overview of 7 ways to count in Excel, you can watch this short slide show. It also contains a video on using the COUNTIFS function. You can see more examples on my Excel Count Functions page, and download the sample file.

Count Specific Items With COUNTIF Function

This video shows how to use the COUNTIF function to count cells that
contain a specific string of text, such as “Pen”.

_____________________________