The spreadsheet is the ultimate all-purpose app. Even when there are focused apps, many people prefer the flexibility and the familiar interface of a spreadsheet. Sort and filter data to your heart’s content, apply formulas, and generate charts – a spreadsheet does it all.
We will show how the newly released Data Validation functionality in Zoho Docs Spreadsheet can be used along with features like Conditional Formatting, Auto Filters and Charts to build a simple task manager.
We have created an online public spreadsheet that showcases a Simple Task Management application for teams. Here is the interactive spreadsheet embedded below. Try editing the existing tasks or adding new tasks.
Let’s have a look at the various features used on this spreadsheet. This will give you an idea on how best to use these features in your spreadsheets.
1. Data Validation for Drop-down Lists:
This spreadsheet uses Data Validation on the Owner, Status and Priority columns. The Owner column gets the list of allowed values from the first column in the “Team” worksheet. Adding/modifying members in the Team worksheet will automatically update the drop-down list on the Owner column. The Status and Priority columns show a list of pre-defined values which was given while configuring the Data Validation on these columns. Try editing these columns in the above spreadsheet.
2. Conditional Formatting to Highlight Tasks:
This spreadsheet also uses Conditional Formatting to fill the cells under the Priority column with shades of red depending on its priority. It also uses a Formula based Conditional Formatting rule to strike out the entire row of tasks that are Closed and to de-emphasize it with a gray text color so that Open tasks get more attention.
You could do a lot more with Conditional Formatting. For example, you can highlight tasks whose due date has elapsed or highlight tasks that are due in the next 7 days.
Learn more about Conditional Formatting »
3. Auto Filters to Filter and Sort Tasks:
Auto Filters have been applied to the task list so that you can filter the tasks by Owner, Status, Priority, etc. You can also use the ‘Filter by Color‘, that we supported recently, on the Priority column. You also have the option to sort the table by Task, Due Date, etc.
4. Formulas and Charts for Reporting:
The ‘Reports’ worksheet tab contains pre-built charts of aggregated information, such as ‘Issues by Status’ and ‘Issues by Priority’ of the team member you select. Again the input cell for team member has Data Validation settings applied to automatically bring up the Team Members as a drop-down list. Changing the team member will automatically recalculate the formulas that shows the aggregate information of that member and the chart refreshes itself to show the data of the team member you just selected.
5. Share and Collaborate in Real-time with your Team:
If you would like to use this spreadsheet for your needs, feel free to save it to your account. You will find the ‘Save to My Account’ option under the File menu on this public spreadsheet. You can then share it with your co-workers, start managing your tasks and collaborate in real time. You can also initiate a group chat with your collaborators who are online on that spreadsheet for quick interactions.
We have just scratched the surface here. You could extend this task management spreadsheet on Zoho Docs and add more functionality such as:
- Manage multiple projects by having different worksheets or even different files for different projects.
- Write VBA Macros to automatically archive Closed tasks and move them to a different worksheet on the click of a button.
- Highlight Overdue / Upcoming tasks using Conditional Formatting. You could then filter those tasks by the cell color / text color.
- Link your tasks to an external site such as a document in Zoho Docs.
- Add a comment to the cell containing the task (we will come up with a full-fledged commenting system in the near future).
- Email your team members from right within this spreadsheet using the ‘Notify Collaborators‘ functionality.
- Track who has made changes to your spreadsheet using the ‘Audit Trail‘ functionality. Automatic email notifications will be supported down the line.
We hope you find these features really useful on your spreadsheets. Do share your feedback with us.
If you don’t have an account with Zoho, you can sign in to Zoho Docs using your Google / Yahoo / Facebook accounts to get started.