Every Excel Error Explained: #N/A to #SPILL!

Excel error types each start with a # sign and tell you what kind of problem a formula hit: #N/A for a value that wasn't found, #DIV/0! for division by zero, #REF! for a cell that no longer exists, and six more. Once you know what each one means, most take seconds to fix. This guide lists every common error with its cause and fix, for anyone who has met an error in a report or a job test and wants to know what it is saying.

Excel error types at a glance#

Each error has one main meaning. Use this table to find yours, then read its section below.

Errors also spread. If C2 shows #N/A, any formula that uses C2, such as a total, shows #N/A too. Always fix the first error in the chain.

Lookup and reference errors: #N/A and #REF!#

These two errors come from formulas that point at the wrong place.

#N/A#

#N/A means "not available": a lookup function such as VLOOKUP, XLOOKUP or MATCH couldn't find the value. =VLOOKUP("P-110",A2:B6,2,FALSE) returns #N/A when P-110 isn't in column A. Common causes are typos, extra spaces, and numbers stored as text on one side only. It is often an expected result, so it is safe to handle with IFNA. The guide on fixing the #N/A error covers each cause.

#REF!#

#REF! means the formula refers to a cell that doesn't exist. It appears when you delete a row, column or sheet that a formula used, or when VLOOKUP asks for a column outside its range: =VLOOKUP(D2,A2:B6,3,FALSE) asks for column 3 of a 2-column range. Press Ctrl+Z (⌘Z on a Mac) straight away to undo a deletion. See how to fix the #REF! error for the rest.

Math errors: #DIV/0! and #NUM!#

These errors come from calculations that have no valid answer.

#DIV/0!#

#DIV/0! appears when a formula divides by zero or by an empty cell, as in =B2/C2 when C2 is blank. AVERAGE on cells with no numbers gives it too. Check the divisor first: =IF(C2=0,"",B2/C2). The #DIV/0! error guide shows more options.

#NUM!#

#NUM! means the number is impossible or too large. Examples: =SQRT(-4), DATEDIF with the start date after the end date, or a number beyond Excel's limits. Financial functions such as IRR also return #NUM! when they can't find an answer. Check the inputs, not the formula.

Formula errors: #VALUE!, #NAME? and #NULL!#

These errors usually mean the formula itself is written wrongly or fed the wrong data.

#VALUE!#

#VALUE! means a value is the wrong type. ="ten"+5 gives #VALUE! because text can't be added. More often, a cell that looks like a number or a date is really text, or contains a hidden space. SUMIFS also returns #VALUE! when its ranges are different sizes. Convert text numbers with VALUE and clean spaces with TRIM.

#NAME?#

#NAME? means Excel doesn't recognise a word in the formula. The usual causes are a misspelled function, like =SUMM(A2:A9), text without double quotes, like =COUNTIF(A2:A9,Open), or a function your version doesn't have, like XLOOKUP in Excel 2016. The article on the #NAME? error goes through each case.

#NULL!#

#NULL! is rare. In Excel, a space between two ranges means "where they overlap". =SUM(A1:A5 C1:C5) returns #NULL! because the ranges don't overlap. You almost always meant a comma, =SUM(A1:A5,C1:C5), or a colon.

Dynamic array errors: #SPILL! and #CALC!#

These two errors only appear in Excel 2021 and Microsoft 365, where one formula can return many values.

#SPILL!#

#SPILL! means a formula wants to fill several cells, but something is in the way. FILTER, UNIQUE, SORT and SEQUENCE spill their results down or across. If any of those cells hold data, even a space, the formula shows #SPILL!. Click the error to see a dashed border around the spill area, then clear it. Spill formulas also don't work inside an Excel table.

#CALC!#

#CALC! means Excel can't finish the calculation. The most common case is FILTER finding no matches, because an empty result isn't allowed. Give FILTER a third argument: =FILTER(A2:A9,B2:B9="Finance","None").

Microsoft 365 has a few rarer errors, such as #FIELD!, #CONNECT! and #BLOCKED!. They appear with linked data types and external data, not in everyday formulas.

What ##### means (it isn't an error)#

A cell full of # signs is not an error. It means the column is too narrow to show the number or date. Double-click the right edge of the column header to fit it. If the # signs stay, the cell holds a negative date or time, often from subtracting a later date from an earlier one. Excel can't show dates before 1900, so check the order of the dates.

How to find and handle errors#

Excel marks many errors with a small green triangle in the top-left corner of the cell. Click the cell and the warning icon to see what Excel thinks is wrong. The Formulas tab has Error Checking, which jumps to each error in turn, and Evaluate Formula, which shows the formula working one step at a time.

When an error is expected, handle it with IFERROR or IFNA:

=IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Not found")

This shows "Not found" instead of #N/A. IFERROR catches every error type. IFNA catches only #N/A, which is safer for lookups because a #REF! or #NAME? still shows. The IFERROR function guide covers the syntax, and the article on using IFERROR the right way explains which errors you should never hide.

To test for errors, ISERROR returns TRUE for any error and ISNA only for #N/A. ERROR.TYPE returns a number for each classic error, from 1 for #NULL! to 7 for #N/A.

Practise it#

Practise fixing lookup errors in a live spreadsheet: you find the cause of each #N/A and fix it, and the sheet checks your answer instantly. Then practise IFERROR to handle the errors you expect, starting with the exercise on this page.