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.

Watch Video – Create a Daily Task Sheet in Excel

How to Create a Daily Task Sheet in Excel: 3 Effective Methods

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.

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…

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.

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.

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

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

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…

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.

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.

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

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.

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.

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

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.

Now, drag and move the checkbox inside your desired 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.

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

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.

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.

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.

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.

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…

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.

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. 👇

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.

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.

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.

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.

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

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….

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.

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. 👇

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.

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.

Create Daily Task Sheet.xlsm

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.

<< Go Back to Create a Tracker in Excel | Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

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

How to Create a Daily Task Sheet in Excel: 3 Effective Methods

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

2. Create a Daily Task Sheet with Checkbox

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

Points to Remember

Conclusion

Related Articles

Leave a reply Cancel reply