The effect of making an error in a spreadsheet can range from mildly irritating, through embarrassing, to the catastrophic and career threatening. For some examples of the latter, it’s worth looking through the collection of spreadsheet horror stories collated by the European Spreadsheet Risk Interest Group (EuSpRIG). Here, we are going to consider a few things that can go wrong rather too easily in a spreadsheet and see how to reduce the risk of them happening.

Sorting

We have typed a list of students and their exam scores into Excel. We want to sort the names alphabetically (Figure 1).

We can right-click on any of our names and choose Sort, then Sort A to Z. Our names are now in ascending alphabetical order. Unfortunately, our score column hasn’t been sorted, so Frederick Ferguson has received an unexpected boost from 32 to 56 (Figure 2).

When creating any table of data in Excel, it’s advisable to avoid including any blank rows or columns. Including blank rows or columns makes it more difficult for Excel to recognise your data as a single entity. Were we to select one of the scores in our example and choose to insert a chart, the list of students would not be included in that chart. However, as we have seen, this is not the only consequence. Because each column in our table is treated as a separate area, the Sort applies to the student column in isolation from the score column. Had we avoided the blank column B and simply made column A wider to accommodate the student names, we could have right-clicked on exactly the same cell and chosen Sort, Sort A to Z in exactly the same way, and our sort would have worked as intended (Figure 3).

Sums sometimes sum some

This error is so common that Excel includes a specific check to highlight it. We can create a formula using the SUM() function to add up all our student scores (Figure 4).

We then add a couple more scores to our results. The cell range in our SUM() function doesn’t change to include these new rows so our total is no longer the total of all of our scores. Excel does try to help avoid this. If the appropriate error checking options remain selected, then the cell containing the range that omits adjacent values will be marked with a green flash in the top left-hand corner and clicking on the cell will display a warning (Figure 5).

As the EuSpRIG list of horror stories shows, such a simple error, left uncorrected, can ruin reputations. One method of preventing the error occurring in the first place is to ensure that you use an Excel Table whenever possible. We can select any single cell in our block of data and use the Control+T keyboard shortcut to turn our table into a Table. Excel should work out the range of cells automatically, as long as there are no blank rows or columns, and we can confirm that our Table has a header row. Having turned our data into an Excel Table, we can add rows and our SUM() range should now adjust automatically (Figure 6).

In fact, if we recreate our SUM() formula, our range will use the name of our Table and column:

=SUM(Table2[Score])

One argument short of a lookup

The various Excel lookup functions are popular with those working with financial data where looking up a value in a table and returning the value from another column in that table is a common requirement. Here we want to see the score for the student whose name we enter in cell A2. We use the VLOOKUP() function to do so, entering A2 as the reference to our lookup value cell, and looking up our value in our Table that, as we saw in the previous example, we can refer to by name. Our third argument tells Excel to return the answer from column 2, the score column (Figure 7).

Unfortunately, Excel doesn’t return the value that we were probably expecting, returning the value from row 9 not row 11.

This is a result of our failure to specify the vital fourth argument of VLOOKUP(). This fourth argument determines whether the function will return an exact match or an approximate match. The default is to return an approximate match. In accidentally omitting the fourth argument, we condemn VLOOKUP() to performing an approximate match and thereby returning a seemingly random, incorrect answer. Here is our function with our fourth argument entered correctly (Figure 8).

There is a general approach to avoiding this sort of error and that is to be very careful and read the documentation on any Excel function you use that you are not already familiar with. The Insert Function button in the Excel formula bar will display an input screen that has a direct link to help on the function you are using.

Excel is also in the process of introducing a replacement for the existing lookup functions. The new XLOOKUP() function defaults to using an exact match.