How to Build a Task Tracker in Excel with a Progress Bar

Step-by-step

A daily task tracker with drop-downs for priority and status, colour-coded priorities, live counts at the top and a progress bar that fills as you complete tasks. No macros and only one formula.

1. Make the sheet easy to read

Select the whole sheet. Set the row height to 20, the column width to 15 and the font size to 14, and align text middle-left.

2. Create the task table

In B7:F7 type the headers No., Task, Priority, Status and Notes. Select B7:F17, go to Insert > Table and tick My table has headers. Tables grow automatically and carry their formatting and formulas with them. Turn off gridlines from the View tab.

3. Style the table and name it

On Table Design, choose New Table Style: give the whole table a light grey outline and inside borders, and the header row bold text with a very light grey fill. Apply it. Then type Tasks in the Table Name box. Add a few example tasks.

4. Add a priority drop-down

Click just above the Priority header to select the column, then go to Data > Data Validation. Set Allow to List and type the source as High,Medium,Low.

5. Colour the priorities

With the column still selected, go to Conditional Formatting > New Rule > Format only cells that contain, set equal to High and pick a light red fill. Repeat for Medium (light blue) and Low (light green).

6. Add a status drop-down

Type Not started, In progress and Completed in B2, C2 and D2. Select the Status column, open Data Validation, choose List and point the source at B2:D2. If you later rename a status, the drop-down picks it up.

7. Count each status

Fill B2:B5, C2:C5 and D2:D5 with light red, blue and green, and merge B3:B5, C3:C5 and D3:D5. In B3:

=COUNTIF(Tasks[Status],B2)

It counts how many tasks in the table’s Status column match the word in B2. Copy it to C3 and D3, changing B2 to C2 and D2. Make the results bigger and change a few statuses to watch them update.

8. Build the progress bar

Select B2:D5 and insert a 100% Stacked Bar chart. In Select Data, click Switch Row/Column so the three statuses form one bar, order them Not started, In progress, Completed, and untick the extra axis labels. Set the gap width to 0, colour the pieces red, light blue and light green, then delete the title, axes and legend and remove the chart fill and border.

9. Number the tasks automatically

In the first No. cell (B8) enter =ROW()-7. The table copies it down, so new rows number themselves. Drag the table’s bottom-right handle to add more rows: drop-downs and colours come along. To track another project, copy the sheet with Move or Copy > Create a copy.