#263-How to make to do list in Excel | How to create Checklist in Excel

How to make to do list in Excel | How to create Checklist in Excel

Create Setup Sheet

Step-1

1. First create a new workbook and save it as Daily To Do List.
2. Rename Sheet1 as Setup.
3. Create another sheet and rename it as To Do List.

Step-2

1. Go to Setup sheet. Change the height of row 1 to 75.
2. Change the width of column A to 2.3.
3. Select the cell range from A1:L1, and color it as Green, Accent 6.
4. Now insert arrow shape named Arrow:Pentagon from Insert►Illustrations►Shapes►Block Arrows►Arrow:Pentagon.
5. Now rotate the the shape to 90° and also the size of shape so that it looks good.
6. Then place the shape in cell B1, and again change size of the shape as required.
7. Then change the shape color to Green, Accent 6, and border color to white and border weight to 1.5 point.
8. Then on the top of the shape place a Setting icon, so that it look professional.
9. Select the shape & icon image and Right-Click on them and select Format Object from the context menu. Then Format Picture pane appears on the right hand side, select the Size & Properties icon and then click properties option and select Don't move or size with cells.
10. Select cell D1, Type Daily To Do List Setup, Format text as Font:Broadway, Size:20.

Step-3

1. Select the columns B, D & F and change column width to 17 and again select columns C & E and change the column width to 4.
2. Select B4 and type Category, select D4 and type Assigned To and again select F4 and type Priority.
3. Select B4:B5, go to Insert tab►Table. Select My table as header and click OK.
4. In the same way do it for Assign To and Priority.
5. Now select all the table and go to Design tab and click the arrow in the table style group and select Green, Table style medium 7.
6. Now type values under Category as Payment, Cleaning, Shopping, Tuition etc. (or as you wish).
7. Type name of person in the person table.
8. Type High, Medium and Low in Priority table.
9. Create Named range for all tables. In Category table select all the values except heading, then go to Formula tab►Name Manager. In the Name Manager dialog box click New, then in the name box type Category, and click OK. Category Named Range is created.
10. In this way create two more named range for Person and Priority table. (See the video).

Step-4

1. Insert a rounded rectangle shape Insert► Illustrations►Shapes ►Rectangle ►Rectangle: Rounded Corner.
2. Select the shape and change the format from Format tab ►Shape Style to Intense Effect - Green, Ascent 6.
3. Insert another icon image of clipboard and resize it fit the shape. 
4. Type To Do List in the shape. Then move the text to right side with Increase Indent option from Home tab.
5. Select the clipboard icon and shape and group it. Then insert a hyperlink to To Do List sheet.
6. Right-Click on the group and select Link and select To Do List sheet from the dialog box and click OK.
7. Setup sheet is complete.

Create To Do List Sheet

Step-1

1. Go to To Do List sheet. Change the height of row 1 to 75.
2. Change the width of column A to 2.3.
3. Select the cell range from A1:O1, and color it as Green, Accent 6.
4. Now insert arrow shape named Arrow:Pentagon from Insert►Illustrations►Shapes►Block Arrows►Arrow:Pentagon.
5. Now rotate the the shape to 90° and also the size of shape so that it looks good.
6. Then place the shape in cell B1, and again change size of the shape as required.
7. Then change the shape color to Green, Accent 6, and border color to white and border weight to 1.5 point.
8. Then on the top of the shape place a Clipboard icon, so that it look professional.
9. Select the shape & icon image and Right-Click on them and select Format Object from the context menu. Then Format Picture pane appears on the right hand side, select the Size & Properties icon and then click properties option and select Don't move or size with cells.
10. Select cell D1, Type Daily To Do List, Format text as Font:Broadway, Size:20.
12. Insert a rounded rectangle shape Insert► Illustrations►Shapes ►Rectangle ►Rectangle: Rounded Corner.
13. Select the shape and change the format from Format tab ►Shape Style to Intense Effect - Green, Ascent 6.
14. Insert another icon image of Setting and resize it fit the shape. 
15. Type SETUP in the shape. Then move the text to right side with Increase Indent option from Home tab.
16. Select the Setting icon and shape and group it. Then insert a hyperlink to Setup sheet.
17. Right-Click on the group and select Link and select Setup sheet from the dialog box and click OK.

