You’ve probably heard the commonly cited statistic that 88% of spreadsheets have errors.
Sometimes, these errors are obvious and Excel will display an error message. Other times, everything looks OK on the surface but there’s actually an error in your calculations.
While it’s helpful to know how to identify and correct common problems with formulas, here’s what we will say: Excel is not the best application for managing data — especially if you’re dealing with large amounts of data from different sources. Spreadsheets are finicky beasts, and their many quirks and hang-ups will not only frustrate users but also put your business at risk of potential losses.
If you’re running into a lot of issues with your spreadsheets, you’re better off using a software system that’s designed for that purpose. In the meantime, though, let’s look at some of the frequent reasons your formulas might not be working as expected:
Problem 1: Excel displays a formula error
If you’re lucky, Excel will catch the error for you and display an error message in the affected cell. This will cut down significantly on the time you spend figuring out why your formulas aren’t working. Here are some of the most common error messages and what they mean:
- #####: This isn’t actually an error — it just means the cell isn’t wide enough to display the full value. To fix this, drag the right border of the column to make the column wider.
- #DIV/0!: You’ll see this error if you try to divide by zero or an empty cell. Check to make sure all the cells referenced in the formula contain a value and there are no blank cells.
- #N/A: This error means the formula can’t find the referenced value. You might see this when you try to use the VLOOKUP, HLOOKUP, LOOKUP, or MATCH functions.
- #NAME?: This error usually happens when you make a typo in the name of a formula — for example, =SM(A1:A11) instead of =SUM(A1:A11) — or another named value. The best way to avoid this is to select the formula name you want from the dropdown menu.
- #NUM: You’ll see this error if you’ve included a symbol like a dollar sign ($) or another invalid number.
- #REF!: This means the formula references a cell that no longer exists. It usually happens when you delete a row or column on your spreadsheet.
- #VALUE: A value error occurs when the value you entered isn’t the expected type — for example, if a function is expecting a number and you enter text instead. This is one of the trickiest errors to decode, since it doesn’t give you many clues as to the source of the problem.
- Circular reference: A circular reference means the formula refers to its own cells. So for example, C3 =(C1+C2+C3).
Problem 2: A link is broken
Sometimes, formulas in one workbook contain references to values in another external workbook. Broken links can happen when these external workbooks are moved, deleted, or renamed.
Unfortunately, problems with linked workbooks aren’t always so obvious. If something changes in the source workbook, Excel doesn’t update and display the new value unless you tell it to. That means a formula that looks normal can be returning outdated or inaccurate values. If you notice data is missing or incorrect, this could be the culprit.
The more workbooks you link to, the more chances there are for something to go wrong, so it’s best to avoid linking to other workbooks as much as possible.
Problem 3: The data was entered incorrectly
This one isn’t a formula error, per se, but it can still cause your formulas to return incorrect or irrational values. Let’s say someone accidentally adds an extra zero when keying in a number. When Excel performs a calculation, you’ll end up with a result that’s outside the expected range.
Something as simple as an extra space before or after your entry can throw Excel for a loop, so you’ll need to enter your data carefully. However, preventing data entry errors is difficult, and spotting them once they’ve happened is even harder.
Problem 4: There’s a formatting issue
If data is formatted incorrectly, it can cause problems with calculations. Dollar signs and commas are two common sources of confusion. So are numbers formatted as text.
Errors can also happen when the formulas themselves are formatted incorrectly. Something as simple as forgetting to close parentheses or add a colon to show a range of cells can throw your whole formula out of whack.
Problem 5: The data is inconsistent
If people aren’t entering data consistently, that could be another reason your formulas aren’t coming up with the results you expected.
Let’s say you have a list of factory locations and their associated injury rates in Excel. One person types in “Chicago”, another “Chicago, IL” and another “Chicago, Illinois” — causing the records to appear different even though they are the same. If you try to analyze this data in a pivot table based on various locations, Excel will deliver multiple results for Chicago even though there should only be one.
Using data validation will help ensure the data entered stays accurate and consistent so you can retrieve the correct information for calculations. With validation turned on, you can create a rule that limits entries to values from a predefined list:
Because the data gets entered correctly in the first place you won’t have to waste time correcting issues before performing your calculations.
There’s nothing wrong with using Excel for certain situations. It’s cheap, widely available, and most people have at least a basic understanding of how to use it.
The problem occurs when you start trying to use Excel for large volumes of complex data. As the size of your workbooks grow, so too will the number of formula issues and errors. These errors can have dire consequences — particularly when your business’ compliance and reputation depends on the accuracy of your calculations.
Knowing how to find and fix these errors will go a long way in helping you avoid a problem, but ultimately the problem can only be solved by moving your data to a system that’s designed for that purpose.
Getting your spreadsheets moved over into a database doesn’t take as much time as you might expect, and can help you make great strides in your big data journey — instead of spending time fighting with formulas in Excel.