ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Business and Employment»
  • Learn Business Skills

How to Automatically Prioritize Tasks Using a Spreadsheet

Updated on April 24, 2012

You may well ask, 'automatically prioritize tasks?'

Let me explain.

Searching the web for "prioritize using spreadsheet" gets many hits, with suggested methods ranging from simple to complex. But they mostly have one thing in common — somewhere along the line you have to assign priorities to your tasks; the spreadsheet doesn't do it for you.

It is often difficult to decide which tasks have higher priorities than other tasks, but with the method described here any preconceived ideas of priority are deliberately ignored. In fact, nowhere in the process do you get anywhere near assigning a priority! I developed a simple technique of comparing the importance or value of tasks or activities and generating a priority weight based on a final value score, along the lines of the technique known as value analysis or value engineering.

Prioritize not only tasks

Before I demonstrate the method I must mention that besides using it for prioritizing tasks or activities in a project, you could also use the method for other purposes. In fact, it had nothing to do with projects and tasks initially. I conceived of this method a few years ago when I was faced with making some career choices. I decided that my best approach would be to present my employer with a list of my skills. But this couldn't be just any list. I wanted to make sure that the skills that gave me the most job satisfaction would be emphasized with a higher 'weight' or 'value score' in the hope that they would receive due consideration. Thinking about how I might use a computer to assist me, gave rise to the method described in this article. It paid dividends in that skills evaluation exercise.

What you need

As the title of this article suggests, you need a computer with a spreadsheet program in order to implement this method. It is also assumed that you know your way around a spreadsheet. In the example that follows I use the open source spreadsheet program, LibreOffice Calc because all my computers and notebooks run on the Linux operating system. However, don't let this discourage you from reading further. Your Microsoft® Excel spreadsheet may differ somewhat in appearance and function here and there, but it does exactly the same job. Any spreadsheet will do, even the one on your smartphone or tablet, as long as data sorting is a built-in feature. Having a summation function would also be an advantage, but not strictly necessary, since numbers in rows or columns may be added by other means.

An example of prioritizing tasks

Let us work through a simple example with 10 tasks. Suppose that my wife and I have just added an extra room to our house. It is a bare-bones affair — concrete floor, plastered walls, no ceiling. We want natural stone tiles for the floor and tongue-and-groove knotty pine for the ceiling. Conduit has been installed for the wiring to wall power outlets and light switches, as well as for down-lighters in the ceiling. A number of floating shelves are needed here and there on the walls. We decide to do the work ourselves, so some planning is in order.

Step 1. Create a list of tasks

Fire up the computer and start the spreadsheet. The first column on the left is reserved for the task ID, which starts at 1 and is sequentially incremented by 1 for every task added. Give the column a name in the first row, e.g. 'ID', 'Item' etc.

Now here is something important: DO NOT use auto numbering in this column — enter each number manually. In other words, don't take a shortcut by inserting the formula "add 1 to the previous cell" in the first cell of the next row and then copying this to all the cells below it. Of course this will sequentially number all the rows in the first column, but will also cause problems later on. The reason for this will be explained later.

The second column is used for the task descriptions. Here we enter the project tasks without any regard to order. Simply add them as they come to mind. In fact, forget about priority.

Figure 1 shows our spreadsheet at this stage.

Figure 1.

Step 2. Set up the comparison matrix

Rows 2 through 11 and the next 10 columns, C through L, constitute the comparison matrix. The 11th column, M, is where the weight or value score of each task is computed and shown. We set up the comparison matrix as follows:

  1. Number columns C to L from 1 to 10 in the first row. These numbers correspond to the task ID numbers in the first column. Give column M an appropriate label, e.g. 'score' or 'weight' or 'priority' etc.
  2. Fill the 10 x 10 comparison matrix with zeros, except for the diagonal cells, which must be 1. What this means, is that each task is initially assigned a value of 1 in the cell corresponding to its ID row and ID column position in the comparison matrix.
  3. Use your spreadsheet's summation function (Σ) in each cell of the score column to add the values in the same row of the comparison matrix. In other words, for task 1, add the values of cells C2 through L2, and place the result in cell M2, i.e., the score of task 1; for task2, add the values of cells C3 through L3, and place the result in cell M3, i.e., the score of task 2, and so on. The result is that all tasks initially have a score of 1.

Figure 2 shows our spreadsheet at this stage. I have highlighted the diagonal cells containing 1 in the comparison matrix. You can adjust the width of the columns as I have done, if you wish to have a more compact layout.

