Manage Priorities with Excel

Writing your priorities on paper works if you’re able to knock them off in an afternoon. With a torrent of incoming tasks at home or work, many get carried over to the next day (or week or month). This Excel spreadsheet watches approaching deadlines and shifts task priorities accordingly. In 20 minutes the steps below provide a more effective way to manage your priorities.

Steps

  1. Create a "Home" or "Office" tab. Open a new Excel spreadsheet. Right click on the "Sheet1" tab at the bottom and click on Rename. Type in "Home" or "Office".
  2. Rename Sheet2 as "Template" and Sheet3 as "Points" by repeating Step 1.
  3. Create the Importance table. On the Points tab, fill in columns A, B and C:
  4. Define the name "Importance". Select from Cell A2 to C7. Click on Insert Name Define
    • Define the name as "Importance" and click OK.
  5. Create the Effort Table. Repeat steps 3, 4 and 5 to create the Effort table in columns E, F, and G. Select cells E2 to G6 and give them the name "Effort".
  6. Create the Urgency table. Repeat steps 3, 4 and 5 to create the Urgency table in columns I, J and K. Name them "Urgency".
  7. Enter headings on Home tab. Click on the Home tab and insert headings in row 1:
    • A - Priority A formula, 1 for the most important task, but can be over 100
    • B - Task Name of the task.
    • C - Importance An A, B, C, D, E, or F from the table.
    • D - Effort A 1-5, from the Effort table.
    • E - Urgency A formula based on Due date.
    • F - Due Date when the task should be finished. Due dates are not hard and fast. Ramp Up tells you how early you could start the task and Extension tells you how many days it could slide. Getting a haircut might have a Ramp Up of 5 and an Extension of 4 - it doesn’t make sense to get a haircut 2 weeks early and people might notice if it was more than 5 days late.
    • G - Ramp Up Days prior to Due date you could start task.
    • H - Extension Automatic extension of Due date
    • I - Days Left Formula. Number of days before Due date; negative if Due date has passed.
    • J - Finished Date task was actually completed.
    • K - Comment Any details for the task.
  8. Enter your list of tasks. Note Priority, Urgency and Days Left are left blank. They will be filled in with formulas. Here’s a sample of home tasks.
  9. Enter formulas for Days Left, Urgency and Priority. The formulas below are for row 2.
    • I (Days Left) =F2-IF(ISBLANK(J2),TODAY(),J2)
    • E (Urgency) =IF(I2>G2,5,IF(I2>0,4,IF(I2=0,3,IF(I2+H2>0,2,1))))
    • A (Priority) =VLOOKUP(C2,Importance,2,FALSE) + VLOOKUP(D2,Effort,2,FALSE) +VLOOKUP(E2,Urgency,2,FALSE)
  10. Change the format for Cell I2 to Integer by right clicking on the cell, selecting format and make it a Number with 0 Decimal places.
  11. Copy formulas for Priority, Urgency and Days Left to the remaining cells in each column. Select cell E2 and type CTRL-C. Select cells E3 to E10 and click CTRL-V. Repeat to copy cell I2 to cells I3 to I10. Finally, repeat to copy cell A2 to cells A3 to A10. Ignore the odd values you get for undefined tasks.
  12. Sort the rows by Priority. Select from cells A1 to K, for as many rows as you have data. Then click on Data Sort.
  13. Save your priorities spreadsheet, including the date for versioning.
  14. Mark tasks Finished. As you complete tasks, mark the date in the Finished column. Remember CTRL-; (control key and semi-colon) immediately enters the current date.
  15. Watch Priorities change each day. Here are the priorities over several days. On July 13 all tasks are before the Ramp Up period, so have large numbers. On July 20th, higher priorities (smaller numbers) appear for four tasks, including Mow Lawn which has hit its Due date. On the 21st, the priority is higher because we're in the Extension period and on July 23rd it's even higher because it's beyond the Extension period. Pay bills also goes through an escalation on the 23rd and 25th.

Tips

  • Feel free to add more columns - who assigned the task, category, etc.
  • Split large tasks into smaller ones.
  • Sort the task list every day if necessary.
  • Keep separate Excel files for family/home and work.
  • Use AutoFilter to select past due tasks (Urgency=1) or highly important tasks (Importance="A")
  • =IF(WEEKDAY(TODAY(),2)>5,TODAY()-(WEEKDAY(TODAY(),2)-5)+7,TODAY()-(WEEKDAY(TODAY(),2)-5))
  • Read articles listed below on prioritizing and time management.
  • Copy recurring tasks to the Templates tab, so you can find and recopy them back easily.
  • Feel free to change the points given for each part of priority.
  • One way to keep a re-occurring task constantly updated is to enter this under the date column: (This example uses Friday, which is "5" here.)
  • Add conditional formats on Due date to indicate when it is overdue.

  • Weekly, monthly (or perhaps double that) count the number of completed tasks in each period. This may be the only positive feedback you might get on your work.
  • This finds "today"s date (Monday=1, Tues=2, ... Sun=7) and checks to see if it's later than Friday. If it is, it will add seven to the current Fri, giving next weeks Friday. If it isn't later than Friday, it just displays Fridays date for this week.
  • In this example, Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, and Sunday = 7.
  • To change this formula so it works for, say, Tuesday, we can see by looking at the list above that Tuesday = 2, so just swap out the three 5s in the formula for 2s.

Warnings

  • Instead of carrying your Excel files with you all the time, use Google Spreadsheet so your files will always be available to you.
  • Save the spreadsheet every few sessions for recovery.
  • Time management is highly personal and this spreadsheet may not fit with your needs and preferences. It may feel intrusive or just too demanding to visit daily. It may work for you, but not your friends or vice versa.
  • The calculated priorities are not ordinal numbers. Completing a “1” priority task doesn’t shift all the others up. Priorities can range to over a hundred and not all numbers are possible. Focus usually on priorities between 1 and 12.
  • Don't worry about having too many tasks - even two months of tasks can wait at the bottom of the list until their due date approaches.

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Manage_Priorities_with_Excel&oldid=275238"