Step-2

1. Now we have to create a table heading from B4 to L4. So start type from B1, Task Description, Task Category, Priority, Assigned Date, Due Date, Assigned To, Start Date, Complete Date, Status, Days Remaining and Notes.
2. Now Select from B4:L5 and go to Insert tab►Tables, Select My table has headers, and click OK.
3. Resize the columns from B to L as required.
4. Now create drop down list for Category, Priority and Assigned To column. Select the whole column of category, leave the heading, then go to Data tab►Data Validation. In the Data Validation dialog box, in the Allow box select List and in Source box, press F3 and select Category named range. Then click OK.
5. In the same way create drop down list for Priority and Assigned To Column.
6. Now insert the data as shown in the image below.

Step-3

Now in the Status column we need to calculate the status of the task based on Assigned Date, Due Date, Start Date and Complete Date. Name of all the status are Overdue, Due Today, In Progress, Not Started, Complete and Cancelled. The logics for all the status are:   

1. if Assigned Date is blank or Due Date is blank then the result will blank,
2. if Complete Date is blank, Start Date is not blank and Due Date is less than today's date then Overdue,
3. if Complete Date is blank, Start Date is not blank and Due Date is equal to today's date then Due Today,
4. if Complete Date is blank, Start Date is not blank and Due Date is greater than today's date then In Progress,
5. if Complete Date is blank, Start Date is blank and Due Date is greater than today's date then Not Started,
6. if Complete Date is not blank and Start Date is not blank then Complete,
7. if Start Date is blank and Due Date is less than today's date then Cancelled    

 Now select the whole column Status except the heading then type the formula as shown below and press Ctrl+Enter.
 =IF(OR([@[Assigned Date]]="",[@[Due Date]]=""),"",IF(AND([@[Complete Date]]="",[@[Start Date]]<>"",[@[Due Date]]<TODAY()),"Overdue",IF(AND([@[Complete Date]]="",[@[Start Date]]<>"",[@[Due Date]]=TODAY()),"Due Today",IF(AND([@[Complete Date]]="",[@[Start Date]]<>"",[@[Due Date]]>TODAY()),"In Progress",IF(AND([@[Complete Date]]="",[@[Start Date]]="",[@[Due Date]]>TODAY()),"Not Started",IF(AND([@[Complete Date]]<>"",[@[Start Date]]<>""),"Complete",IF(AND([@[Start Date]]="",[@[Due Date]]<TODAY()),"Cancelled","")))))))

Step-4

Now in the Days Remaining column we need to calculate days remains to complete the task according to Due Date or how many days crossed from the Due Date. So the logic for the formula is 

1. If Assign Date or Due Date is blank then Blank Result.
2. If Status is Completed or Cancelled then Blank Result.
3. Otherwise in every case Due Date - Today's' Date.
4. If result comes with positive number then that number of days is remaining and if negative then that number of days is overdue.

Now select the whole column of Days Remaining except the heading then type the formula as shown below and press Ctrl+Enter.

=IF(OR([@[Assigned Date]]="",[@[Due Date]]=""),"",IF(OR([@Status]="Complete",[@Status]="Cancelled"),"",[@[Due Date]]-TODAY()))

Now we need to custom format the column. select the whole column of Days Remaining except the heading then Right-Click on the column and select Format Cells.

Then in the dialog box, select the Number tab and select Custom from the Category list. Then in the Type box type :

0 "Day Remain";0 "Day Overdue";;

and click OK.

Step-5

Now we need to format the table and for that we will use Conditional Formatting. The conditions are

