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,
- Check boxes – to mark each activity as done (or not done)
- Data validation – to assign priority (1 to 5) to each activity
- Conditional Formatting – to highlight a row when the activity is marked as done
- Thermo-meter chart – to show the progress as you mark each activity done
- 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
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.
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/
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Creating an Agenda template in Excel||3 upcoming courses to Make you awesome [Announcements] »|