13 Common Excel Errors and How to Fix Them (2026 Guide)

Every Excel error you'll ever see — #REF!, #VALUE!, #N/A, #SPILL!, and the rest — traces back to one of four root causes: a broken reference, a data type mismatch, a missing name, or circular logic. Once you know which family an error belongs to, fixing it stops being guesswork and becomes a five-minute task using Trace Precedents, Name Manager, and Evaluate Formula.

This guide covers all 13 error codes you'll actually run into in modern Excel (including the newer dynamic-array errors most tutorials skip), plus a free VBA macro that finds every error in a workbook in one click.

For lookup-specific failures, see Excel VLOOKUP troubleshooting. For broader model governance, see how to audit Excel workbooks.

Quick Answer: What Does Each Excel Error Mean?

• #REF! — A cell the formula depended on was deleted or moved.
• #VALUE! — The formula is mixing incompatible data types (e.g., text where a number is expected).
• #NAME? — Excel doesn't recognize a function name, defined name, or a missing quote/colon.
• #DIV/0! — The formula is dividing by zero or by an empty cell.
• #N/A — A lookup function couldn't find the value you asked for.
• #NUM! — A calculation produced a number Excel can't represent (e.g., square root of a negative number).
• #NULL! — A space was used instead of a comma or colon between ranges.
• #SPILL! — A dynamic array formula can't "spill" because something is blocking its output range.
• #CALC! — A dynamic array function returned an empty or unsupported result.
• #GETTING_DATA!/#CONNECT! — Excel is still pulling data from an external source or lost that connection.
• Circular Reference warning — A formula refers back to its own cell, directly or through a chain.
• Formula shown as text — The cell is formatted as Text instead of General/Number.
• Slow recalculation — Not a true error, but a symptom of volatile functions or oversized ranges.

Jump to any error below for the full explanation and fix, or skip straight to the free VBA error-scanner macro if you just want every error in your workbook found automatically.

Why Excel Errors Rarely Show Up Alone

In real finance and operations workbooks, errors don't arrive one at a time. A deleted helper column throws #REF!, someone wraps it in IFERROR to make the red go away, and three tabs later the totals are quietly wrong — with no visible error anywhere. Hiding an error is not the same as fixing it.

Work in this order every time:

1. Identify the first-fail cell — the earliest cell in the calculation chain that shows an error.
2. Trace Precedents (Formulas tab) to see what feeds into it.
3. Verify the source ranges and named ranges still point where you think they do.
4. Fix and test on one row, then copy the fix across the rest of the dataset — never the reverse.

1. #REF! Error

What it means: A formula references a cell, row, column, or sheet that no longer exists — almost always because someone deleted it.

How to fix:

• Undo immediately (Ctrl+Z) if the deletion just happened — this is the fastest fix by far.
• Use Find & Replace (Ctrl+H) and search for "#REF!" to locate every instance at once.
• Rebuild the formula pointing to the correct, current range.
• Switch critical formulas to named ranges so future deletes fail loudly instead of silently breaking a hardcoded reference.

2. #VALUE! Error

What it means: The formula is trying to do math on something that isn't a number — often text, a space character, or a cell formatted as Text.

How to fix:

• Check every cell in the formula for stray text, leading apostrophes, or trailing spaces.
• Wrap suspect values in `VALUE()` to force text-that-looks-like-a-number into a real number.
• Use `TRIM()` and `CLEAN()` to strip invisible characters pasted in from other systems.
• Confirm date cells are true dates, not text formatted to look like dates — this is the single most common cause of #VALUE! in imported data.

3. #NAME? Error

What it means: Excel doesn't recognize something in the formula — a misspelled function, a defined name that doesn't exist, or missing punctuation.

How to fix:

• Check for typos in function names (VLOKUP instead of VLOOKUP is the classic one).
• Open Formulas > Name Manager and confirm every named range referenced still exists.
• Make sure text strings are wrapped in quotes and ranges use a colon, not a space.
• If you're using a newer function (like `XLOOKUP` or `LET`) on a file opened in an older Excel version, that's your cause — there's no local fix except upgrading or rewriting the formula.

4. #DIV/0! Error

What it means: The formula is dividing by zero — or by a cell that's empty, which Excel treats as zero.

How to fix:

• Guard the formula: `=IF(B2=0, "", A2/B2)`
• Or wrap it: `=IFERROR(A2/B2, "N/A")`
• Check whether the "zero" is actually an empty cell from an unfilled report row — fix the source data, not just the symptom.
• Never use IFERROR as a first response — confirm why the denominator is zero before you suppress it.

5. #N/A Error

What it means: A lookup function ran successfully but couldn't find a matching value.

How to fix:

• Confirm the lookup value actually exists in the source range — check for typos on both sides.
• In VLOOKUP/HLOOKUP, verify you're using an exact match (`FALSE` or `0` as the last argument), not an approximate one.
• Strip hidden spaces with `TRIM()` — a value that looks identical but has a trailing space will always fail to match.
• Check that both sides are the same data type — "1024" (text) will never match 1024 (number) in a lookup.
• For a full breakdown of lookup-specific failures, see our dedicated [VLOOKUP troubleshooting guide](/blog/excel-vlookup-troubleshooting).

6. #NUM! Error

What it means: The formula produced a number Excel can't handle — too large, too small, or mathematically invalid (like the square root of a negative number).

How to fix:

• Check for numbers outside Excel's supported range (roughly ±1E+307).
• Review iterative formulas (like `RATE` or `IRR`) — they can return #NUM! if a solution can't be found within the guess range.
• Validate inputs before running functions like `SQRT` or `POWER` that break on negative or invalid inputs.

7. Circular Reference Warning

What it means: A formula, directly or through a chain of other cells, ends up referencing its own cell.

How to fix:

• Go to Formulas > Error Checking > Circular References to jump straight to the offending cell.
• Trace the formula chain manually if the error checker doesn't catch it (common in multi-sheet models).
• Restructure the calculation so the dependency loop is broken — usually by adding a helper cell.
• Only enable iterative calculation (File > Options > Formulas) if the circularity is intentional (common in interest-on-interest or goal-seek style models) — otherwise this setting just hides a real bug.

8. #NULL! Error

What it means: The formula uses a space between two ranges where Excel expected a comma or colon — a space is Excel's "intersection" operator, and if the ranges don't actually intersect, you get #NULL!.

How to fix:

• Replace the space with a comma (union) or colon (range): use `=SUM(A1:A10)`, not `=SUM(A1 A10)`.
• Double-check any formula that was typed manually rather than built with the mouse or Name Box.

9. #SPILL! Error

What it means: You're using a dynamic array formula (like `UNIQUE`, `SORT`, `FILTER`, or `SEQUENCE`) and the range it needs to "spill" into isn't empty.

How to fix:

• Clear the cells below/beside the formula that are blocking the spill range.
• Check for merged cells in the spill path — dynamic arrays cannot spill into a merged cell.
• If the array is unpredictable in size, wrap it or plan extra blank space around it deliberately.
• This is the most common new error we see in workbooks built after upgrading to Excel 365 — if your templates predate 2021, this is likely your first encounter with it.

10. #CALC! Error

What it means: A dynamic array function returned an empty result, or two array formulas are conflicting with each other.

How to fix:

• Check that the array function isn't returning zero elements (e.g., a `FILTER` with no matching rows).
• Add a fallback: `=IFERROR(FILTER(A:A,B:B="X"), "No results")`
• Confirm you're not nesting array functions in a way Excel doesn't support in that formula position.

11. #GETTING_DATA! and #CONNECT! Errors

What it means: Excel is either still retrieving data from an external source (a Power Query refresh, a linked cell, or a real-time data type) or has lost that connection entirely.

How to fix:

• If it's `#GETTING_DATA!`, it's often transient — wait for the refresh to complete.
• For `#CONNECT!`, check the data source: has the file moved, the API key expired, or the linked workbook been renamed?
• Go to Data > Queries & Connections to see the connection status and refresh manually.
• For workbooks that rely heavily on live connections, consider whether the model has outgrown Excel — see our comparison of [Excel vs. Access](/blog/excel-vs-access-comparison-guide) for when a real database back end solves this permanently.

12. Formula Shows as Text Instead of Calculating

What it means: The cell is formatted as Text, or the formula starts with a leading apostrophe.

How to fix:

• Select the cell(s), change the format to General or Number.
• Delete any leading apostrophe.
• Press F2, then Enter, to force Excel to re-evaluate the formula.
• For a whole column, use Data > Text to Columns > Finish — it forces a full re-parse.

13. Slow Calculation Performance

What it means: Not a red-triangle error, but it behaves like one — the workbook lags or freezes on every edit.

How to fix:

• Switch to Manual calculation (Formulas > Calculation Options) while building, then recalc with F9 when needed.
• Replace volatile functions (`NOW`, `TODAY`, `RAND`, `OFFSET`, `INDIRECT`) with static or non-volatile alternatives where possible.
• Prefer `INDEX/MATCH` or `XLOOKUP` over `VLOOKUP` on large ranges — they don't force Excel to scan every intervening column.
• Move heavy transformation logic to Power Query instead of thousands of live in-cell formulas.

Free VBA Macro: Scan Your Entire Workbook for Errors in Seconds {#vba-error-scanner}

Manually hunting for red-triangle errors across a 30-tab workbook wastes hours. This macro loops through every worksheet, finds every error cell, and builds a report with the sheet name, cell address, error type, and the underlying formula — so you can triage everything from one place.

To use it: open the VBA editor (Alt+F11), insert a new Module, paste the code below, and run `FindAllWorkbookErrors`.

Sub FindAllWorkbookErrors()
    Dim ws As Worksheet
    Dim cell As Range
    Dim reportWs As Worksheet
    Dim reportRow As Long
    Dim errorCount As Long

    ' Remove any previous report sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Error Report").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set reportWs = ThisWorkbook.Worksheets.Add
    reportWs.Name = "Error Report"
    reportWs.Range("A1:D1").Value = Array("Sheet", "Cell", "Error Type", "Formula")
    reportWs.Range("A1:D1").Font.Bold = True

    reportRow = 2
    errorCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Error Report" Then
            Dim usedRng As Range
            Set usedRng = ws.UsedRange

            For Each cell In usedRng
                If Application.WorksheetFunction.IsError(cell) Then
                    reportWs.Cells(reportRow, 1).Value = ws.Name
                    reportWs.Cells(reportRow, 2).Value = cell.Address
                    reportWs.Cells(reportRow, 3).Value = CStr(cell.Text)
                    reportWs.Cells(reportRow, 4).Value = "'" & cell.Formula
                    reportRow = reportRow + 1
                    errorCount = errorCount + 1
                End If
            Next cell
        End If
    Next ws

    reportWs.Columns("A:D").AutoFit

    MsgBox errorCount & " error(s) found across the workbook." & vbCrLf & _
           "See the 'Error Report' sheet for full details.", _
           vbInformation, "Error Scan Complete"
End Sub

What it does: Creates a fresh "Error Report" sheet, walks every used cell on every other sheet, flags anything Excel classifies as an error, and logs the exact formula that produced it — so you can fix root causes instead of chasing red triangles one tab at a time.

Want this wired into a scheduled audit, a ribbon button, or triggered automatically before a file is emailed out? See our VBA automation services.

Prevention Checklist

1. Use Data Validation to block invalid entries at the source, before they become formula errors downstream.
2. Create Named Ranges so formulas stay readable and don't silently point at the wrong cell after a sort or insert.
3. Document formulas with cell comments wherever the logic isn't obvious from the formula itself.
4. Test formulas on sample data before rolling them out across a full dataset.
5. Keep regular backups or use version history so you can roll back to a known-good state after a bad edit.
6. Separate input, logic, and output sheets so a stray keystroke on a raw-data tab can't quietly break a formula three sheets away.
7. Add control totals — a simple `=SUM()` check that turns red when row counts or totals drift unexpectedly is worth more than any amount of IFERROR wrapping.
8. Standardize date and number formats before importing external data — most #VALUE! errors trace back to inconsistent import formatting.
9. Avoid merged cells in any range that dynamic array formulas might need to spill into.
10. Audit before you automate — running the VBA macro above on a schedule catches new errors before they reach a client or a board deck.

Frequently Asked Questions

When to Bring in Professional Help

If the same errors keep coming back across templates, or reappear after every file close, the issue is process and structure — not a missing IFERROR. Excel consulting can standardize your models end to end, add VBA automation for validation and error scanning, and help you decide when Excel vs. Access is the right long-term architecture for the data you're managing.