1. If Status is Complete then text will be Orange color.
2. if Status is Cancelled then text will be red in color with strikethrough.

Condition for Complete Task

So first select the table body and go to Home tab►Conditional Formatting►New. Then in the New Formatting Rule dialog box, select Use a formula to determine which cell to format. Then in the formula box type =$J5="Complete" and click Format then select the text color as Orange, Ascent 2 and text style as Bold. Then click Ok, Ok, Ok.

Condition for Cancelled Task

So first select the table body and go to Home tab►Conditional Formatting►New. Then in the New Formatting Rule dialog box, select Use a formula to determine which cell to format. Then in the formula box type =$J5="Cancelled" and click Format then select the text color as Dark Red and text style as Bold and effect as Strikethrough. Then click Ok, Ok, Ok.

Step-6

Now we will create Task Board for the To Do List table. In this board we can see the number of task based on status, that is thi board will total number of Not Started task, incomplete task, due today task, completed task, Overdue task and cancelled task. So first we need to calculate the number of task based on status.

1. In Setup sheet, select cell M6 and type Not Started, in N6 type In Progress, in O6 type Due Today, in P6 type Complete, in Q6 type Overdue, in R6 type Cancelled.
2. In Cell M5, type =COUNTIF(Table24[[Status]:[Status]],M6)  (NB:Table24 is name of the table, if you have different table name replace it with that.)
3. Select cell M5, and drag the fill handle till R5. You will get the value of all status.
4. In To Do List sheet create a new shape Rectangle: Top Corners Rounded and flip it vertically.
5. Resize the shape and position it correct place so that it looks good. (See Video)
6. Fill the shape with white color and remove the border.
7. Insert 6 text boxes and type the 6 status name as Not Started, In Progress, Due Today, Complete, Overdue, Cancelled and format them (See Video)
8. Insert a vertical line with autoshape between the text boxes.
9. Create 6 circle shape and place them under each text box. Format them with different colors.
10. Now select first circle under Not Started text box, then click on formula bar and go to Setup sheet and select the cell M5 and press Enter. Do it for all the circles below each text box.
11. Position all the objects of the Task Board so that it looks good.
12. Now select all text boxes, all vertical lines, all circles and the white shape and group them.

Step-7

Now we will create progress chart for the table. 

1. Go to Setup sheet, select M7 and type the formula =L4/SUM($L$4:$Q$4) press Enter.
2. Again select M7 and drag the fill handle till R7 .
3. Select M6:R7 and go to Insert tab►Insert Column or Bar Chart►2D Bar►Stacked Bar
4. Go to Design►Switch Row / Column 
5. Right-Click on Horizontal (Value) Axis and click Format Axis. Format Axis pane appears.
6. In the Maximum box type 1.
7. Remove all the chart object except the bars. Remove the background of the chart area.
8. Right click on the bars and click Format Data Series. Format Data Series pane appears.
9. Reduce the Gap Width to 0 and Reduce the height of the chart so that it looks good.
10. Select the Chart and go to Design►Move Chart Location. In the dialog box select To Do List from the drop down list of Object in:
11. Reduce the chart height and Position the chart with in row 2 an 3. Also increase the width of the chart till column L .
12. Select the chart and click on the Plus sign on the top right corner of the chart and select Data Labels.
13. Right click on the value inside the bar and select Format Data Labels. On pane check Series Name and uncheck Value.
14. You can also the the bar color and format the data label text.

Step-8

Now we will insert slicer so that we can filter the data in the table based on Category, Priority and Status .

1. Select any cell with in the table.
2. Go to Insert tab►Filters Group►Slicer
3. In the Insert Slicer dialog box select Task Category, Priority and Status and click OK.
4. Now resize the slicers and place them on the right hand side of the table.
5. Now click on the options of the slicer you can the data of the table get filtered.

Now everything is done, if you face any problem you can also see the video and you can also write to us in the comment section.