How to Automatically Prioritize Tasks Using a Spreadsheet

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.