How to Manage Priorities with Excel
Create a "Home" or "Office" tab., Rename Sheet2 as "Template" and Sheet3 as "Points" by repeating Step 1. , Create the Importance table., Define the name "Importance"., Create the Effort Table., Create the Urgency table., Enter headings on Home...
Step-by-Step Guide
-
Step 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". -
Step 2: Rename Sheet2 as "Template" and Sheet3 as "Points" by repeating Step 1.
, On the Points tab, fill in columns A, B and C: , Select from Cell A2 to C7.
Click on Insert Name Define Define the name as "Importance" and click OK. , 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". , Repeat steps 3, 4 and 5 to create the Urgency table in columns I, J and K.
Name them "Urgency". , 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. , Note Priority, Urgency and Days Left are left blank.
They will be filled in with formulas.
Here’s a sample of home tasks. , 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) ,, 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. , Select from cells A1 to K, for as many rows as you have data.
Then click on Data Sort. ,, As you complete tasks, mark the date in the Finished column.
Remember CTRL-; (control key and semi-colon) immediately enters the current date. , 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. -
Step 3: Create the Importance table.
-
Step 4: Define the name "Importance".
-
Step 5: Create the Effort Table.
-
Step 6: Create the Urgency table.
-
Step 7: Enter headings on Home tab.
-
Step 8: Enter your list of tasks.
-
Step 9: Enter formulas for Days Left
-
Step 10: Urgency and Priority.
-
Step 11: Change the format for Cell I2 to Integer by right clicking on the cell
-
Step 12: selecting format and make it a Number with 0 Decimal places.
-
Step 13: Copy formulas for Priority
-
Step 14: Urgency and Days Left to the remaining cells in each column.
-
Step 15: Sort the rows by Priority.
-
Step 16: Save your priorities spreadsheet
-
Step 17: including the date for versioning.
-
Step 18: Mark tasks Finished.
-
Step 19: Watch Priorities change each day.
Detailed Guide
Open a new Excel spreadsheet.
Right click on the "Sheet1" tab at the bottom and click on Rename.
Type in "Home" or "Office".
, On the Points tab, fill in columns A, B and C: , Select from Cell A2 to C7.
Click on Insert Name Define Define the name as "Importance" and click OK. , 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". , Repeat steps 3, 4 and 5 to create the Urgency table in columns I, J and K.
Name them "Urgency". , 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. , Note Priority, Urgency and Days Left are left blank.
They will be filled in with formulas.
Here’s a sample of home tasks. , 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) ,, 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. , Select from cells A1 to K, for as many rows as you have data.
Then click on Data Sort. ,, As you complete tasks, mark the date in the Finished column.
Remember CTRL-; (control key and semi-colon) immediately enters the current date. , 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.
About the Author
Ann Myers
Ann Myers has dedicated 3 years to mastering education and learning. As a content creator, Ann focuses on providing actionable tips and step-by-step guides.
Rate This Guide
How helpful was this guide? Click to rate: