Excel formula errors look alarming, but they’re usually helpful clues rather than signs of a broken spreadsheet. When a cell shows #DIV/0!, #N/A, or one of the other error codes, Excel is telling you something specific about the formula, the data it depends on, or the space available on the worksheet.
The fastest way to fix an Excel error is to read the code first, then check the cells the formula relies on. Often the fix is small: widen a column, correct a typo, remove an extra space, or confirm that a lookup value actually exists. This guide walks through the most common Excel formula errors, what causes each one, and how to fix it without papering over a real data problem.
What Each Error Code Means, at a Glance
If you just need to know what you’re looking at, here’s the short version. Details and fixes for each one follow below.
- ### – the column is too narrow to display the value
- #DIV/0! – the formula is dividing by zero or an empty cell
- #VALUE! – the formula received the wrong type of data
- #NAME? – Excel doesn’t recognise a function name, range name, or text value
- #REF! – a cell reference no longer exists
- #N/A – a lookup formula couldn’t find a match
- #NUM! – the calculation produced an invalid number
- #NULL! – two referenced ranges don’t intersect
- #SPILL! – a dynamic array has no room to expand
- #CALC! – Excel can’t complete an array calculation
###: The Column Is Too Narrow
A row of hash symbols isn’t a formula error at all. It usually just means Excel has calculated a value but can’t display it in the current column width. This shows up most often with dates, long numbers, currency, and percentages. You may also see hashes when a cell holds a negative date or time value that Excel’s date system can’t represent.
How to fix it
- Move the pointer to the right edge of the column heading.
- When the pointer turns into a double arrow, double-click to auto-fit the column width.
- Or drag the column boundary to the right manually.
If widening the column doesn’t clear the hashes, check the cell’s format and value. A negative date or time usually needs a different calculation approach, not just more space.
#DIV/0!: A Formula Is Dividing by Zero
The #DIV/0! error appears when a formula divides by zero or by an empty cell that Excel treats as zero. It’s a regular visitor in percentage calculations, averages, unit costs, growth rates, and dashboard metrics. A formula like =A2/B2 will return this error if B2 is blank or contains zero.
How to fix it
- Check the divisor. Look at the cell after the division sign and confirm it holds a valid, non-zero number.
- Account for incomplete data. If the denominator will be filled in later, adjust the formula so it doesn’t try to calculate before the value exists.
- Use an IF check. A formula such as =IF(B2=0,0,A2/B2) returns zero when B2 is zero and does the division otherwise.
- Use IFERROR carefully. A formula such as =IFERROR(A2/B2,0) hides the error, but it also hides other problems. Only use it when zero is genuinely the right fallback.
In reports, a blank result is often clearer than a zero when data is simply missing. You can return a blank with a nested IF, or use whatever blank-cell convention the rest of your workbook follows.
#VALUE!: The Formula Received the Wrong Type of Data
The #VALUE! error means Excel can’t use one of the values fed into a formula. A calculation might be receiving text where it expects a number, a date function might be getting invalid text, or a range might contain an unexpected character. A simple example: trying to add a number to a cell that contains a status word like Pending. Excel can’t add text and numbers with a plain addition formula.
Common causes
- Numbers stored as text after importing data from another system.
- Hidden spaces before or after a value.
- Currency symbols or other special characters entered as text rather than formatted numbers.
- A formula referencing text when it expects a number or date.
- An incompatible range or argument inside a function.
How to fix it
- Select the error cell and check the formula bar to see which cells it references.
- Inspect those cells for text, stray spaces, apostrophes, or symbols where a number should be.
- Use TRIM to strip unwanted spaces from imported text.
- Convert text-formatted numbers into real numbers, either through Excel’s warning menu or with a formula like =VALUE(A2).
- Confirm that anything you’re treating as a date is a genuine Excel date, not text that merely looks like one.
A quick test: reformat a suspicious cell as General. If it doesn’t behave like a number afterward, it’s probably still stored as text.
#NAME?: Excel Doesn’t Recognise Part of the Formula
The #NAME? error almost always points to a spelling mistake. Excel has run into a function name, named range, or text value it doesn’t recognise. Typing =SUMM(A2:A10) instead of =SUM(A2:A10) triggers this because SUMM isn’t a real function. It can also happen when text is used in a formula without the required quotation marks around it, since Excel will then try to interpret the word as a named range and fail to find one.
How to fix it
- Check function names. Reread every function in the formula for typos.
- Use Formula AutoComplete. Start typing a function name and accept the suggestion with Tab instead of typing the whole thing out.
- Check named ranges. Open Name Manager on the Formulas tab and confirm the name exists and is spelled the way you typed it.
- Review text arguments. Make sure text values are wrapped in quotation marks the way Excel expects.
- Check your list separator. Some regional Excel settings use semicolons between function arguments instead of commas.
#REF!: A Cell Reference Is No Longer Valid
The #REF! error means a formula points to a cell, range, row, column, or sheet that no longer exists. It typically shows up after cells are deleted or data is moved without updating the formulas that depended on it. If a formula refers to a column that later gets deleted, Excel replaces that part of the formula with #REF! rather than guessing what you meant.
How to fix it
- If the deletion just happened, hit Undo immediately. It’s often the cleanest fix available.
- If Undo is no longer an option, edit the formula and replace the broken reference with the correct cell or range.
- Check whether a deleted worksheet was referenced by formulas elsewhere in the workbook.
- Use Find & Replace to search the workbook for #REF! so you can locate every affected formula at once.
To avoid future reference errors, check where a column, row, or sheet is used before deleting it. Trace Dependents, on the Formulas tab, will show you which formulas rely on a selected cell before you make changes.
#N/A: Excel Couldn’t Find a Match
The #N/A error means a lookup formula couldn’t find the value you asked for. It shows up constantly with VLOOKUP, HLOOKUP, XLOOKUP, MATCH, XMATCH, and INDEX combined with MATCH. It’s worth remembering that #N/A isn’t always a mistake. If an employee ID, product code, or customer record genuinely isn’t in the lookup table, #N/A is Excel accurately reporting that there’s no match to find.
How to fix it
- Confirm the lookup value. Check for spelling differences, extra spaces, or hidden characters.
- Match data types. A numeric ID and the same ID stored as text won’t match each other.
- Check the lookup range. Make sure it actually covers the records you expect to find.
- Force exact matching when you need it. With VLOOKUP, use FALSE or 0 for an exact match unless you specifically want an approximate one.
- Handle expected gaps. Use IFNA to return a controlled fallback result when a missing value is a normal, expected outcome rather than an error.
If you’re using XLOOKUP, its built-in not-found argument does the same job as wrapping the formula in IFNA, and it keeps the formula shorter and easier to read.
#NUM!: The Number or Calculation Is Invalid
The #NUM! error appears when Excel can’t produce a valid numeric result. It usually comes from an impossible calculation, an unsupported result, or a formula that can’t converge on an answer. A classic example is asking for the square root of a negative number with a standard worksheet formula, which has no real-number solution.
How to fix it
- Check your inputs. Make sure they fall within the range the function actually supports.
- Review financial functions. Functions like IRR can return #NUM! when the cash flows you’ve supplied don’t allow Excel to find a solution.
- Check date logic. Invalid dates or dates outside Excel’s supported range can trigger numeric errors too.
- Don’t force a result blindly. Wrapping a value in ABS to make a negative number positive will remove the error, but it may also change what the calculation actually means. Only do this when the underlying logic supports it.
If a long, nested formula returns #NUM!, break it into steps using helper cells. It’s much easier to spot the failing input when each stage of the calculation is visible on its own.
#NULL!: Two Ranges Don’t Intersect
The #NULL! error is rare, but it’s easy to fix once you know the cause. It happens when a formula asks Excel to find the intersection of two ranges that don’t actually overlap. In Excel’s syntax, a space between two references acts as an intersection operator, and if there’s no overlap, you get #NULL! instead of a value.
How to fix it
- Use a colon for one continuous range. =SUM(A1:B10) sums the rectangular block from A1 through B10.
- Use the correct separator for multiple ranges. Many Excel installations use a comma, as in =SUM(A1:A10,B1:B10), while some regional settings use a semicolon instead.
- Look for accidental spaces. These often creep in after copying or editing a formula.
#SPILL!: A Dynamic Array Can’t Expand
The #SPILL! error shows up in versions of Excel that support dynamic arrays. Functions like FILTER, SORT, UNIQUE, and SEQUENCE can return multiple results from a single formula, and Excel automatically places those results into the surrounding cells, a process known as spilling. If something is blocking that output area, Excel returns #SPILL! rather than overwriting whatever’s already there.
How to fix it
- Select the cell showing the #SPILL! error.
- Look at the outlined spill range Excel highlights.
- Clear, move, or correct whatever is blocking that range.
- Check for stray spaces or invisible content, not just visible values, since these can block a spill too.
- Make sure the formula isn’t inside an Excel Table, since dynamic arrays can’t expand within one.
- Remove any merged cells sitting inside the intended spill area.
If you need a dynamic array result next to a table, place the formula outside the table entirely so it has room to grow.
#CALC!: Excel Can’t Complete an Array Calculation
The #CALC! error is most often tied to newer dynamic array formulas. It signals that Excel understood the formula but couldn’t finish computing it as written. One common trigger is FILTER returning no records when you haven’t supplied a fallback for that situation. More complex array formulas can also return #CALC! when they produce an unsupported empty result or combine arrays that don’t fit together logically.
How to fix it
- Check whether a FILTER result is empty. Supply a fallback using the function’s optional if-empty argument.
- Test each part of a nested formula separately. Put intermediate steps in their own cells so you can see exactly where things go wrong.
- Review array sizes. Functions that combine multiple arrays usually need them to be compatible in size and shape.
- Simplify where you can. A couple of helper columns are often easier to audit than one deeply nested formula trying to do everything at once.
Do Not Hide Every Error With IFERROR
IFERROR is genuinely useful, but it gets overused. Wrapping every formula in IFERROR makes a worksheet look clean while quietly hiding missing data, broken references, and faulty logic underneath. A cleaner approach is to match the error-handling tool to the actual problem:
- Use IFNA when a lookup may legitimately return no match.
- Use IF when you need to check a specific condition, such as a zero divisor, before it becomes an error.
- Use IFERROR only when several possible error types should all lead to the same deliberate fallback.
Before reaching for any of these, ask whether the error is actually signalling a real problem worth fixing at the source rather than masking.
Useful Excel Tools for Finding Formula Problems
Evaluate Formula
For long or nested formulas, Evaluate Formula walks through each calculation step in order. Select the problem cell, open the Formulas tab, and choose Evaluate Formula. Excel highlights the next part it’s about to calculate, which makes it much easier to spot exactly where an error or an unexpected value first appears.
Trace Precedents and Trace Dependents
Trace Precedents draws arrows to the cells feeding into your selected formula. Trace Dependents does the reverse, showing which formulas depend on the cell you’ve selected. Both are especially handy when a value looks wrong but isn’t throwing an obvious error code.
Error Checking
Excel sometimes flags a possible issue with a small green triangle in the corner of a cell. Click that cell to see the available options. Excel can flag things like numbers stored as text or formulas that break an otherwise consistent pattern across a row or column.
Show Formulas
On a worksheet packed with formulas, Show Formulas (on the Formulas tab) displays the formulas themselves instead of their results. It’s a fast way to spot inconsistent references or accidentally hard-coded numbers hiding among your formulas.
Preventing Excel Formula Errors
The best troubleshooting is the kind you never have to do. Keep input data consistent, avoid mixing text and numbers in the same field, and label your source columns clearly. When you build a new formula, test it against normal values, blank cells, zeros, missing lookup values, and anything else unexpected that might land in that column later.
It also helps to avoid hard-coding numbers directly inside formulas when those numbers might change. Put assumptions in clearly labelled cells instead, then reference those cells in your calculations. It takes a little longer to set up, but it makes your formulas far easier to audit, update, and trust six months from now.
Excel errors aren’t random noise. Each one points to a specific category of problem: missing data, invalid references, mismatched data types, unsupported calculations, or simply not enough room on the worksheet. Once you know what each code is actually telling you, fixing common Excel formula errors stops being guesswork and becomes a routine, methodical process.
