All the key points in one handy PDF
** KEEP THIS BY YOUR SIDE FOR CONSTANT REFERENCE **
Enter your details for immediate access.
Mistakes can creep into your Excel formulas for all sorts of reasons.
In this post, I’m going to walk you through just about every mistake you could make with a formula, starting with the simplest and working through to more complex examples, and show you how to identify, fix and avoid these mistakes in the future.
Kicking things off with something that can throw beginners into a panic. It isn’t really an error as such, but if you don’t recognise what it is, you won’t know how to rectify it.
What am I talking about?
The good old hash symbol, also known as hashes, hashtags, sharps pounds or train tracks.
These symbols will appear if the cell width is not wide enough to display numerical content in full.
Every formula must start with ‘=’ (without the quotes). Starting with ‘+’ also works but is only generally used by those with a Lotus 123 background.
When using functions, every function needs brackets. Some functions have data inside the brackets, e.g.
=SUM(A1, B1, G10:G15)
while others don’t e.g.
but always make sure you include the brackets.
Some people write formulas like
thinking that Excel will calculate a total. That’s like saying “Hi Excel, here’s some data” but I'm not going to you what to do with it.
There is a set of mathematical rules called the Order of Operations which clearly states the order in which a formula is calculated. The rules have been around since the 1600s and are taught at lower-high-school level.
Here is the order of operations:
For example, the majority of people say that
= 2 + 3 * 4
is 20, when in fact the answer is 14.
The multiplication (3*4) is done first giving an answer of 12. The addition (2+12) is done second, giving an answer of 14.
By adding brackets around the 2+3 like this …
= (2 + 3) * 4
… the answer is now 20, because the bit in the brackets is now performed first, giving an answer of 5. The multiplication - five times four - is now done second, resulting in 20.
Let’s look at another example.
Let’s say that cell A1 contains today’s date and cell A2 contains your date of birth.
As a quick aside, dates in Excel are stored as numbers.
This makes calculations with dates a doddle.
To calculate your age, you can start with today’s date (A1), subtract your date of birth (A2) and divide the result by the number of days in a year (365.25).
Your initial formula will look like this:
= A1 - A2 / 365.25
But if you leave it like this, the formula calculates out of sequence because the division will occur before the subtraction. Therefore you would modify the formula to
= (A1 - A2) / 365.25
P.S. There are a bunch of alternative acronyms for BEDMAS. These include BODMAS, BOMDAS and PEDMAS. They all serve the same purpose. The ‘P’ stands for Parantheses. The ‘O’ stands for Of (i.e. to the power OF), Over, Order or Ordinals.
Whenever you type an opening bracket in a formula you must include a corresponding closing bracket.
This is true whether you are using brackets to control the order of operations or brackets for one or more functions.
Remember to treat every function (in a formula) as a unit. Every function needs a set of brackets.
Where you have nested functions, i.e. one function sitting inside the brackets of another function, the formula calculates from the inside to the outside.
=IF(AND(A1="QLD", A2>500), "Yes", "No")
The inner brackets belong to the AND function, so the AND function is processed as a unit. The answer is then used as the condition for the IF function, which owns the outer set of brackets.
When editing the formula in Excel, the outer brackets (IF) are black and the inner brackets (AND) are green.
Whenever multiple brackets are used in a formula, they are colour-coded. The outermost brackets are always black, the next inner brackets are always green, the next set are purple and each subsequent inner set of brackets will have its own colour.
This is a very handy way of identifying matching pairs of brackets and should be used to make sure that brackets are placed in the correct positions.
I’ll state it outright. You should never type a cell reference directly into a formula.
Because you could misread it and you could mistype it. Very easy to do.
When you finish writing a formula you must press ENTER to lock it in. If you don't you could end up with a random cell reference in your formula, which has no right to be there.
Let’s say, you write this formula.
= A1 + A2 + A3
Then, instead of pressing Enter to lock in the formula, you click on the other side of the spreadsheets to get away from the formula. If the cell you click randomly is, say, M5, then the formula now changes to:
= A1 + A2 + M5
And the worst part is, you probably don't notice until later!
For example, to sum a cell range you would type:
which uses the colon (:) to indicate a cell range. The following alternatives will fail or give incorrect answers:
=SUM(A1 to A10)
If you forget to separate a data item or a function argument with a comma, it will not work.
=VLOOKUP(A1 B3:G15, 2, FALSE)
fails because of a missing comma between A1 and B3:G15.
=SUM(A1, B2 E3:G10)
fails because of a missing comma between B2 and E3:G10.
Any formula that includes a text-based value must place quotation marks (") around the text item. If you don’t, the function won’t work.
= IF (A1="QLD", "Go the maroons", "Go the blues")
works great, but miss off any of the 6 quotation marks and the formula will fall flat.
As a minimum you must provide all the mandatory data that a function requires to produce an answer, separating each argument or data item with a comma.
For example, VLOOKUP has 3 mandatory arguments (the match value, the data table or array, the number of the column that contains the result data) and 1 optional argument (the range lookup which is TRUE or FALSE depending on whether you need a CLOSEST match or an EXACT match).
If you do not include one or more of the first 3 arguments, the formula will fail.
Sticking with VLOOKUP, the first column of the table, which is used to match the input value, must contain unique entries. If there are any duplicates in the first column, the formula may return the correct result, or it may not, making it as useful as a chocolate teapot.
Obviously, with over 450 functions in the Excel library, I cannot possibly go through every function, pointing out the things you must conform to, but Microsoft provide a very comprehensive description in the Help files.
A #REF! or reference error occurs when a formula refers to a non-existent cell, caused by physically deleting the column or row where the cell existed.
This is the simplest of all error to fix. It occurs when the cell that is being divided by is blank or contains zero.
There are 2 reasons why you get a #NAME! error.
When you name a cell or cell range using the name box, make sure to eliminate spaces (use underscore instead) and make sure you press Enter to register the name.
When using a name in a formula make sure not to mis-spell it. You’re actually better off just pressing F3 while inside your formula and selecting the name from the list of named cells or ranges that are displayed.
A #VALUE! error normally occurs when you feed text into a numerical function.
For example, if your SUM formula includes a text cell, an error will occur. Often, a label (i.e. text) will be listed alongside a numerical value and the label is mistakenly picked up instead of the value.
If a function points to a cell expecting to find numerical data but instead finds text, the formula will fail.
This error mostly occurs in LOOKUP functions. If the lookup function cannot find a match based on the input value provided, a #N/A! error is shown.
This error occurs if you supply an invalid number to a function argument. For example, you may inadvertently provide a negative number or add a dollar sign ($) or percent symbol (%) where none is needed.
When you refer to cell range (e.g. A1:A10) if you omit the colon (:) and leave a space, the formula will result in a #NULL! error.
This is another one where once you understand what is happening, you can fix it quickly.
A circular reference occurs when the cell in which you are writing the formula contains a reference to itself.
For example, if you write a formula in cell A1 that says
then you will get a circular reference.
When you refer to a cell on the same worksheet, say A1, the cell reference just says A1.
When you refer to a cell on another worksheet, say A1 on a sheet called February, the cell reference now looks like February!A1.
When you refer to a cell on another workbook, say A1 on a sheet called February in a workbook called Finance 2016, the cell reference now looks like [‘Finance 2016’]February!$A$1.
When you create this type of formula, you should switch to the other workbook if necessary, click the desired worksheet tab, then select the cell or cell range. Excel will insert the cell reference into the formula using the correct notation.
If you try to type out the full cell reference yourself, there are too many opportunities for you to stuff up. Just don’t do it!
To round off this post I thought I’d add a few ...
Sometimes you’ll see a small green triangle in the corner of a cell. This simply indicates that there’s something weird happening in your cell. Common causes are that the cell is inconsistent will all those that surround it, or that a number is stored as text. There are other reasons too.
Click the cell that has the triangle, to display a Smart Tag.
Click the smart tag to see the reason, then choose one of the options presented to take the appropriate action. A screen tip is also displayed which displays the reason for the error.
When you click the smart tag a list of actions is offered. For example:
The IS functions check for a particular type of error within a cell and return TRUE or FALSE.
The structure for all IS functions is =functionname(cell reference or formula).
The ERROR.TYPE function is a single function which will return a number between 1 and 8 depending on the type of error found.
=IF(ISNA(VLOOKUP(A1, Lookup_table, 2, FALSE), “”, VLOOKUP(A1, Lookup_table, 2, FALSE)
would show the result of the VLOOKUP function if one was found, otherwise it displays a blank cell (instead of the #N/A! error) if no match is found.
Yes, it's a bit clunky. The next method is much better.
The IFERROR was only introduced in the last few years but simplifies things immensely.
We could simplify the formula in the last step to
=IFERROR(VLOOKUP(A1, Lookup_table, 2, FALSE), "")
If the VLOOKUP does not find a match or returns any kind of error, display nothing instead than the error.
Let’s consider this simple (if contrived) example.
The formula in cell D6 is manually adding the contents of cells D1 to D5 but cell D3 contains a text entry, which cannot be added.
Select the Formulas tab. Click the Evaluate Formula icon (in the Formula Auditing group). This tool lets you step through the formula one step at a time. The current step is underlined.
Click Evaluate to calculate the result for the underlined step.
The current step is calculated and the next step in underlined.
In this example, processing the current step results in a #VALUE! because it is attempting to add a text item to a numeric item.
There is a tool called Trace Precedents which lets you see what data flows into a cell. There is a sister tool called Trace Dependents which lets you see where a cell is used elsewhere.
Both paint arrows on your screen so you can visually see the flow.
To see the precedent cells related to a selected cell:
To display cells that are dependent on the selected cell:
In this post I have highlighted 21 different mistakes you can make in your formula and have demonstrated how to rectify each one. I’ve also given you 5 fantastic tools to identify, check for and fix errors.
I trust this has been useful.
Have I forgotten something? Share the love and add your own little tips and tricks in the comments below.
Now go fix those pesky errors!
Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.