If you want to create a fully functional to do list in Excel, this article is for you. Here, we will walk you through 4 easy and effective methods to do the task effortlessly.
Download Workbook
4 Methods to Create Fully Functional To Do List in Excel
The following Homework List table shows Date, Subject Name and Status columns. In the Status column, we will show you how to create a fully functional to do list by using 4 methods. Here, we used Excel 365. You can use any available Excel version.
Method-1: Using Drop Down List to Create Fully Functional To Do List
Here, in the Status column, we will create a drop down list to make a fully functional to do list.
- First, we will select the entire Status column from cell D5 to D14.
- After that, we will go to the Data tab > select Data Tools > select Data Validation > select Data Validation.
A Data Validation dialog box will appear.
- Then, in the allow box, we will select List > in the Source box and click on the upward arrow marked with a red color box.
- Afterwards, we selected from cell F5 to F7 as Source > click OK.
Now, we can see a down arrow box marked with red color at the right bottom of cell D5.
- Then, we will click on this arrow and we can see the status names > we will select On Going.
We can see the Status in cell D5.
Finally, we can see the fully functional to do list.
Method-2: Use of Check Box to Create Fully Functional To Do List
In this method, we will use the check box to create fully functional to do list.
Steps:
- First, we will click on a cell, here we click on cell D5 > go to the Developer tab > click on Insert > select Check Box.
We will see a plus sign in cell D5.
- After that, we will click on cell D5.
We will see a Check Box in cell D5.
- Then, press ENTER.
- Afterwards, we will drag down the Check Box downward with the Fill Handle tool.
We can see the Check Box in each cell of the Status column.
- Afterwards, we will click on the Check Box to show whether the homework is complete or not.
Finally, we can see the fully functional to do list.
Read More: How to Make To Do List in Excel with Checkbox (With Quick Steps)
Similar Readings
- How to Keep Track of Invoices and Payments in Excel (3 Ideal Examples)
- Create a Recruitment Tracker in Excel (Download Free Template)
- How to Keep Track of Inventory in Excel (2 Easy Methods)
- Make a Sales Tracker in Excel (Download Free Template)
- How to Track Multiple Projects in Excel (Download Free Template)
Method-3: Employing Sort Option to Create Fully Functional To Do List
Here, we will use the Sort & Filter option to show a fully functional to do list.
Steps:
- First, we will complete the Status column by following the steps described in Method 1.
Here, we keep the rows highlighted with green color to show Complete in the Status column, and we will filter these rows.
- After that, we will select the entire Status column from cell D5 to D14 > go to the Home tab > select Editing > select Sort & Filter option > select Filter.
Now, we can see a downward arrow marked with red color box in cell D5.
- Then, we will click on this arrow.
- Afterwards, we will mark on Complete > click OK.
Finally, we can see that the Status column contains Complete.
Method-4: Using Excel Formula & VBA to Create Fully Functional To Do List
In this method, we will use VBA code to create fully functional to do list.
Steps:
- First, we complete the Priority column by following the steps described in Method-1.
Here, for the Priority list, we take Weightage and in cell G5 we insert a Status Input symbol.
Now, we want to calculate the Total Activities, Completed Score, Total Score and %Completed. We will use the COUNTIF, COUNTIFS and IFERROR functions in that case.
- After that, we will click on cell I11 and type the following formula to calculate Total Activities.
=COUNTIF($D$5:$D$14,"<>")
Formula Breakdown
- COUNTIF($D$5:$D$14,”<>”)→ counts the number of cells that meet a criterion.
- Output → 10.
- Then, press ENTER.
- Afterwards, we will type the following formula in cell I12 to calculate Completed Score.
=COUNTIFS($E$5:$E$14,$H$5,$F$5:$F$14,$I$9)*$I$5+COUNTIFS($E$5:$E$14,$H$6,$F$5:$F$14,$I$9)*$I$6+COUNTIFS($E$5:$E$14,$H$7,$F$5:$F$14,$I$9)*$I$7
Formula Breakdown
- COUNTIFS($E$5:$E$14,$H$5,$F$5:$F$14,$I$9) → applies criterion to cells across multiple ranges and counts the number of times the criterion is met.
- Output → 0
- COUNTIFS($E$5:$E$14,$H$5,$F$5:$F$14,$I$9)*$I$5 → multiplies 3 with $I$5.
- Output → 0
- COUNTIFS($E$5:$E$14,$H$6,$F$5:$F$14,$I$9) → applies criterion to cells across multiple ranges and counts the number of times the criterion is met.
- Output → 0
- COUNTIFS($E$5:$E$14,$H$6,$F$5:$F$14,$I$9)*$I$6 → multiplies 2 with $I$6.
- Output → 0
- COUNTIFS($E$5:$E$14,$H$7,$F$5:$F$14,$I$9) → applies criterion to cells across multiple ranges and counts the number of times the criterion is met.
- Output → 0
- COUNTIFS($E$5:$E$14,$H$7,$F$5:$F$14,$I$9)*$I$7 → multiplies 0 with $I$7.
- Output → 0
- Then, press ENTER.
- After that, we will type the following formula in cell I13 to calculate Total Score.
=COUNTIF($E$5:$E$14,$H$5)*$I$5+COUNTIF($E$5:$E$14,$H$6)*$I$6+COUNTIF($E$5:$E$14,$H$7)*$I$7
Formula Breakdown
- COUNTIF($E$5:$E$14,$H$5) → counts the number of cells that meet a criterion
- Output → 4
- COUNTIF($E$5:$E$14,$H$5)*$I$5 → multiplies 4 with $I$5
- Output → 20
- COUNTIF($E$5:$E$14,$H$6) → counts the number of cells that meet a criterion
- Output → 3
- COUNTIF($E$5:$E$14,$H$6)*$I$6 → multiplies 3 with $I$6
- Output → 9
- COUNTIF($E$5:$E$14,$H$7) → counts the number of cells that meet a criterion
- Output → 3
- COUNTIF($E$5:$E$14,$H$7)*$I$7 → multiplies 3 with $I$7
- Output → 3
- After that, press ENTER.
- Afterwards, we will type the following formula in cell I14 to find out Percent Completed.
=IFERROR(H10/H11,0)
Formula Breakdown
- IFERROR(I12/I13,0) → If TRUE returns the value of H10/H11, otherwise returns 0.
- Output → 0.0%.
- After that, press ENTER.
- After that, one thing must be noted that we have to keep the cell I9 in Wingdings Font.
- Along with that, we have to keep the Status column in Wingdings Font.
Now, we will insert VBA code to create a fully functional to do list.
- We will right-click on the VBA Code sheet > select View Code from the Context Menu.
A VBA editor window will appear.
- Afterwards, we will click on the downward arrow of the General box > select Worksheet.
- Afterwards, we will click on the downward arrow of the SelectionChange > select BeforeDoubleClick.
- Then, we will type the following code in the VBA editor window.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = False
If Target.Row >= 5 And Target.Row <= 14 And Target.Column = 6 Then
Cancel = True
If Cells(Target.Row, Target.Column) <> Range("I9").Value Then
Cells(Target.Row, Target.Column).Value = Range("I9").Value
Else: Cells(Target.Row, Target.Column).Value = ""
End If
End If
End Sub
Code Breakdown
- Here, I used the Private Sub where I used DoubleClick as event. If I double click on the selected column then the code will execute and will place the Check Mark on the completed project.
- Then, I used a IF statement to check the values of rows 5 to 14. If the cell is not equal the value of I9 cell then it will place the value of I9 cell there otherwise will keep the cell empty.
- We will now close the VBA window and return to our worksheet.
- After that, we will double-click on cell F5.
We will see a marked symbol. Along with that, we will see %Completed is 15.6%.
Now, we want to highlight the marked Status row.
- First, we will select the entire data of the table > go to the Home tab > select Conditional Formatting > select New Rule.
A New Formatting Rule window will appear.
- Afterwards, we will select Use a formula to determine which cells to format.
- After that, we will write the following formula in Format value where this formula is true box.
=AND($F5=$I$9,$D5<>"")
- Then, click on Format.
A Format Cells window will appear.
- After that, we will select a color, here we selected blue and we can see the Sample of the color > click OK.
In the New Formatting Rule window, we can see the Preview.
- Afterwards, click OK.
- Now, if we double click on a cell in the Status column, we will see the entire row gets highlighted with blue color. We can also see the %Completed in cell H12.
- Then, we double click on several cells and we can see the fully functional to do list.
Read More: How to Create a Daily Task Sheet in Excel (3 Useful Methods)
Conclusion
Here, we tried to show you 4 methods to create fully functional to do list in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section. Please visit our website Exceldemy to explore more.
Related Articles
- Tracking Student Progress Excel Template (Free Download)
- How to Track Attendance in Excel (with Detailed Steps)
- Students Tracking Their Own Progress Template
- Excel Invoice Tracker (Format and Usage)
- How to Keep Track of Customer Orders in Excel (With Easy Steps)
- How to Keep Track of Clients in Excel (Download Free Template)