How to Create a Daily Task Sheet in Excel (3 Useful Methods)

At times, it is very necessary for us to create a daily task sheet.  Using Excel, we can create a daily task sheet very easily because it gives us the opportunity to automate and format conditionally in our daily task sheet. In this article, I will show you 3 methods to create a daily task sheet in Excel.


Download Practice Workbook

Here, you can download our daily task sheet template and practice creating a personal daily task sheet for yourself. You can also edit the task sheet according to your needs.


3 Effective Methods to Create a Daily Task Sheet in Excel

Here, in our dataset, we have prepared a daily task sheet table with necessary column headings. According to our dataset, we have written the task no., date and task. Now, we will fill up the priority and status of individual tasks using automation and conditional formatting. I have explained 3 useful examples below in this regard. Go through them carefully to create your own automated daily task sheet in Excel.


1. Create a Daily Task Sheet with Drop-Down List

You can prepare your daily task sheet by making a drop-down list using data validation. Follow the steps below to do these. 👇

Steps:

  • First, prepare your daily task sheet table with column headings. According to our dataset, we have written the task no., date and task. Now, we will write the priority and status of individual tasks using automation.

Daily Task Sheet Columns

  • Now, click on the cell where you want to fill up the priority of the task. Subsequently, Go to the Data tab >> Data Tools group >> Data Validation sub-group >> click on Data Validation…

Create a Daily Task Sheet with Drop-Down List

  • At this time, the Data Validation window will appear. Now, on the Settings tab, choose the List option from the Allow drop-down list. Next, write your priority list options that you want to select automatically in the Source text box. Separate every option by a comma(,). Click on the OK button.

Create a Daily Task Sheet with Drop-Down List

  •  Now, as you can see, on the selected E5 cell, we have a drop-down arrow just after the cell. And, if we click on the arrow, it shows us every priority option we have set. We can just click on the desired option and it will be put on the cell.

Fill Up Priority Cell Using Drop-Down List

  • Now, drag the fill handle to copy the data validation to all priority cells.

Copy Drop-Down List

  • Similarly, we can set another data validation on the Status column. Here, we will put the status options in the Source text box.

Automate Your Status Column Options

  • Now, upon the arrival of the priority options, you can think about making them colored for getting a better visualization of which is more important and which is less. For doing this, select the priority cells >> click on the Home tab >> click on Conditional Formatting >> click on New Rule…

Apply Conditional Formatting on the Priority Column Cells

  • Now, the New Formatting Rule window will appear. Choose the Format only cells that contain option from the Rule Type options. Subsequently, choose the Specific text option from the Format only cells with: drop-down list. Now, if you are setting the format for high priority, write High in the right side text box. Click on the Format button to set the formatting.

Set the Formatting Rule for Priority Column

  • At this time,  the Format window will appear. Go to the Fill tab of the window and choose the fill color you want to set. Click on the OK button.

Format Priority Column Cells with Color

  • Now, the New Formatting Rule window will appear again with a format preview. Click on the OK button.

Preview of the Formatting of the Priority Column

  • Similarly, you can set formats for medium and low priority tasks too.
  • Moreover, you can also set the format for the Status column. For doing this, select the status cells >> click on the Home tab >> click on Conditional Formatting >> click on New Rule…
  • From the New Formatting Rule window, choose Use a formula to determine which cells to format from the Rule Type options. Write equal sign (=) in the desired formula text box to put a formula. We are setting the format here for completed status. So, we would write the formula as if the selected cell contains the “Complete” value. Now, click on the Format button.

Set Conditional Formatting for the Status Column Cells

  • At this time, the Format Cells window will appear. Go to the Font tab and put the checkmark on the Strikethrough option. You can change the format for font color here too. You can also change the fill color from the Fill tab. Click on the Ok button.

Set the Format Style for the Status Column Cells

Thus, your daily task sheet is prepared with automation and customized conditional formatting. A complete daily task sheet would look like this. 👇

Create a Daily Task Sheet in Excel


2. Create a Daily Task Sheet with Checkbox

In this example, you will learn to create a daily task sheet using checkboxes. Follow the steps below to do so. 👇

Steps:

  • Follow the first eleven steps from the first example to automate and conditionally format the priority cells.
  • Now, here, we will not automate or write the status ourselves. Rather, we will add checkboxes in the Completed column cells. For doing this, select the Completed column cells >> Go to the Developer tab from the ribbon >> click on the Insert tool >> click on the Checkbox icon.

Insert Checkbox for Task Sheet

  • Now, drag and move the checkbox inside your desired cell.

Place Checkbox Properly in the Cell

  • Subsequently, repeat this process for all the other status cells. As a result, you will find checkboxes in all of the Completed column cells.

All Required Cells containing Checkbox

  • Now, if you want to remove the text Check, right-click on the checkbox and choose the Edit Text option from the context menu.

