To-do List with Priorities using Excel

A while ago, we published a new year resolution template. This was a hit with our readers with thousands of you downloading it. During last week, Peppe, one of our readers from Italy, took this template and made it even more awesome.

The original template had tasks and completion check marks. As you finish each task, you can see overall progress too.

Peppe added priorities to this. With his new version, progress is measured based on how much priority we assigned that particular task. Pretty neat eh?!?

Personal Todo list with Priorities – Demo

First take a look at Peppe’s todo list.

How is this made?

Using lots of Excel goodness of course. The basic components of this todo list are,

1. Check boxes – to mark each activity as done (or not done)
2. Data validation – to assign priority (1 to 5) to each activity
3. Conditional Formatting – to highlight a row when the activity is marked as done
4. Thermo-meter chart – to show the progress as you mark each activity done
5. Formulas – to calculate % done based on how many activities are done & their priorities.

Since first 4 items are already explained on Chandoo.org, let me focus on the formula part.

Calculating % completion based on priorities:

To understand this problem, lets imagine, we have 5 tasks & priorities like below:

Step 1: Calculating weights

First step is to calculate how much weight each task should get. This is a simple job of inverting priority values (1/priority value). We will get this.

Step 2: Calculate weights to 100%

Next, we adjust the weights so that their total is 100%. To do this, we just divide a task’s weight by total of all task weights.

Step 3: Calculate % done only if a task is marked as done

Now, we just use TRUE / FALSE values generated by the check boxes to calculate % done. For this, we just need to multiply 100% weights with TRUE or FALSE values.

The total of this column gives us how much % of all tasks are done.

Note on weights for priorities

In this approach, we are assuming that doing one priority 1 task gives same output (%done) as doing two priority 2 tasks, three priority 3 tasks etc.

That means the weight enjoyed by priority 1 task is twice that of priority 2 task.

Some other possibilities are,

• Priority 1 is 1, 2 is 0.8, 3 is 0.6…
• A mapping table telling us how much each priority weighs

Read weighted averages in Excel to understand more.

Download this todo list template

Click here to download this template and chase that todo list in style. Examine the formulas in hidden column to understand this better.

Thank you Peppe

I find this template quite simple, yet powerful. It shows how much we can do with Excel by using a little creativity, simple features (conditional formatting, form controls etc.) and a some motivation.

Peppe, Thank you so much for sharing this with us.

If you enjoyed this todo list template, go ahead and say thanks to Peppe.

Also, use comments to share how you handle to dos & pending tasks using Excel. Share your tips & ideas with all of us.

Update

Over in the Chandoo.org Forums, Asshu has updated this witha VB Interface
Have a look and use if from: http://chandoo.org/forum/threads/to-do-list-vb-interface.28973/

More todo lists: Simple todo list in Excel, To do lists & Project Management