Automated Task Tracker: Built within minutes
Master the art of automation! In this Excel Tutorial, we build a professional Automated Task Tracker from scratch. Learn how to use VBA for a double-click checkmark feature, create a dynamic Progress Bar, and use Slicers to filter like a pro. Perfect for aspiring data analysts and productivity nerds! 🚀
Timestamps:
0:00 - Intro: What we’re building
0:33 - Create the Table: tblTask
1:18 - Dropdown list for Category and Priority
1:50 - New Task Button
3:03 - Double Click to Checkmark
3:45 - Status Column
3:56 - Conditional Formatting: Light Gray
4:27 - Conditional Formatting: Strikethrough
4:46 - Conditional Formatting: High Priority
5:03 - Task Count
5:19 - Completion Rate
6:08 - Add Slicers
6:30 - Final Step: Save as .XLSM
Download working file: https://1drv.ms/x/c/19685f0b767e7ee0/...
VBA Code:
----------------------------------------------------------------------
' New Task Button Click
Sub btnNewTask_Click()
' Get the Table: tblTask
Dim tbl As ListObject
Set tbl = SheetTaskList.ListObjects("tblTask")
' Add a new row (at the bottom) to tblTask
Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
' Select the cell under "Task Name" ready for user input
newRow.Range(tbl.ListColumns("Task Name").Index).Select
End Sub
----------------------------------------------------------------------
' Column Done: Double Click Event
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Only work in Column 1 (Column A)
If Target.Column = 1 Then
' Stop Excel from entering "Edit Mode"
Cancel = True
' Toggle the checkmark: "a" or empty string
If Target.Value = "a" Then
Target.Value = ""
Else
Target.Value = "a"
End If
End If
End Sub
----------------------------------------------------------------------
Formula:
Status Column: =IF([@Done]="a", "✅ Finished", "⏳ In Progress")
Task Count: D4: =COUNTA(tblTask[Task Name])
Completion Rate: F4: =IFERROR(COUNTIF(tblTask[Done], "a") / D4, 0)
Conditional Formatting:
Highlight Gray =$A7="a"
Strikethrough: =$A7="a"
High Priority: =$D7="High"