SubscribeTo-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,
- 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
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.
More todo lists: Simple todo list in Excel, To do lists & Project Management
| ||||
|
| ||||
|
Leave a Reply
 |
Creating an Agenda template in Excel | 3 upcoming courses to Make you awesome [Announcements] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
33 Responses to “To-do List with Priorities using Excel”
Very useful, you always give us good ideas for our excel files. Thanks
I’ve been working on calendars leagues. If you must watch a bit on my blog. http://economiaemergente.com/
EXCELLENTE!!!!
Needed .. thanks for sharing
[...] To-do List with Priorities using Excel [...]
Excellent spreadsheet. Nice work.
Ciao Peppe!, Tante grazie per compartire il tuo eccellentissimo lavoro in Excel. Tu hai a web blog? – Grazie Chandoo per la publicazione.
Hello Peppe, Thank you so much for sharing your most excellent work in Excel. Have you a web blog? – Thanks Chandoo for publication.
Hi Jose,
tanks for your appreciations and tks to Chandoo for publishing
my little job. it’s a pleasure for me to be mentioned on my guru’s blog.
Just to reply to Jose, I don’t have a blog, but if you want to share some ideas or need some help don’t hesitate to contact me also on twitter like @peppinogreco.
Regards
Peppe
Great!
I’ve learned a little bit of VBA during the last year, and get addicted to it, but sometimes, it makes us forget how powerful excel is, without macros.
Nice post!
Cauê
Hi Chandru,
A very good post. Though I had been reading your posts for a longer time, did not post any questions so far except for wishing and appreciating.
I have a question here. I had attempted to do something on my own (a little R & D) on the new year resolution template itself. However, I could not do it fully. Thankfully, you had provided the link for each step , which was exactly what I was looking for
I had done with the check boxes and also conditional formatting. I am glad indeed. I am able to highlight a row when a check box is checked. However, the value of the checkbox gets printed in the same cell which it was linked to. How can I avoid it ? I could not see it in the sample excel files you had provided.
I appreciate your help in this.
Cheers,
Raghavan alias Saravanan M
Jeddah | Kingdom of Saudi Arabia
Hi Raghavan… Thanks for your comments and I am glad you are trying to build this on your own. There is no way we can avoid printing the check box value in linked cell. If you do not want to see “TRUE or FALSE” in a cell, you do one of the following.
Dal Messico grazie tanti Peppe.
A great idea, thanks for shearing it with all of us.
Daniel
Lovely idea – downloading now.
What software is used to create the animated gif of the template in action? Love to replicate to simple demos on my site.
Cheers
Glen
@Glen
Chandoo uses Camtasia Studio to make the animated GIF’s
You can read what else Chandoo uses here: http://chandoo.org/wp/about/what-we-use/
Cheers
I should have checked first.
G
Still defeated.
What I am looking for is the TORN edge effect as applied to the screen capture. I can see how to do this for captured images, not vidoes.
I wonder if they are post processed in some way?
Cheers
G
Sorted.
Capture white screen with torn paper edge with SnagIT
Make the inner of border transparent (Photoshop)
Add the image as an overlay in Camtasia.
Sorry to hijack an Excel thread with this – its been bugging me for a while.
G
Raghavan
I just make the font white for the cell linked to the checkbox or if you have shading applied then font colour = shading so its there but is not seen or printed.
John
Excellent! Thank you very much.
Excellent thanks!
Happy New Year.
Looks simple but excellent. Never knew you could do this without VBA.
Thanks Pepe
good day,
Please, how can I create a chart with scroll bar that is also dynamic in PPT.
I created the chart in Excel, but I need this information to be presented dynamically in powerpoint and when I put the bar rolls loses functionality. please can you help me?
Come nella migliore tradizione:grandi ma semplici idde dall’Italia.
bravo Peppe
This was outstanding. I have had two bosses give me to-do lists that I was very unhappy with. I went and added 15 more lines to this and it was really easy to so with a little reformatting and changing some links. THANKS!!!
Thanks. really usuful. Will be waiting for such thing in future.
Great tutorial! It would be interesting if someone could explain how to do the chart with detail: how to insert the values of the horizontal axis, to create the horizontal bar (the outlines) and the bar itself, etc
Hi Juan…
See this page for a tutorial on the chart – http://chandoo.org/wp/2009/12/17/quick-thermometer-chart/
[...] To-do List with Priorities using Excel [...]
Good Concept!
Downloaded it but, my Excel 2007 hangs and I have to recover it few times. Finally it opens but, everything is distorted.
Am I doing something wrong?
-DJ
Interesting idea.
You give e new way to track my actual planning.
But instead of using thermometer in this case, we can use a simple bar chart , with data is the total done.
Reasoning for that, with thermometer, you have to format all the small part of data with the same color. If you have more than 10 parts, it will take your time to finish.
I tested and it shown the same.
I’m searching for How to automatically add check box link to a new cells when we add new item?
Thanks for your interesting idea.
Thank you Peppe & Chandoo for sharing an awesome idea.
How do i increase the list ? I cant just drag down can I ? the check boxes perform the same way
VERY EXCELLENT THANK YOU VERY MUCH.
How do you increase the list? Formatting of the check boxes and shading etc does not copy correctly if using copy and paste or dragging cells down…