Excel Template: rusinfomos.ru Homework Tracker

by Matthew Kuo on October 2, 2012

in Excel, Excel Templates

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Free Excel Template Download

When I started as an MBA student at UCLA Anderson, I was surprised and eventually overwhelmed by the number of responsibilities I had to manage.  Multi-tasking was something I had always considered a skill of mine, but I soon realized that I couldn’t track all of my tasks just by memory.

For this reason, I built a homework and task tracker using Excel to track all of my commitments during fall quarter.  I have been using it ever since and have made several revisions to the tool.

Tracker Design

There were a couple of key themes that went into the design of this tool.  The first of which is having vertical lists for all of your classes.  The reason for this is fairly simple – during your first quarter you will fall behind in your classes.  Even those of us who are used to getting straight A’s in our undergraduate courses will have difficulty balancing school work with networking, recruiting, social events, and club leadership positions.

Therefore, it’s not uncommon to fall several readings or assignments behind for a given course.  The remedy for this situation is always the same – the night before everything is due, or right before a midterm, you’ll go through each deferred task in sequence until you are caught up with the syllabus.  The vertical format is perfect for this.

Another key theme is the process of checking and crossing off items.  While it may seem like a minor attribute, there’s something fulfilling about checking a box or crossing out an item once you’ve completed a task.  The Homework Tracker template replicates both of these processes in a digital format.

Another goal of mine was to make the tool simple and intuitive.  There are only two tabs where you actually input data and all input cells are highlighted in yellow.  The tracker was built on a series of lookup formulas and significant conditional formatting.  Please follow the Excel category on my website if you’d like additional information on those subjects.

Click here to download the rusinfomos.ru Homework Tracker

The next section is a basic tutorial on how to use this template.  It covers each tab by going from left to right through the workbook.

The Setup Tab

Start using the template by going to the Setup tab.  Once there, all the cells you can update are highlighted in yellow.  The process of populating this tab is fairly simple:

  • Input your name
  • Under the “Class Name” field, input all of the courses and commitments you’ve made for the quarter/semester (these could be club leadership positions, TA positions, recruiting, group projects, etc.).

If you have more than 8 total commitments, I would suggest combining some of the less intense commitments into one entry.  If you have fewer than 8 commitments, input the word “(blank)” or some other generic text in your unused slots.  This affects the conditional formatting of the Calendar View.

Each class / commitment (outside of the blank ones) must have a unique name for the template to work properly.  For these inputs, it’s best visually to use an abbreviation.  For example, Economics could be ECON; Accounting could be ACCTG.


Updated: the next two sections discuss two features that were recently added to the 2.0 version of this tracker.

Baseline Year

Per some of the comments from users of this tracker, I adjusted the template so that you could shift between years more quickly.  The original template required you to start in 2012 and manually click your way through the years in the calendar view.  Now all you need to do to reset the template is select a baseline year.  The template will use the first Sunday of that year as the starting point of the calendar view.

In the example below, we’ve picked the year “2019” and the calendar view has been manually shifted by 2 weeks (with the up and down arrows), so the calendar view would be showing the third full week of 2019.

No Password Protection

When I built the first version of the homework tracker, I collaborated with a friend and put a lot of time into creating something that we believed would help others.  We wanted to receive credit for our contribution and for that reason, we decided to protect the file with a password.  (which ironically, neither of us can remember today)

The problem with this approach is that, it’s now causing problems rather than helping people.  Had there been no password protection, I’m sure the average Excel user could have figured out the problem and addressed it.  Additionally, it goes against the principle of transparency I’ve discussed in my model building posts.  Therefore, this latest version of the tool will not have password protection.

Please note that since the password protection has been removed, it’s much easier to make an entry error.  Because of this, I’ve protected the “Calendar View” tab (without a password) as that tab is primarily view only.


The Master List Tab

Now that you’ve setup the foundation of the tracker, you must input your relevant individual tasks.  The best time to do this is right at the beginning of your classes.  What I’ll usually do before I start a quarter is sit down with each course syllabus for all of my classes and use them to populate this template.  The update process is as follows:

  • Enter a task name for every task
  • Enter a task due date
  • When you finish a task, enter “Shift + P” in the checkmark column to cross that task off
  • To highlight an important task, enter “Shift + N” in the checkmark column to highlight it in yellow and bold the text