Figure 2.

Step 3. The hard part

Now comes the labour-intensive part — examining each task and comparing it against all other tasks in order to determine whether it is more important (has more value) than other tasks. In this example, my wife and I would do this together. In a large project, there would typically be a team of people who carry out the value analysis. Or, you may very well be alone in this task, as I have been on many occasions.

Back to our example. Starting with task 1, we compare it with the other tasks, one after the other. Note that it doesn't make sense to compare a task with itself; that is why the diagonal entries in the comparison matrix have a value of 1. We compare task 1 with task 2 and decide that task 1 is more important. After all, we cannot start any work without materials! So, the value of the cell in the same row as task 1, in the column of task 2 in the comparison matrix, becomes 1, i.e., cell D2. This indicates that task 1 is more important than task 2. Next, we compare tasks 1 and 3, and again decide that buying materials is more important than making floating shelves, so we replace the 0 in cell E2 with a 1. In fact, all the cells in the task 1 row of the comparison matrix are replaced with 1, with the result that the score of task 1 is now 10 (automatically computed by the summation function).

Hint: At this point you may want to highlight the entire task 1 row to show that it has been compared to all other tasks. This is particularly useful when you have a large number of items to compare.

Next, we move on to task 2. Since it has already been compared with task 1, and since it is not compared with itself, we start by comparing it with task 3. What is more important: lay the floor tiles or make floating shelves? We decide that the floor tiles are more important, so cell E3 gets a 1, and the score becomes 2. The same applies to task 4: having tiles on the floor is more important than painting the shelves, so cell F3 gets a 1, and the score of task 2 becomes 3.

But what about task 5? We decide that it is better to paint the walls before laying the floor tiles, if only to eliminate paint spills on the new tiles! This means that task 5 is more important than task 2, so now we place a 1 in cell D6. Can you see why?

We continue in this way until every task has been compared with every other task. You will notice that the number of comparisons decreases by 1 as we continue; by the time we reach task 9, the only task left with which to compare it, is task 10.

Hint: It may well be that you cannot decide whether a task is more important than the one it is being compared with. In this case, give each a 1 in the appropriate cell. For example, suppose that tasks 2 and 5 were equally important. Then we would place a 1 in cell G3 and a 1 in cell D6.

The results of our analysis are shown in Figure 3.

Figure 3.

Step 4. The fun part

We are nearly there, but before we continue, it would be wise to save the file, if this has not been done already. The next step is to highlight the entire spreadsheet, excluding the headings in the first row, as shown in Figure 4.

Figure 4.

All that remains now, is to sort the data. Depending on your spreadsheet the details may differ from what I describe here. After highlighting the spreadsheet as shown in Figure 4, I select Data > Sort from the menu bar in LibreOffice Calc. This opens the Sort dialog window. Under the Sort criteria tab I choose column M (the score column) to sort by in descending order. I leave the other options as undefined and click OK.

Done!

Figure 5 shows the result. We now have our tasks listed in order of priority, from highest to lowest.

Figure 5.

A few words...

In Step 1 I warned against auto numbering in column 1. The reason is that should you find it necessary to add one or more tasks, you can easily reverse the last sorting step by sorting again, this time by column 1 (ID). If you entered the task ID numbers manually, you will have your original layout back, still showing the scores obtained by comparison. However, if the ID numbers were auto numbered, re-sorting will not work, and you would have no choice but to rearrange everything manually.

To add a task, insert a new comparison matrix column between the last column (10 in the example) and the score column. Then, add a new ID below the last row, add the task description, enter the zeros and a 1 (on the diagonal) in the last row, insert the summation function in the new score position and compare the new task with the old tasks following the procedure described above. Finally, do a sort by the score column in order to obtain your new prioritized list.

I hope that my method makes it easier for you the next time you have to prioritize tasks or activities, whether it be for work, business, home, hobby or your next holiday.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Stanley Dawn S. Tayo 5 years ago

      I would like to say thank you to this author, i really appreciate this time management technique using spreadsheets, i just search this last day and i took a sample from my projects and applied it, yes it's really true although my projects are already their but i cannot decide which will i prioritize, anyway I am Stanley from Philippines HR Manager, a first time HR Manager..Thank you so much and God Bless you always

    • jpcmc profile image

      JP Carlos 6 years ago from Quezon CIty, Phlippines

      This is really useful. Now for the hard part - get people to do this.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)