Errors in your spreadsheet? Traci Williams shares her recommendations for dealing with mistakes in Excel
We all know that Excel works on logic and rules, and therefore it is impossible for Excel to make a mistake. As it is a computer, it does exactly what you tell it to do, so therefore, if it does give you an incorrect answer, it will be because of an error you have made with the formula, the range, or the data itself.
It also means that there is a perfectly logical explanation for everything that Excel does too. I take comfort in this, as I know I will always be able to find an answer.
Here are my recommendations for dealing with mistakes in Excel:
Stay Calm
The most important thing I can tell you when faced with errors in a spreadsheet is to stay calm and remind yourself that there will be a logical explanation.
What I see time and time again from clients is that they recognise something isn’t right and assume the formula must be incorrect, so they start tweaking the formula.
When this doesn’t solve the problem, they start questioning the formatting and re-formatting the sheet.
When this also doesn’t solve the problem and time is ticking away, and they feel like they’re going around in circles, and they’ve lost track of the changes they’ve made, people will often resort to removing formulas and entering data manually.
Often, there was no issue with the formula, or the formatting; it was simply that a cell may have been missing a value and the error could have been resolved very easily.
I employ some of the following steps when trying to resolve errors:
Have Faith in Yourself and Your Formulas
The first assumption we tend to make is that we have entered the formula incorrectly. I always look at ALL the formulas; if none of them are working, then it is possible we may have made a mistake.
However, if some of the formulas do work, then the issue cannot be with the formula itself.
Use Evaluate Formula
Sometimes when we look at formulas, we fail to see the obvious. I always use the ‘Evaluate Formula’ function, as this steps through a formula and shows you how Excel arrived at the answer.
Select a cell containing a formula, then go to the Formulas ribbon and select ‘Evaluate Formula’.
This screen will appear, which will contain the entire formula from the selected cell:
A tiny part of the formula will be underlined, and this represents the position where Excel will begin the calculation.
If you click ‘Evaluate,’ this part of the formula will be replaced with the value of the underlined element (whether that represents a cell, range or calculation). I would recommend identifying the value you expect it to return before pressing ‘Evaluate,’ as this is the best way to follow the logic.
In the above instance, the value of the range ‘Values’ is: $F$3:$F$20
The evaluation then moves on and has underlined the next part of the formula that will be evaluated.
Continue pressing ‘Evaluate’ to see the entire formula calculated.
Sometimes this can make it easier to find an incorrect cell reference or step within the formula.
Dollar Symbols
When formulas are copied across columns and down rows, Excel’s natural behaviour is to update the Row numbers and Column letters. Sometimes this is exactly what we need and is useful, but other times we need a range to stay fixed as we copy formulas, and this is a common reason for formulas to stop working.
If we need a range to stay constant, simply enter dollar symbols around the cell references:
F3:F20 would become: $F$3:$F$20
Top Tip
Position the cursor around the cell reference and press F4 (on the keyboard). This will automatically enter the $ symbols to save you from working out where to position them.
These dollar symbols (or lack of them) are often the cause of a rogue formula.
Spaces – TRIM
A key issue that I always find with spreadsheets is extra spaces being added to the end of the text. This can play havoc with Match or Lookup formulas, as Excel reads them as a whole new character. Most frustratingly, the spaces are usually invisible unless you have the cell open for editing, which also makes them extremely difficult to spot too.
I’ve been caught out with the spaces so many times that it’s now my ‘go-to’ thing to look for when a formula doesn’t work.
The additional spaces can be found either by opening the cell for editing, or underlining the cell, as you can see that the line doesn’t stop at the end of the text:
Once you’ve identified that you have extra spaces, you realise that you can’t use ‘Find & Replace,’ as this will remove ALL of the spaces, and you need the spaces in between the words.
The formula you need is: =TRIM(A1)
This formula removes any irrelevant spaces from text, so it will leave one space in between words but remove any others.
It can also be wrapped up within other formulas too, such as:
IFERROR
You may know in advance that some of your formulas won’t work (e.g., if your data is incomplete, or you’re trying to find missing data).
In this instance, instead of waiting for Excel to return an error (such as #REF!, #N/A, #VALUE!, etc.), you can use an IFERROR formula to tell Excel what to do if the formula returns an error:
Wizard – Commas, Brackets, Speech Marks
In the formula above, you can see that there are several uses for commas, brackets and speech marks. They can be confusing as to where they need to go and are very often the cause of formulas not working.
If this is an area where you struggle, then help is at hand with the Excel Formula Wizard. This can be accessed in the Formulas ribbon, Insert Function (first icon on left):
This screen will appear, and you can type a brief description of the formula into the bar at the top and click ‘Go,’ and all of the matching (or similar) formulas will be returned in the box at the bottom. Select each one and read the description underneath the box to make sure this is the required formula.
Click ‘OK’ when you’ve found the correct formula.
This is the Formula Wizard, and you can simply fill in each of the boxes (as per the guidance at the bottom) to complete the formula.
The Wizard will take care of all the commas, brackets and speech marks, so it makes it super simple for you to enter formulas, especially if you’re new to them.
An Offer for You
If you’re still unable to find the underlying cause of any spreadsheet issue, I would love you to send it to me… in fact, I look forward to helping you to solve the mystery!
