- Excel Howtos, Templates
To-do List with Priorities using Excel
- Last updated on April 20, 2016
Chandoo
Share
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 Peppes 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 tasks 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: //chandoo.org/forum/threads/to-do-list-vb-interface.28973/
More todo lists: Simple todo list in Excel, To do lists & Project Management
Share this tip with your colleagues
Get FREE Excel + Power BI Tips
Simple, fun and useful emails, once per week.
Learn & be awesome.
- 63 Comments
- Ask a question or say something...
- Tagged under Charts and Graphs, checkbox, data validation, downloads, form controls, Learn Excel, Microsoft Excel Conditional Formatting, Microsoft Excel Formulas, reader contributions, screencasts, templates, thermometer charts, to do list, to do list with excel
- Category: Excel Howtos, Templates
Welcome to Chandoo.org
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.
Read my story FREE Excel tips book
Want an AWESOME
Excel Class?
Brenda
FREE Goodies for you...
From simple to complex, there is a formula for every occasion. Check out the list now.
Calendars, invoices, trackers and much more. All free, fun and fantastic.
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
Sorting values in Olympic Medal Table style [Quick Tip]
It is Olympic season. Everyone I know is tracking the games and checking their countrys performance. One thing that we notice when looking at medal tally is,
A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.
So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals [but no Gold].
How to use Date & Time values in Excel 10 + 3 tips
Getting Started with Power BI Sales Dashboard in one hour
6 Essential TEXT functions in Excel with 6 Everyday Examples
How to create a fully interactive Project Dashboard with Excel Tutorial
- Excel for beginners
- Advanced Excel Skills
- Excel Dashboards
- Complete guide to Pivot Tables
- Top 10 Excel Formulas
- Excel Shortcuts
- #Awesome Budget vs. Actual Chart
- 40+ VBA Examples
Related Tips
Sorting values in Olympic Medal Table style [Quick Tip]
How to use Date & Time values in Excel 10 + 3 tips
What is XLOOKUP? 13 formula examples to really understand it
How to embed Excel files, calculators on your website? Step by step instructions
Two-level Data Validation [Excel Trick]
Project Plan Gantt Chart with drill-down capability [Templates]
63 Responses to To-do List with Priorities using Excel
- Mario 8a says:January 7, 2013 at 8:55 am
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.//economiaemergente.com/Reply - Jason says:January 7, 2013 at 1:54 pm
EXCELLENTE!!!!
Reply - Rasheed says:January 7, 2013 at 3:24 pm
Needed .. thanks for sharing
Reply - New Year gifts from an Excel blog | vijayraghavendran says:January 7, 2013 at 4:33 pm
[...] To-do List with Priorities using Excel [...]
Reply - Gregg says:January 7, 2013 at 5:19 pm
Excellent spreadsheet. Nice work.
Reply - Jose Pedro says:January 7, 2013 at 5:28 pm
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.Reply- PEPPE says:January 7, 2013 at 11:35 pm
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
PeppeReply- Diane says:July 14, 2014 at 5:15 pm
Hi Peppe!
Thank you for this very useful excel spreadsheet!Reply
- Cauê Mendes Tavares says:January 7, 2013 at 5:36 pm
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êReply - Raghavan alias Saravanan M says:January 7, 2013 at 6:34 pm
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 ArabiaReply- Chandoo says:January 8, 2013 at 12:23 am
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.
- Link check box to a cell in an un-used column. Then hide that column.
- Link check box to a cell in a different sheet. Then hide that sheet.
- Link check box to a cell and then hide the cell contents by formatting it with custom code ;;; [more on this here: //chandoo.org/wp/2009/06/05/hide-cell/ ]
Reply
- Daniel says:January 7, 2013 at 6:37 pm
Dal Messico grazie tanti Peppe.
A great idea, thanks for shearing it with all of us.
DanielReply - Glen Gilchrist [@mrgpg] says:January 7, 2013 at 9:31 pm
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
GlenReply- Hui says:January 7, 2013 at 11:54 pm
@Glen
Chandoo uses Camtasia Studio to make the animated GIF's
You can read what else Chandoo uses here: //chandoo.org/wp/about/what-we-use/Reply- Glen Gilchrist [@mrgpg] says:January 8, 2013 at 5:58 pm
Cheers
I should have checked first.
GReply- Glen Gilchrist [@mrgpg] says:January 8, 2013 at 6:32 pm
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
GReply- Glen Gilchrist [@mrgpg] says:January 8, 2013 at 8:52 pm
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
- John says:January 7, 2013 at 9:31 pm
Raghavan
I justmake the font whitefor thecell linked to thecheckbox or if you have shading applied then font colour = shading so its there but is not seen or printed.
JohnReply - Utku says:January 7, 2013 at 11:08 pm
Excellent! Thank you very much.
Reply - Sully says:January 8, 2013 at 3:20 am
Excellent thanks!
Reply - Suresh says:January 8, 2013 at 6:36 am
Happy New Year.
Looks simple but excellent. Never knew you could do this without VBA.
Thanks PepeReply - Henrique de Albuquerque says:January 8, 2013 at 11:48 am
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?Reply - Benny says:January 8, 2013 at 12:47 pm
Come nella migliore tradizione:grandi ma semplici idde dall'Italia.
bravo PeppeReply - Vaslo says:January 8, 2013 at 6:01 pm
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!!!
Reply - Aparajita says:January 9, 2013 at 7:01 am
Thanks. really usuful. Will be waiting for such thing in future.
Reply - Juan says:January 11, 2013 at 4:41 pm
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
Reply- Chandoo says:January 13, 2013 at 1:27 pm
Hi Juan...
See this page for a tutorial on the chart - //chandoo.org/wp/2009/12/17/quick-thermometer-chart/
Reply
- Blog Posts of the Week [06th - 12th January 2013] - The South Asia MVP Blog - Site Home - TechNet Blogs says:January 14, 2013 at 11:02 am
[...] To-do List with Priorities using Excel [...]
Reply - DJ says:January 15, 2013 at 6:36 am
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?
-DJReply - tadovn says:January 17, 2013 at 5:43 am
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.Reply - Munir says:January 24, 2013 at 6:42 pm
Thank you Peppe & Chandoo for sharing an awesome idea.
Reply - Sara says:April 3, 2013 at 9:12 am
How do i increase the list ? I cant just drag down can I ? the check boxes perform the same way
Reply - RAVI XAVIER says:April 11, 2013 at 11:39 am
VERY EXCELLENT THANK YOU VERY MUCH.
Reply - Talia says:June 14, 2013 at 1:40 am
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...
Reply - Pradeep says:July 5, 2013 at 3:49 am
Thanks for this useful to do list.
I have the same question as TADOVN. This blog doesn't properly give instruction on how to add new task row. Following are my queries.
1] How do I add a new row?
2] If I copy paste the last row to create a new row, the check box get duplicated, i.e. if I click on the new check box on the new row, the previous check box also gets checked.So the simple question is.. how do I add a new row so that it behaves the same way as other rows?
Reply - Angela G. says:July 15, 2013 at 2:10 pm
Thank you very much! Great to do list template.
Reply - Celine says:August 27, 2013 at 4:40 am
Thanks for the template.
From an NGO organisation in Malaysia
Reply - Leah says:September 10, 2013 at 2:15 am
Will someone please answer the question about how to add additional rows to this list? I love it, but this is a fatal flaw, as I frequently have many more tasks.
Thank you!
Reply- Dennis says:October 28, 2013 at 11:32 pm
Below is how I added additional rows:
1] Select both columns H and N, right clicked, and clicked Unhide to reveal the formulas.
2] Select row 12 on the To Do List, copy it, and insert it below in the next row.
3] Change the 12 in cell C22 to a 13.
3] Drag your mouse and copy the formulas from cells I15, J15, and K15.
4] Paste the formulas below in cells I16, J16, and K16.
5] Right click on the check box in cell F22.
6] Click Format Control.
7] Click the Control tab.
8] In the Cell Link box, change the I15 to I16.
9] Repeat the steps above. [Change I16 in the Cell Link box to I17...I17 to I18, etc.]
10] If you are not seeing Format Control when you right click the check box, you need to make the Developer Tab available.Reply - Dennis says:October 29, 2013 at 1:19 am
Leah,
If you follow my previous instructions, you still may need to go back and change the formulas in column K. They calculate the priority weights and go in consecutive order as you go down the column:
IFERROR[1/E10,0]
IFERROR[1/E11,0]
IFERROR[1/E12,0], etc.Some of you who are more Excel savy may be able to figure out how to copy the formulas quicker. This is just the way I figured it out.
Reply- Leah says:October 29, 2013 at 1:59 am
Thank you Dennis; I will try that!
Reply
- Bandula says:October 24, 2013 at 2:24 pm
I am sure I would love this and it will help me to accomplice my tasks efficiently . Thanks Buddy
Reply - Sander says:November 5, 2013 at 1:10 pm
How would I be able to delete one of the row [not use 6 for example] so it won't calculate it with the progress?
Reply - Best of Chandoo.org 2013 | Chandoo.org - Learn Microsoft Excel Online says:December 31, 2013 at 9:29 am
[] 42,416] Angry Formulas game [Visitors: 36,392] Learn top 10 Excel features [Visitors: 25,723] To-do list with priorities Excel templates [Visitors: 19,947] Introduction to Power Pivot [Visitors: 21,298] Best new features in Excel 2013 []
Reply - FREE 2014 Calendar - Download and Print Year 2014 Calendar, Daily Planner & Event tracker today - Excel Calendar Template [2014] | Chandoo.org - Learn Microsoft Excel Online says:January 2, 2014 at 8:15 am
[] 2013 Calendar, 2012 Calendar, 2011 Calendar, New Year Resolution Tracker, Picture Calendar Template and Todo list template []
Reply - ????? ?? ??????, ????? ?? Excel:-] @ ProductivityBlog.com.ua says:January 5, 2014 at 5:24 pm
[] ?? ????? ??????????? ?????? ?????? ????? ? ????????????? ??? Excel. ??? ???? ????? ?? ??? []
Reply - Victor says:January 6, 2014 at 5:58 am
Thank you so much for this post. I took me a bit to figure out how the checkboxes link to the rest of the sheet, but now that I've got it I've created a new page for every day so I can track tasks going forward. I've also added work tasks side-by-side with personal tasks. Once I did that I also thought it would be neat to see how productive I am week over week so I added a nice summery page. The summary builds on the percentage completion for personal and work tasks.
Love this template - so versatile and yet simple.
My next project is to get standard weighting for certain tasks so I don't have to keep remembering them.
Cheers,
Victor
Reply - Jeff Carlsen says:January 6, 2014 at 9:31 pm
I like this template. I may modify how the checkboxes work though for a couple reasons:
1] It's a pain to add more rows. If I want to add 10 more rows, it appears that I have to re-point each new object to the appropriate link-cell. Otherwise, they all point back to the copied row - checking one causes all of them to check.
2] I can't group and collapse rows in the checklist without all the objects stacking together and remaining visible in the lowest non-collapsed row. With a simple "x", this would be ok.
One solution would be to have a simple "x" instead of a checkbox object. I could just use an "x" to mark complete, and make the TRUE/FALSE based on an If formula [If "x" then TRUE; otherwise FALSE].
Reply - Kris says:January 17, 2014 at 6:34 am
I downloaded the file, but it is a ZIP file with several subfolders and xml files. There is no workbook here. How do I open this in Excel?
thank you for the help and excellent ideas you share.
Reply- Hui... says:January 17, 2014 at 2:54 pm
@Kris
Yes, Excel files are special Zip files that actually contain a number of files including your data
If the file opens like that save it locally as a *.zip file and rename it to a *.xlsx fileOpen with excel normally
Reply
- s.f says:January 22, 2014 at 7:32 pm
How do you change the color when it is completed....I have multiple companies and need to color code this template.
Thank you.
Reply- Hui... says:January 24, 2014 at 2:09 am
@S.F
Use Conditional FormattingReply
- Callie says:May 20, 2014 at 3:28 pm
Hello! I have added additional rows, fixed it so that the check boxes work individually, AND made it so that the #% changes when each box is checked -- however the status bar won't move past the midway mark.
Any ideas on how I can get the progress bar to fill up the entire way once the list is complete?
Reply- Cheryl says:July 27, 2014 at 9:40 pm
If you right click the status bar, select 'Select Data' and go to 'Chart Data Range' and revise to include your expanded range. The bar chart colors may default to a predefined style. Right click the chart to reformat the Chart Area.
Reply - Cheryl says:July 27, 2014 at 10:08 pm
Or, to change the bar colors, I populated all rows w/ activities and rank and then left clicked the bar chart color that I wanted to change - went up to the ribbon under the home tab, selected the new bar color from the fill color dropdown.
Reply
- Cheryl says:July 27, 2014 at 8:30 pm
Love the instant gratification of the status bar! Genius!
Reply - Frank says:February 8, 2015 at 11:01 pm
Thank you so much, what a great tool! God bless you for doing this for free!!
Reply - rahul saldanha says:May 30, 2015 at 11:35 am
Awesome
Nice to show power of excel.Reply - Hui... says:April 20, 2016 at 7:53 am
Over in the Chandoo.org Forums, Asshu has updated this witha VB Interface
Have a look and use if from: //chandoo.org/forum/threads/to-do-list-vb-interface.28973/
Reply - Chirag Raval says:May 4, 2017 at 12:07 pm
Dear All,
There are good job done here & its very helpful for all.
God Bless You to you all for your valuable working.
Regards,
ChiragReply - Jake says:March 19, 2018 at 4:12 am
Hi guys,
I've added additional rows, but the percentages in the thermo-meter don't reflect this when the boxes are checked. I'm lost with how to change this, so any assistance would be greatly appreciated!
Jake
Reply- Hui... says:March 19, 2018 at 6:00 am
@Jake
Can you please ask the question on the Chandoo.org Forums
//chandoo.org/forum/Please attach a sample file to simplify the solution
Reply
- Peggy Wong says:February 29, 2020 at 2:00 am
Hi Chandoo, how you do it for all this check list. it is using Excel VBA, I am not good it that.. still leaning part. and I was trying to figure out. Trying to understand all vba code and meaning and when I use which code.
do you have any guide line on this, i mean. Exp: dim is what, string etc:
for all this checking list does need to use VBA?
Thankyou
PeggyReply
Leave a Reply
Name [required]
Mail [will not be published] [required]
Website
Notify me of when new comments are posted via e-mail
Δ