All About Excel Errors and How to Fix Them

Excel errors can stop a report cold, but every one of them is Excel telling you something specific. Excel is a powerful tool for data analysis and management, and understanding what each error means is the fastest way to get your workflow back on track.

This guide covers the ten most common Excel errors, what causes each one, and how to fix it with a real example you can test. It also explains when to hide errors with IFERROR and when not to, how to find every error in a workbook at once, and includes a free Excel error fixer and practice sheet.

Excel Error Types at a Glance

Here are the ten errors you will run into most, with what each one means and the quickest fix. The sections below walk through each one with an example.

How to Fix the 10 Most Common Excel Errors

The examples below all use the same small data set, the one in the practice sheet further down. Column G holds quantities (10 and 0), column H holds product codes (A1, B2, C3), column I holds prices, and cell J2 holds the text "12 units".

1. How to Fix the #DIV/0! Error in Excel

The #DIV/0! error occurs when a formula attempts to divide by zero or by an empty cell. It shows up constantly in margin, average, and percentage calculations when some rows have no data yet.

For example, =G2/G3 divides 10 by 0 and returns #DIV/0!. Make sure the denominator is not zero or empty, or check it first: =IF(G3=0,"",G2/G3) returns a blank until there is something to divide by.

You can also use IFERROR, as in =IFERROR(G2/G3,0), but the IF version is safer because it only handles the zero case and still lets other mistakes show.

2. How to Fix the #N/A Error in Excel

The #N/A error indicates that a value is not available to a function or formula. It usually comes from lookup functions like VLOOKUP, HLOOKUP, XLOOKUP, and MATCH when a match is not found.

Check your lookup values and make sure they exist in the data range. The most common hidden causes are extra spaces, which TRIM removes, and numbers stored as text, which look identical to real numbers but never match them.

When a miss is expected, handle it with IFNA: =IFNA(VLOOKUP("Z9",H2:I4,2,FALSE),"Not found"). XLOOKUP has this built in as its fourth argument, the value to return if nothing is found.

3. How to Fix the #VALUE! Error in Excel

The #VALUE! error appears when there is a problem with the types of values used in a formula, most often text in a numeric calculation.

For example, =G2+J2 tries to add 10 to the text "12 units" and returns #VALUE!. Make sure all the values in your formula are the right type. Here, =G2+VALUE(LEFT(J2,FIND(" ",J2)-1)) pulls the 12 out of the text and returns 22.

A cell that looks empty but contains a space causes the same error. Note that SUM ignores text in a range, so =SUM(G2,J2) returns 10 without an error, which can hide the problem instead of solving it.

4. How to Fix the #REF! Error in Excel

The #REF! error occurs when a formula refers to a cell that is not valid, usually because rows or columns were deleted or cells were pasted over. After a deletion, the formula itself changes to something like =A2+#REF!.

If you just deleted something, press Undo right away. Otherwise, check the cell references in your formula and rebuild the broken one. Referencing whole ranges, such as =SUM(A2:A10) instead of =A2+A3+A4, makes formulas survive deleted rows.

Lookups cause #REF! too. =VLOOKUP("A1",H2:I4,3,FALSE) asks for the third column of a two-column range. Changing the 3 to a 2 returns 4.5. Wrapping a #REF! in IFERROR does not fix it; it only hides a formula that is broken.

5. How to Fix the #NAME? Error in Excel

The #NAME? error appears when Excel does not recognize text in a formula. The usual cause is a typo in a function name, such as =SUMM(G2:G3) instead of =SUM(G2:G3).

Other causes include text without quotation marks, as in =IF(A2=Yes,1,0), which needs "Yes", a named range that has not been defined, and a newer function like XLOOKUP opened in an older version of Excel that does not have it.

Double-check the spelling of function names and make sure all named ranges are defined. Typing the function and picking it from the autocomplete list prevents most typos.

6. How to Fix the #NUM! Error in Excel

The #NUM! error indicates a problem with a number in your formula, such as an invalid argument in a function, or a result that is too large or too small for Excel to represent.

For example, =SQRT(G2-20) asks for the square root of -10 and returns #NUM!. Check the numbers and arguments in your formula, and guard against bad inputs: =IF(G2-20<0,"Needs a positive number",SQRT(G2-20)).

Financial functions such as IRR and RATE also return #NUM! when they cannot find an answer. Giving them a starting guess as the optional last argument often solves it.

7. How to Fix the #NULL! Error in Excel

The #NULL! error is one of the rarest. According to Microsoft, it happens when a formula uses an incorrect range operator, or uses a space between two ranges that do not intersect. A space is Excel’s intersection operator, so it asks for the cells the two ranges share.

For example, =SUM(G2 G3) returns #NULL! because G2 and G3 do not overlap. Use a comma to separate separate ranges, =SUM(G2,G3), and a colon for a continuous range, =SUM(G2:G3).

8. How to Fix the #SPILL! Error in Excel

The #SPILL! error occurs when a dynamic array formula cannot output its results because the spill range is blocked. Formulas like UNIQUE, FILTER, SORT, and SEQUENCE return several results that spill into the cells below or beside them.

Make sure there is enough empty space for the spill range, and move or delete any data in the way. Microsoft lists other causes too, including merged cells in the spill range, a spill that would run past the edge of the worksheet, and spilling formulas inside an Excel table, which are not supported.

If you need the results in one cell, wrap the formula. =TEXTJOIN(", ",TRUE,SEQUENCE(3)) returns "1, 2, 3" in a single cell instead of spilling.

9. How to Fix #### in Excel

The #### display is not really a formula error. It appears when a column is not wide enough to show its contents, which is common with dates, times, and long numbers.

Widen the column by double-clicking the right edge of its header, or change the cell format. If widening does not help, the cell probably holds a negative date or time, such as an end date subtracted from an earlier start date. Fix the calculation rather than the column.

10. How to Fix a Circular Reference in Excel

A circular reference occurs when a formula refers to its own cell, either directly or indirectly, creating an endless loop. A classic example is =SUM(A1:A10) typed in cell A10, which asks the total to include itself.

Microsoft’s guidance is to find it under Formulas, then Error Checking, then Circular References, which jumps to the cell causing the loop. Point the formula at a range that does not include its own cell.

Some financial models use circular references on purpose with iterative calculation turned on. For everyday workbooks, keep iterative calculation off so a real mistake is never hidden.

Should You Use IFERROR to Hide Excel Errors?

IFERROR and IFNA handle errors gracefully in finished reports, but they are easy to overuse. IFERROR hides every error, including a typo or a deleted reference that is giving you wrong numbers.

A good rule is to fix the formula first, then handle only the errors you expect. Use IFNA around lookups where a missing value is normal, an IF check around division, and IFERROR only when you are sure the formula itself is right. Our guide to handling and blanking out Excel errors goes deeper on the options.

How to Find All Errors in an Excel Workbook

You do not have to scroll through a sheet looking for errors. On the Home tab, choose Find and Select, then Go To Special, then Formulas, and leave only Errors checked. Excel selects every cell on the sheet that is showing an error.

To count them, use =SUMPRODUCT(--ISERROR(A1:Z500)) on a check sheet. Anything above zero means something needs attention before the report goes out. The Error Checking button on the Formulas tab steps through errors one at a time with an explanation of each.

Free Excel Error Fixer and Practice Sheet

Pick the error you are seeing in the fixer below to get what it means, the usual causes, and the fix. Paste your own formula to wrap it with IFNA, IFERROR, or a divide-by-zero check, then copy the wrapped formula straight into your sheet.

The practice sheet shows every error next to a working fix. Copy it to Excel, paste it in cell A1 of a blank sheet, and each broken formula produces its error live beside the fixed version, with a check column that confirms the fix works. The SEQUENCE and TEXTJOIN rows need Excel for Microsoft 365 or another version with dynamic arrays. Print the practice sheet to keep next to your desk.

Preventing Excel Errors Before They Happen

Excel errors can be frustrating, but they are usually straightforward to fix once you understand what they mean. By getting familiar with these common errors and their fixes, you will work faster and trust your numbers more.

Prevention is even better than a fix. Clean imported data with TRIM and VALUE, use ranges and tables instead of cell-by-cell references, and handle expected errors with IFNA or an IF check instead of blanket IFERROR. For more of the functions behind these fixes, see 10 Excel functions every user should know.

When spreadsheets start holding the work your team depends on, Updoot is business management software for $5 per user per month that keeps tasks, goals, and reports in one place without broken formulas.

Sources

How to correct a #SPILL! error (Microsoft Support)

Correct a #NULL! error (Microsoft Support)

Remove or allow a circular reference in Excel (Microsoft Support)