The template allows a maximum of 200 tasks for a given class / commitment.  How many rows you use will depend on how much detail you want for your tasks (i.e. listing five readings due in one day as one assignment versus breaking each reading out individually).  Overdue tasks and tasks without a date will be highlighted in red.

After completing you initial input, you’ll likely need to add additional tasks that are due in the middle of your entries.  To add data and re-sort your entries:

  • Append your new task after the last task you filled out
  • Select any entry under the “due date” field that is part of your congruent data set
  • Input “Alt – A – S – A” to re-sort your tasks
  • You can also sort manually by going to the menu and selecting “Data – Sort – Sort By: Due Date – Order: Oldest to Newest
  • Your entries will now be chronologically arranged

For the sort function shortcut to work, you need a congruent data set, meaning you can’t have empty rows separating your data.

Finally, a couple of things you should not do on this tab:

  • Do NOT try to move or shift rows to sort them when incorporating new tasks.  Doing this will break formulas that feed into the Calendar View.  Please use the sort process described above.
  • Do NOT edit the top row of the Master List tab

The Calendar View

The Calendar View is primarily an output tab designed to show you a weekly view of the tasks you entered into the Master List tab.  The only thing you can change on this tab is the week displayed; you do so by pushing the spin button up or down.

The Calendar View basically shows you when you might get slammed with work and encourages you to prepare accordingly.  During the recruiting season, it wasn’t uncommon for me to have multiple interviews, a midterm, and a group assignment all due in one day.  A maximum of 12 tasks will appear for a given day; if you happen to have more than 12 tasks due, an overflow indicator ( ! ) will appear at the very bottom of the corresponding day.

The Calendar View is also formatted to be print ready, so you can print a copy out at the beginning of the week to help plan out your schedule.

Technical Issues

Below are a list of known technical issues with the template:

  • The rusinfomos.ru Homework Tracker was built using Excel 2010.  Opening the file with an earlier version of Excel may prevent some formatting features from working.
  • There are no macros in this workbook nor links to other workbooks
  • A circular reference warning may pop up when using this file.  There are no circular references in this model and this is a known bug in Excel.
  • I designed the tool as “offline-only” as each tracker will primarily be used by one person.  I considered using Google Docs for this tracker, but currently Google’s version of Excel has nowhere near the conditional formatting capabilities that Excel offers.

Please email me with any comments or suggestions for future versions.  Due to contractual agreements, I am not able to give out the unlock password for the spreadsheets.

Click here to download the rusinfomos.ru Homework Tracker

{ 32 comments… read them below or add one }

dana February 23, 2013 at 9:54 am

PERFECT!!!!! Exactly what I was looking for!!!! thank you!

Reply

Alys February 25, 2013 at 10:35 am

Holy cow! Thank you! I had a family tragedy a couple weeks ago, and it really threw me off in school. I have been incredibly overwhelmed with being behind because it seemed like I had SO much to make up. After putting all my assignments and such in and seeing the calendar, I feel a lot better. I have a ton of work to do in the next few days, but having things organized helps me see the light at the end of the tunnel. Thanks for saving my GPA!

Reply

Matthew February 26, 2013 at 4:17 am

Glad I could help. Hope things get better for you.

Reply

Maaike March 16, 2013 at 4:41 pm

Wow, this is super helpful! Is there also a possibility to include a monthly overview, so it is easier to see which weeks have the highest workload etc.?

Reply

Matthew March 16, 2013 at 7:51 pm

Thanks for the suggestion. I’ll include that in the next version.

Best,
Matthew

Reply

Marcos October 15, 2013 at 9:02 am

Hi Matthew, this is awesome, thanks! quick question, i’m trying to put in some tasks for 2014 however the date defaults to 2013, how can I change this?

Thanks again

Reply

Blake October 16, 2013 at 2:18 pm

I downloaded the tracker. I have followed the instructions, but for some reason i can’t get anything to populate in the Calendar view. Can you help me troubleshoot this issue?

Reply

Benjamin October 31, 2013 at 10:58 pm

I have filled in all the things required but the calender will not fill in the tasks for me. How do I fix this???

Reply

Mark Miller June 1, 2014 at 12:21 pm

Thank you for the spreadsheet. It works very well. I am able to sort by date properly. I cannot however use the “Shift-N” or “Shift-S” key to mark the task completed or highlight and bold the item. Do you have any suggestions?

Thank you.

Reply

Mark Miller June 1, 2014 at 12:23 pm

I meant “Shift + P”, not “Shift + S”.

Mark

Reply

Omar October 13, 2014 at 2:12 pm

I just downloaded the template, and it’s fantastically great. 🙂
I would love too see monthly view in the next version because it really helps to see the your schedule ahead of time. Please include monthly view in the next version.
And if possible, would we be able to know the release date? Thank you bunch. Really!

Have a good one.

Reply

Prue April 3, 2015 at 8:54 pm

This is a really awesome resource, Thank you.

I am actually using it to coordinate an MBA project, where a team of 4 each put their contribution (current and pending) in the Master list: The Calendar view for each week is the submitted as our Summary of activity.

I understand the need for protecting the Worksheets, both from the view point of ensuring functionality is not broken by edits, and that of protecting branding and copyright, but it would be nice to be able to at least reorder the tabs? It is usually the case that the setup/data tab if not hidden is last rather than first in a shared spreadsheet.

Other than that I see a product that with a bit of tweaking I would happily pay for. Thanks again

Reply

Katia June 10, 2015 at 12:10 pm

THANK YOU!!!! This tool is awesome !!!!!

Reply

A N Sharma July 30, 2015 at 4:02 am

Sir
This is a really a good resource, Thank you.
I am also learning so much MS Excel spreadsheet programs.
particularly formulas and their functioning.
Thank you very much.

Reply

Alexandra August 19, 2015 at 4:47 am

In Calendar view sheet, why the class names don’t appear in colors (green, blu…)?

Reply

Nate August 22, 2015 at 10:13 am

Amazing! Just what I needed for grad school. However, I wonder if as a quick update you could change the the starting date from 2012 to 2015? It’s not a big deal but its a lot of clicking to bring it up to date since you can’t change the cell manually.

Reply

Sarah August 27, 2015 at 10:31 am

Downloaded this today (8/27/15) and this is perfect except that the calendar view starts on 6/3/12. Can’t change/fix this since it’s protected, which I get, but this one thing makes this sheet unuseable for me. I was specifically looking for a month view for my assignments. Everything else is gravy. Is there an updated version somewhere?

Reply

Maria November 20, 2015 at 6:09 pm

Hello, I modified this excel template to use as my homeschool lesson planner – it includes a monthly view – email me at if you wish to have a free modified version –

I must tell you I have no experience in excel formulas except what I have gleaned from using online tutorials here and there; with that stated, any issues you may have with it I may or may not be able to help you.

Reply

Jennifer Kim September 1, 2015 at 4:06 pm

This is GREAT! Thank you.

Looking forward to if you make an update for an option to have both a weekly & monthly view. 🙂

Reply

Maria November 20, 2015 at 6:11 pm

Thank you so much for making this excel template. I have modified it to work for my homeschool lesson planning for assignments for my 3 sons –

It’s perfect for what I needed –

Thanks again for sharing

Reply

Matt November 23, 2015 at 12:17 pm

Hi,
Like everyone else I agree that this is a great resource. I understand that the current version currently max’s out at 12 task per a given day. Is there a way to be able to list more then 12 tasks? I have certain days that have as much as 25 tasks and would like to include all of them. thanks!

Reply

July 13, 2016 at 8:03 am

Great tool Matthew, just started my MBA and this will surely increase my productivity.

Reply

Maria August 22, 2016 at 4:52 am

Hi Matthew thanks for this amazing tool! I really want to start using it, but how do I change the dates in the calendar view? Its set to 2012 😛 Hope you’ll have time to answer me. Cheers!

Reply

Daphne January 4, 2017 at 8:07 am

