How To – Using Excel 2010 To Prioritize Your Life

I tend to get bombarded constantly with new things to do, problems to solve, and solutions that need implementing. Instead of taking a random crazy attack on all your tasks it can be a good idea to prioritize them. I’ve come up with a simple spread sheet that makes prioritization easier. This list is aimed at those doing pro-active improvements – often limited resources time are available for these tasks. So what I’ve done is I use a simple rating mechanism based on the following factors:

  • Complexity
  • Impact
  • Is it within our direct influence
  • Cost

By combining these the spread sheet will automatically create a priority level, which can assist you in determining what items to tackle first. It is based on the idea if something is SIMPLE, HIGH impact, and LOW cost – do it first! Get a quick win! Then once you’ve got through those the record of your success will assist in getting funding for the more expensive items!

So first we create a number of columns in our spread sheet. Not all of these may be applicable in your case, but this is just an example:

  • Solution Submitter’s Name
  • Date Submitted
  • Team
  • Issue
  • Solution
  • Increase Employee Satisfaction? How?
  • Increase Customer Satisfaction How?
  • Complexity
  • Impact
  • Is it within our direct influence?
  • Cost
  • Priority Level (10 = highest priority, 1 = lowest priority)
  • Owner
  • Status
  • Status Comment
  • Approved By
  • Percentage Completed
  • Estimated Implementation Date
  • Actual Implementation Date

In any case it looks something like this:

image

 

To automatically calculate our Priority level we need to create three columns with the following selectable values:

  • Complexity
    • Simple
    • Complex
  • Impact
    • Low
    • High
  • Is it within our direct influence?
    • Unknown
    • Yes
    • No
  • Cost
    • Low
    • Moderate
    • High

To create the drop down lists we first need to select the column we are going to add the drop down list. To do this just click the letter name of the row. Then on Data tab click Data Validation –> Data Validation

image

 

Set Allow to List and for the source specify the text required separated by commas, in this case Simple, Complex then click OK. Repeat the process for columns  Impact, Is it within our direct influence?, and Cost.

image

 

We’ll now create a column called Priority. We create a formula using IF statements to add values based on the settings in previous columns. You may need to change this to meet your needs but in this case what I’ve used is:

=(IF(J2=”Simple”,2,0)+IF(J2=”Complex”,1,0)+IF(K2=”High”,2,0)+IF(K2=”Low”,1,0)+IF(L2=”Yes”,2,0)+IF(L2=”No”,1,0)+IF(M2=”Unknown”,1,0)+IF(M2=”High”,-2,0)+IF(M2=”Moderate”,1,0)+IF(M2=”Low”,4,0))

Where J2 would refer to the Complexity Column, K2 would refer to the Impact column, etc. So in this formula if J2 (Complexity) = Simple then 2 is added to the total priority. Once you’re done click and drag the formula down to fill up the other rows:

 

image

For a final touch we can click Filter and filter from Largest to Smallest. This will give us the highest priority tasks first.

image

 

My complete spreadsheet can be downloaded here:

http://tiange.com.au/SolutionTracker.zip

Now for those of you with SharePoint in your environments –> You can use a similar formula in a calculated column to do the same thing. If you try it but can’t get it to work let me know, and what version of SharePoint you’re using.

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in Excel, Office and tagged . Bookmark the permalink.

One Response to How To – Using Excel 2010 To Prioritize Your Life

  1. Christoph says:

    Hi,

    This is exactly what I’m looking for. Thanks to show this solution.
    But unfortunately the link to download the solution is dead.
    Could you please send me the zipfile to my email address or update the link on this page?

    Thanks.
    Christoph

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s