Excel Tutorials · Free Download
Eight errors cover almost everything Excel throws at you, and each one names a different category of problem. Once you know which category you are in, most of them take under a minute to fix. This page covers what each error means, what causes it, and how to repair it rather than hide it, with a free printable guide to keep next to your monitor.
Every Excel error, its cause and its fix, on a printable reference sheet. No login, no email required.
An Excel error is not a crash and not a bug. It is the answer to a question the formula could not resolve, returned in a format designed to be impossible to ignore. Excel could have left the cell blank, and the fact that it refuses to is the most useful thing about the whole system.
Each error name is a diagnosis rather than a generic complaint. #REF! means a reference is gone. #VALUE! means the wrong type of data arrived. #NAME? means Excel did not recognize something you typed. Reading the name before touching anything narrows the search from the whole workbook to one specific kind of mistake, which is why the fix is usually quick once you stop guessing.
Errors also travel. A single #DIV/0! in one cell propagates through every formula that references it, so a summary tab showing thirty errors frequently has one actual cause sitting several tabs upstream. Tracing back to the origin rather than patching the visible symptom is most of the work.
That is also the argument against reflexively wrapping everything in IFERROR. It suppresses all eight error types equally, so a genuine broken reference disappears just as quietly as the divide by zero you were expecting, and the spreadsheet keeps reporting numbers that are no longer true.
The same five steps work regardless of which error you are looking at.
The text between the hash and the punctuation is the diagnosis. Each of the eight points at a different category of problem, so identifying it first stops you from checking things that could not possibly be the cause.
Press Ctrl + G, choose Special, select Formulas and tick only Errors. Excel highlights every erroring cell on the sheet, which shows whether you have one problem or thirty.
Use Trace Precedents on the Formulas tab, or Ctrl + [, to jump to the cells feeding the formula. Errors propagate downstream, so the cell you are staring at is often just a victim of one several steps back.
Highlight a fragment in the formula bar and press F9 to see what that piece alone returns. Press Escape afterwards, never Enter, or the fragment is permanently replaced with its hardcoded value.
Repair the underlying problem first. Wrap a formula in IFERROR only for errors you genuinely expect, such as a division where zero is a legitimate value, not as a way to make the sheet look clean.
| Error | What Went Wrong | First Thing to Check |
|---|---|---|
| #SPILL! | A dynamic array had nowhere to put its results | Cells below or right of the formula |
| #NAME? | Excel did not recognize what you typed | Function spelling and missing quotation marks |
| #NUM! | The answer is impossible or too large | Negative roots, huge exponents, bad arguments |
| #NULL! | Two ranges were asked to intersect and do not | A stray space between ranges |
| #REF! | A referenced cell no longer exists | Recently deleted rows, columns or sheets |
| #DIV/0! | Divided by zero or by an empty cell | The divisor, not the formula itself |
| #CALC! | The engine could not produce a result | A dynamic array that returned nothing |
| #VALUE! | The wrong type of data was supplied | Numbers stored as text, stray spaces |
Print this table and most error hunts turn into a ten second lookup. The pattern worth internalizing is that #REF! and #NAME? are structural problems in the formula itself, while #VALUE!, #DIV/0! and #NUM! are almost always problems with the data being fed into it.
The newest error, and the one that confuses experienced users most.
A dynamic array formula such as FILTER, SORT, UNIQUE or a modern XLOOKUP tried to return several results at once and something is occupying the cells it needed. A single stray space in one cell three rows down is enough to block the whole thing.
Click the formula cell and Excel outlines the spill range it wanted with a dashed border. Clear anything sitting inside that outline, including cells that look empty but contain a space, and the results appear on their own with no edit to the formula.
A spill range that would run past the last row of the sheet, a formula entered inside an Excel Table, which does not support spilling, or a merged cell sitting in the path. Converting the table back to a range or unmerging the cell resolves both.
Nearly always a typing problem rather than a logic problem.
VLOOKUP typed as VLOOKP, or a function that does not exist in your version of Excel. If a colleague on Microsoft 365 sends a workbook using XLOOKUP or TEXTSPLIT and you are on an older perpetual version, the function is genuinely unrecognized on your machine.
Writing =IF(A1=Yes,1,0) instead of =IF(A1="Yes",1,0) makes Excel treat Yes as a named range it cannot find. Any literal text inside a formula needs quotation marks around it.
If a named range was deleted or renamed, every formula using the old name breaks. Open Name Manager with Ctrl + F3 to see what actually exists in the workbook.
The most destructive of the eight, and the least recoverable.
A row, column or entire worksheet that a formula depended on was deleted. Excel cannot substitute a sensible replacement, so the reference itself is overwritten with #REF! inside the formula, permanently.
Unlike the other errors, the original address is not recoverable once you save and close. If it just happened, press Ctrl + Z immediately rather than investigating, because the information you need to rebuild the formula disappears with the undo stack.
Named ranges and structured table references survive row and column deletion far better than raw cell addresses, which is the practical argument for using Ctrl + T on any data block a formula depends on.
| Cell Contents | Formula | Result |
|---|---|---|
| A1: 100 A2: 50 | =A1+A2 | 150, working normally |
| A2 pasted from a website as " 50" | =A1+A2 | #VALUE!, the space makes it text |
| Same data | =A1+VALUE(TRIM(A2)) | 150, converted back to a number |
Numbers that arrive from a website, a PDF or an exported system routinely carry leading spaces or non breaking characters that are invisible on screen. The quickest tell is alignment, since Excel right aligns real numbers and left aligns text by default. A column of left aligned figures is text pretending to be numeric.
Three errors that all mean the formula is fine and the inputs are not.
Arithmetic on something Excel considers text. Beyond stray spaces, common sources are a date typed in an unrecognized format, a currency symbol typed directly into the cell, or a cell holding an error that then feeds into your formula.
Rather than IFERROR, test the divisor directly with something like =IF(B2=0,"",A2/B2). This hides only the divide by zero case you predicted and lets a genuine #REF! or #VALUE! still surface, which a blanket IFERROR would swallow.
The square root of a negative number, a result larger than Excel can represent, or an iterative function such as IRR or RATE that cannot converge. For IRR and RATE, supplying a reasonable guess argument usually resolves it.
A space between two ranges is the intersection operator, so =SUM(A1:A5 B1:B5) asks for cells the two ranges share. Where they do not overlap there is nothing to return. A comma unions them, a colon spans them.
Rare, modern, and almost always about an empty array.
When FILTER matches nothing at all it has no result to return. Supplying the optional third argument, as in =FILTER(A2:C50,B2:B50="West","No matches"), replaces the error with something readable.
Excel cannot return an array of arrays, so a formula that would produce one raises #CALC! instead. Restructuring the formula to return a single array, often by splitting it across two cells, is the fix.
#CALC! and #SPILL! only exist in Microsoft 365 and Excel 2021 and later. On an older version the same formula fails differently or the function is unavailable entirely, which is worth knowing before debugging a file that works fine on somebody else's machine.
It hides all eight error types equally, so a broken reference vanishes as quietly as an expected divide by zero and the sheet keeps reporting numbers that are no longer true. Fix the cause, then suppress only what you predicted.
Errors propagate downstream, so thirty errors on a summary tab often trace to one cell several tabs back. Trace Precedents before editing anything.
The original reference is overwritten and unrecoverable once the file is saved. #REF! is the one error where investigating first costs you the information needed to repair it.
Manually retyping a column works but does not scale and does not stop it recurring at the next import. Text to Columns on the column, or a VALUE and TRIM formula, handles the whole thing in one pass.
The blocking cell sometimes contains real data somebody needs. Check what is inside the dashed spill outline before clearing it rather than after.
They are not errors, but they flag numbers stored as text and formulas inconsistent with their neighbors, which are the two conditions that produce errors later. They are early warnings, not clutter.
A broken reference should not be able to take down a report.
Free 14-day trial. No credit card required.
A dynamic array formula tried to return several results but something is sitting in the cells it needed. Clear the blocking cells below or to the right of the formula and the results appear on their own.
Excel does not recognize something you typed, almost always a misspelled function name, a named range that does not exist, or text used without quotation marks around it.
The formula is mathematically valid but the answer is impossible or too large to represent, such as the square root of a negative number or an iterative calculation that never settles.
Two ranges were separated by a space, which asks Excel for the cells they share. If the ranges do not overlap there is nothing to return. Use a comma or a colon instead.
The formula points at a cell that no longer exists, usually because a row, column or worksheet was deleted after the formula was written. Undo immediately if it just happened, since the original address is gone once you save.
The formula divided by zero or by an empty cell. Wrap it in IFERROR, or better, use an IF test on the divisor so a genuine zero shows a blank rather than hiding other errors too.
The calculation engine cannot produce a result, most often because a dynamic array function such as FILTER returned nothing at all. Supply the optional if_empty argument to control what shows instead.
The formula received the wrong type of data, most commonly a number stored as text or a stray space in a cell being used in arithmetic.
No. IFERROR hides every error type, so a #REF! caused by a deleted column disappears just as quietly as a #DIV/0! you expected. Fix the cause first and use IFERROR only for errors you predicted.
Numbers pasted from a website or exported system often carry hidden spaces or non breaking characters. Left aligned numbers are the tell, and Text to Columns or the VALUE function converts them back.
Press Ctrl G, choose Special, then Formulas and tick only Errors. Excel selects every erroring cell on the sheet so you can see the full scope before fixing anything.
It is an error checking warning rather than an error itself, flagging things like a number stored as text or a formula inconsistent with its neighbors. Click the cell and the warning icon explains what Excel noticed.
Every business starts with a spreadsheet. Updoot is where you scale past it, with structured records that cannot return a #REF!.
Start Your Free Trial →