A to-do list is a simple but effective way to organize your tasks and keep track of your progress. You can use Excel to create your own customized to-do list with formulas that can help you prioritize, filter, and mark your tasks as done. In this article, you will learn how to design your own to-do list in Excel formula, with a step-by-step guide and a sample scenario.
To design your own to-do list in Excel formula, you need to use the following elements:
- A table that contains your tasks, their due dates, their priority levels, and their status (done or not done).
- A formula that calculates the priority score of each task based on its due date and priority level.
- A formula that filters the tasks based on their status and priority score.
- A formula that marks the tasks as done by changing their status and color.
Procedures
To create your own to-do list in Excel formula, follow these steps:
- Create a table with four columns: Task, Due Date, Priority, and Status. Enter your tasks in the first column, their due dates in the second column, their priority levels in the third column (from 1 to 5, where 1 is the highest and 5 is the lowest), and their status in the fourth column (either Done or Not Done).
- In the fifth column, enter the formula
=TODAY()-B2+C2*7to calculate the priority score of each task. This formula subtracts the due date from the current date and adds the priority level multiplied by 7. The higher the priority score, the more urgent the task is. - In the sixth column, enter the formula
=IF(D2="Done","",E2)to filter the tasks based on their status. This formula returns a blank value if the task is done, or the priority score if the task is not done. - Sort the table by the sixth column in descending order, so that the tasks with the highest priority score are at the top.
- In the seventh column, enter the formula
=IF(D2="Done","Done",IF(E2<=7,"High",IF(E2<=14,"Medium","Low")))to mark the tasks as done or assign them a priority label based on their priority score. This formula returns “Done” if the task is done, or “High”, “Medium”, or “Low” if the task is not done and has a priority score of less than or equal to 7, 14, or more than 14, respectively. - Apply conditional formatting to the table to change the color of the cells based on their values. For example, you can use green for done tasks, red for high priority tasks, yellow for medium priority tasks, and blue for low priority tasks.
Scenario
To illustrate how to design your own to-do list in Excel formula, let’s use a sample scenario of a student who has the following tasks to complete:
- Write a research paper due on March 1, with a priority level of 1.
- Study for a midterm exam on February 25, with a priority level of 2.
- Do a group project due on February 28, with a priority level of 3.
- Read a book for a class discussion on February 26, with a priority level of 4.
- Watch a movie for fun on February 27, with a priority level of 5.
Assuming that the current date is February 23, the table and the formulas would look like this:
| Task | Due Date | Priority | Status | Priority Score | Filter | Label |
|---|---|---|---|---|---|---|
| Write a research paper | 3/1/2024 | 1 | Not Done | 13 | 13 | Medium |
| Study for a midterm exam | 2/25/2024 | 2 | Not Done | 11 | 11 | Medium |
| Do a group project | 2/28/2024 | 3 | Not Done | 18 | 18 | Low |
| Read a book | 2/26/2024 | 4 | Not Done | 15 | 15 | Low |
| Watch a movie | 2/27/2024 | 5 | Not Done | 16 | 16 | Low |
After sorting the table by the filter column and applying conditional formatting, the table would look like this:
| Task | Due Date | Priority | Status | Priority Score | Filter | Label |
|---|---|---|---|---|---|---|
| Write a research paper | 3/1/2024 | 1 | Not Done | 13 | 13 | Medium |
| Study for a midterm exam | 2/25/2024 | 2 | Not Done | 11 | 11 | Medium |
| Do a group project | 2/28/2024 | 3 | Not Done | 18 | 18 | Low |
| Read a book | 2/26/2024 | 4 | Not Done | 15 | 15 | Low |
| Watch a movie | 2/27/2024 | 5 | Not Done | 16 | 16 | Low |
The student can now see which tasks are more urgent and need to be done first, and which tasks are less urgent and can be done later. The student can also mark the tasks as done by changing their status to “Done” and see the color change to green.
Other Approaches
There are other ways to design your own to-do list in Excel formula, depending on your preferences and needs. For example, you can:
- Use different formulas to calculate the priority score of each task, such as using the number of days left instead of the difference between the due date and the current date, or using a different weighting factor for the priority level.
- Use different criteria to filter the tasks based on their status, such as using a checkbox instead of a text value, or using a different column to store the filter value.
- Use different criteria to mark the tasks as done or assign them a priority label, such as using icons instead of text values, or using a different column to store the label value.
- Use different colors to apply conditional formatting to the table, such as using a color scale instead of discrete colors, or using different colors for different priority levels.