Remove the Text from the Checkbox

  • Now, delete all the text characters. Subsequently, repeat this process for every other checkbox. As a result, you will only get the checkbox icon now.

All Cells Carry Only the Checkbox without the Text

  • Now, if you want to add any formatting according to these checkboxes, you will have to extract the values for checkboxes first. For extracting the values, right-click on the checkbox and choose Format Control… from the context menu.

Define Checkbox Values

  • Now, the Format Control window will appear. Choose the Control tab from the window. Make sure the value is selected as Unchecked. Now, you will have to link the cell where the extracted value will appear. In our dataset, we have chosen the G5 cell for this. Click on the OK button.

Checkbox Value Linked Successfully

  • The checkbox will show FALSE when unchecked and show TRUE when checked. Subsequently, drag the fill handle to copy the format control through the cells below.

Copy the Checkbox Value Link for All the Cells Below

  • At this time, you will see every Completed column cell has a value beside the checkboxes. And all of the values are FALSE as they are unchecked.

  • Now, for better visualization of your task completion, you can format the completed tasks. For doing this, select the D5:G10 cells >> click on the Home tab >> click on Conditional Formatting >> click on New Rule…

Set the Rule for Formatting the Completed Cells

  • From the New Formatting Rule window, choose Use a formula to determine which cells to format from the Rule Type options. Write equal sign (=) in the desired formula text box to put a formula. We are setting the format here for completed status which is determined by the checked checkbox. So, we would write the formula as if the checkbox value cell returns the TRUE value. Now, click on the Format button.
  • At this time, just like the example 1, the Format Cells window will appear. Go to the Font tab and put the checkmark on the Strikethrough option. You can change the format for font color here too. You can also change the fill color from the Fill tab. Click on the Ok button.

Preview of the Format of the Completed Cells

Thus, your desired daily task sheet will be prepared with the checkboxes. If you click on the checkbox, the checkbox value would return TRUE and as per the conditional formatting, the task will face strikethrough with blue fill and white font color. The result sheet will look like this. 👇

Create a Daily Task Sheet in Excel Using Checkbox


3. Prepare a Double-Click-Enabled Daily Task Sheet

You can also just double-click on your Completed column cells to automate the complete status using VBA. Follow the steps below to do so. 👇

Steps:

  • Follow the first 11 steps from the first example to automate the task priority. Now, for completion status, we would use VBA and enable double-click to change the completion status. For doing this, Go to the Developer tab >> Visual Basic tool.

Enable VBA to Write Code

  • At this time, the Microsoft Visual Basic for Applications window will appear. Click on Sheet 3, as we are enabling the VBA in this worksheet. Write the following code in the newly appeared code window.

Write the Code in VBA Window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = False
    If Target.Row >= 5 And Target.Row <= 10 And Target.Column = 6 Then
    Cancel = True
        If Cells(Target.Row, Target.Column) <> Range("G5").Value Then
            Cells(Target.Row, Target.Column).Value = Range("G5").Value
        Else: Cells(Target.Row, Target.Column).Value = "No"
        End If
    End If
End Sub
  • Now, as there is VBA code, it is a must to save the workbook as Excel-Macro Enabled Workbook (.xlsm) type. Close and open the workbook again. Now, the macro is enabled in this workbook.

Prepare a Double-Click-Enabled Daily Task Sheet

  • As we have referred to the G5 cell value as the appearing value for double-click, write YES on the cell. Now, whenever you double-click on a cell, between row 5 and row 10 at column 6(Declared in our code), YES will appear and Another double-click will toggle the value to NO as per declared in the VBA code.

Prepare a Double-Click-Enabled Daily Task Sheet

  • Subsequently, fill all the cells by toggling double-click about the completion status.

Enable All cells with Desired Value

  • Now, you can apply conditional formatting here for the completed tasks. For doing this, select the cells >> go to the Home tab >> click on Conditional Formatting >> click on New Rule….

Prepare a Double-Click-Enabled Daily Task Sheet

  • Subsequently, select the Rule Type as Use a formula to determine which cells to format >> in the formula text box, write =F5=”YES” >> click on Format >> Check the Strikethrough option from the Font tab and use Fill tab to choose your desired fill color. Click on the OK button.

Prepare a Double-Click-Enabled Daily Task Sheet

Thus, your task sheet is now ready with the double-click toggling as your completion checker. Besides, you have the desired formatting for priority cells and completed cells. The task sheet will look like this. 👇

Create a Daily Task Sheet in Excel with Double-Click


Points to Remember

  • If you do not save the Excel workbook in .xlsm format, the macro will not be enabled.
  • When formatting conditionally using some formula, do not put the cell reference as absolute, if you need to copy the conditional formatting.

Conclusion

So, I have shown you briefly how to create a daily task sheet in Excel using 3 handy approaches with 3 practical examples. You can follow any of these ways to make your desired daily task sheet. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this.

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo