How to organize tasks in Google Spreadsheet

Spreadsheets are often used in companies. From technology companies that rely on it to develop functions, formulas and control systems to the more traditional ones that rely on spreadsheets to control materials, stock and payrolls.

Spreadsheets are accessible and allow an easy understanding of how to organize tasks and information. But, even though they’re so commonplace, several functionalities of spreadsheets are not used or known.

In this text, we will give you some tips to better organize your tasks in spreadsheets, create processes and workflows that will make it easier to deal with your data!

Walkthrough: How to organize your tasks into spreadsheets

The first step to correctly use Google Spreadsheets is to have organized data. For this, some columns are essential:

• Description: a brief summary of what should be done about that task. It can’t be generic or overwhelming;
• Deadline: a limit for when the task can be delivered. It’s an important information that helps prioritizing tasks;
• Status: it’s important to have a column that shows if the activity was done or not. The ideal here is to use a Checkbox to mark them as done,, instead of deleting the row (losing the task history), for example;
• Owner: it’s essential to make it clear who’s the owner of that task.

Data Validation in a Spreadsheet column

To use the features of Google Spreadsheets without headaches, it’s better to make sure all entries are done correctly, respecting the proper formatting for each type of information. For example, if you enter a date as follows: “04/010/2021”, the data won’t be recognized as a date, as there is a “0” in front of “10”.

That is why it’s important to validate the data that can be inputted in the chosen cells, avoiding errors and incompatibilities. For this, the following steps must be followed:

1º) In Spreadsheet, select all fields you want to lock.

2º) In the “Data” menu, click on the “Data Validation” option.

3º) A pop-up will appear. In “Allow”, select the option referring to the data in the fields, in this case, “Date”.

You can select different filters, such as a period between numbers and dates.

Creating list field in Google Spreadsheets

In some cases, such as in the “Status” or in the “Owner” columns, only a few data options should be chosen. As in “Status”, there’ll be options such as: “Done” or “Not Done”, and in “Owner”, there’ll be only the company’s employees.

For this reason, it’s important to carry out a data validation that shows a list of items for these cells. To do this:

1º) In a separate column, describe the possible options. In the case of “Status”, we’ll use the items:

• New Task;
• In Progress;
• Done.

2º) Select the “Status” column. Go to the “Data” menu and click on the “Data Validation” option.

3º) In “Allow”, select the “List” option.

4º) In “Source”, click on the icon in the right corner and select the cells that show the data for that field.

5º) Click on “OK”.

6º) If you prefer, hide the cells that serve as the basis for the data so that they don’t interfere with the spreadsheet view.

Creating chart in Excel

Even though it’s a software designed for creating tables, you can also use charts to manage tasks. To create a chart, follow these instructions:

1º) Select “Status”.

2º) Go to the “Insert” menu and click on the option with the icon of a pie chart, select the format you want;

3º) In the new window, right click and select the option “Select Data”;

4º) Check if the selected interval is the one you want and click on “OK”.

Using COUNTIF to see the number of daily tasks

Another way to organize tasks is to create summaries that help seeing important data. For example, you may want to see the number of tasks per day, that is, how many tasks are in progress / will be done that day:

1º) Create an auxiliary column by placing the dates of the month.

2º) Insert the formula: “= countif (Deadline Column; Day Date)”.

3º) Apply the formula for all days of the month by dragging it downwards, so you can see the number of tasks that are being dealt with on each day.

Daily task bar chart

Now that we’ve created a template for displaying the number of tasks per day, we can create a bar chart to help us visualize the data. To create it:

1º) Select the “Date” and the “Number of tasks per day” columns, created in the previous section;

2º) Go to the “Insert” menu and click on the button with a bar chart icon;

You can see that as you enter new information and dates, the chart will update.

Using COUNTIF to check the number of tasks in progress

In addition to using “Countif” to check the number of tasks per day, it can also be used to validate the number of tasks in a specific case, for example, to count only the tasks with status “In Progress”.

To use this formula:

1º) As in “Countif”, create an auxiliary column and fill in the dates in each cell;

2º) The formula used will be “= COUNTIFS (Deadline Column; Day Date; Status Column, “In progress”)”.

3º) Apply the formula for the entire month by dragging it downwards, so you can see the number of tasks that are being dealt with on each day.

These tips will help you a lot! But, if you are looking for even simpler and complete software, try using jestor.