How to Create a Task Tracker in Google Sheets
What is a task tracker?
A task tracker often referred to as a to-do list, is a system for organizing and managing tasks. The organization of tasks can take many forms, but usually includes a visual representation of each item by its status—to-do, doing, or done.
Businesses of all sizes use a variety of systems to organize their work more efficiently, from a simple to-do list to complex project management software. Some of the most common features in task tracking software include time tracking, team projects, calendar view, templates, and mobile app access.
Even the very best task tracking software won't be better than a system that works and makes sense to you. More often than not, adding complex functionality will only complicate your own personal project management.
That's why we'll make a simple dynamic task tracker in Google Sheets. Not only is this a free alternative to expensive software, but it also allows you to customize it to fit your exact needs.
Make a copy of our task tracker template or follow along the tutorial to build your own.
How to make a simple, dynamic task tracker in Google Sheets
For starters, let's open a new Google Sheet and add the Today (=today) formula in cell D1, and hit enter.
Pro Tip: Save some clicks by entering the URL sheets.new into your browser. On Chrome, it will automatically open a blank spreadsheet. It also works with Google Docs (docs.new), Slides (slides.new), and Forms (forms.new).
Now that D1 shows today's date, we want the three columns before and after D1 to reflect the current week. We can do that by adding +1 or -1 to the =today formula like this.
Ignore the dates in the image above, since they will be relative to the date you are actually building your task tracker on.
Next, let's create a new tab on our sheet. This second tab will actually hold all of our to-do tasks. And we'll use the first tab as a dashboard to only show the current week's tasks. Since we're using dynamic formulas on our first tab, it will always show the most relevant data.
Whatever the first date is on tab 1, enter that date into the very first cell (A1) on tab 2. Then add subsequent dates along row 1 for as long as you'd like to track your tasks.
Make sure that these are formatted as dates and not using a formula like on our first tab. Now that our second spreadsheet is formatted, we can start pulling this data into our weekly overview tab.
Add in some sample tasks to the first few days. This will help us test to make sure everything is working when we pull tasks into our overview tab.
Head over to tab 1, and let's start building our HLOOKUP formula in cell A2. If you've struggled with vertical or horizontal lookups in the past, don't worry. Building these formulas requires a lot of trial and error. But if you really struggle, feel free to use our template with pre-built formulas.
Understanding the HLOOKUP Formula
Here's the formula: =HLOOKUP($A$1,Sheet2!$1:$11,2,TRUE)
Let's dissect each part of the formula to understand exactly why this works.
This stands for horizontal lookup. A horizontal lookup means that Google Sheets is going to search across the first row of a given range and return a value we specify found in that column.
Search Key. $A$1 or A1
This search key is referencing cell A1 on tab 1. It's telling Google Sheets that the value we want to look for is the date in cell A1. We've added the "$" symbols to lock down that cell, so when we drag the formula down, it won't change or update it.
The range to consider for the search. The first row in the range is searched for the search key. In this case, it's searching row 1 through 11 of tab 2 for our date in A1. Even though all of our dates are in row 1, we set it to search in rows 1 through 11 because once it finds our date, it has to look at rows 2 through 11 to add in our to-do data. You don't have to have the "$" symbols, but it will make it easier in the next step to drag our formula down to additional cells.
Index is the cell containing the information returned from the matched column. In this case, 2 refers to the first task in the first column on tab 2.
Is Sorted. True
This is an optional value that tells our formula whether or not the row it's searching for is sorted.
As mentioned previously, you don't need to understand each part of this formula in order to use it. But now that the formula is complete, we just have to drag it down column A and adjust the index to match the row that it's referencing.
For example, in cell A3, the index should be 3.
To begin filling out additional columns, drag the formula in A2 over to G2. Update the search key in each cell to match the column that it's referencing.
The formula in B2, for example, would have a search key of $B$1
The final step in building our task tracker is to drag down the formula in row 2 for each column down to row 11. You don't have to stop at 11 if you have more than 10 to-dos for each day. Once you've filled out each column, you'll just need to update the index to correspond to the row that it's in.
That's it! Now you can easily add tasks to tab 2, and tab 1 will dynamically pull in tasks for the current week, showing you the most important information and nothing else. You can also tweak the design of your new task tracker to match your personal taste.
Reactive Reporting Tools
Task and project management have become huge industries as today's technology companies are more hyper-focused on productivity than ever. But to-do lists and project management software are still reactive means of reporting. Even though they help you see what's being done, they don't do much to accomplish the work.
Hivewire is a different kind of productivity tool. It combines the spreadsheet-like environment of Google Sheets with a visual workflow builder. So you can create processes using if-then logic and data-driven reporting to act on data dynamically instead of just staring at a spreadsheet that tells you your to-dos. Try Hivewire today and see how much more you can get done.