Hey Maria, you can do that just by clicking on the Up and Down arrows on the upper left of the worksheet. There is no shortcut to go to your desired date though.

Reply

Nikhila Eda November 4, 2016 at 9:49 am

Hi!
I am unable to download the excel file, it has an issue with “workbook protection”. Could you please help?

Reply

hannah December 5, 2016 at 1:14 pm

Hi, this tool is AWESOME! I have been using it to help me the past year with my masters degree. A couple of tweaks in the next version would be great.
1. The ability to print off a “monthly view.” The week view is ok, but I need to see the whole month to be able to plan and schedule my time. Currently I do screen clippings of the weeks and paste them into a word doc to make the monthly view, but it is not ideal since it takes time and also assignments change.
2. Tab color customization
If you could change these, especially the calendar view, it would help me so much!

Thanks again for this!!

Reply

Daphne January 3, 2017 at 2:36 am

Hi Matthew,

I just downloaded your template and I think its awesome! However, only the 1st column tasks were being displayed in the calendar view when the date is 12/20/16 onwards.. Please help.

Thank you!

Reply

Matthew Kuo February 28, 2017 at 7:21 am

The template has been updated. Please re-download.

Best,
Matthew

Reply

Cori January 3, 2017 at 3:01 pm

Hi Matthew!

I am having the same problem a couple other people had: When I input assignments in the first available category (blue), everything works wonderfully and it all shows up in the Calendar View. When I input assignments in any of the subsequent blocks (red-black), none of them translate into the Calendar View.
Do you know how to fix this problem?

Thank you!
Cori

Reply

Matthew Kuo February 28, 2017 at 7:22 am

The template has been updated. Please re-download.

Best,
Matthew

Reply

Maryam February 9, 2017 at 2:06 pm

This tool is great! I have used it every semester since Fall 2015. Now, it doesn’t seem to work. Starting at the beginning of 2017, all columns after the first one no longer feed into the calendar. I tried grabbing a clean version, but ran into the same issue. Then, I changed the dates to end in 2016 and went back a year, and all the information appears. It seems that all columns except the first one on the left do not work after 2016. Any thoughts?

Reply

Matthew Kuo February 28, 2017 at 7:21 am

The template has been updated. Please re-download.

Best,
Matthew

Reply

Leave a Comment

{ 2 trackbacks }

Previous post:

Next post:


Related pages


converting excel file to pdfdelete duplicate data excelround to the nearest 1000 in excelshortcut excel keysexcel formulas functionsnested if and vlookuplogic formula in excellookup function in excel 2010excel formula is not blankgreater than excel formulasumif and vlookupstacked bar chart in excelexcel ifsexcel currency format millionsgaussian distribution in excelrandom selection tool excelexcel if statement not equalinventory spreadsheetsexcel formula lookup value in tablecumulative graph in excelsumproduct index matchif clause in excelisna function in excelcount if in excellearn vlookup in excel 2013vlookup learningvlookup formula in excel 2010 with examplevlookup and hlookup in excellookup with multiple criteriaexcel vlookup valuetax spreadsheetsvlookup left to rightnested formulas in excelexcel remove duplicates from columnmaking graphs with excelnested if in vbafind duplicates excel 2007excel 100 stacked column charthow to make a line chart in excel 2010remove duplicate rows excelexcel formula criteriaexcel sheet to pdf converter onlinevlook up for dummieswhat is hlookup in excel 2007vlookup multiple valueremove duplicates excel vbaexcel chart formattingexcel find duplicatesremove duplicate cells excelvlookup multiple values in one cellcheapest microsoft office home and student 2013ucla mba admissionsexcel duplicate formulahow to do hlookup in excelhow do i filter out duplicates in excelsensitivity analysis for dummiesquery pivot tablehow to create a frequency histogram in excelexcel random number generator no repeatsmicrosoft excel numberingtop 20 most visited websitesvlookup function not workinghistogram excel templateexcel shortcut for filterexcel formula with multiple conditionsvlookup column and rowworking in excel with formulasisna formulahow to create a histogram excelgpa curvebest excel bookexcel 2010 hlookup
\n