What #VALUE!, #REF!, #DIV/0!, ##### and other Excel errors mean, why they happen and how to fix them fast.
10 articles
#DIV/0! means a formula tried to divide by zero or by an empty cell. Fix it by checking the divisor first: =IF(B2=0,"",A2/B2) shows a blank until B2 has a number. =IFERROR(A2/B2,"") is shorter but hides every other error too. AVERAGE and AVERAGEIF also return #DIV/0! when there are no numbers to average.
#NAME? means Excel found a word in your formula it doesn't recognise. The usual causes are a misspelled function (=SUMM), text without double quotes (=IF(A2>50,Pass,Fail)), a range missing its colon (A2A9), a named range that doesn't exist, or a function your Excel version doesn't have, such as XLOOKUP in Excel 2019.
#NUM! means a formula asked for a calculation that can't give a valid number. The most common causes are DATEDIF with the start date after the end date, LARGE or SMALL asking for a position beyond the number of values, the square root of a negative number, and results too big for Excel. Fix the input, such as swapping the dates.
#REF! means a formula refers to a cell that isn't valid any more. Usually a row, column or sheet it used was deleted, or a copied formula now points off the edge of the sheet. Press Ctrl+Z straight away to undo the deletion. Otherwise, edit the formula and replace each #REF! with the right cell.
#VALUE! means a formula got the wrong type of value, most often text where it needed a number. Common causes are a word like "n/a" in a cell you add with +, a cell that holds only a space, or a date stored as text. Fix the data, or use SUM, which skips text, instead of adding cells with +.
A circular reference is a formula that refers to its own cell, directly or through other cells, such as =SUM(B2:B8) typed in B8. To find it, go to Formulas > Error Checking > Circular References, or read the status bar. Fix it by changing the range so it stops before the formula's cell, here =SUM(B2:B7).
When an Excel formula is not updating, calculation is usually set to Manual. Go to Formulas, Calculation Options and choose Automatic, or press F9 to recalculate now. If it is already Automatic, check that the formula points at cells rather than typed-in numbers, that its range includes new rows, and that the inputs are real numbers.
Excel shows a formula as text for one of four reasons: Show Formulas is turned on, the cell was formatted as Text before you typed, there is a space or apostrophe before the equals sign, or the equals sign is missing. Press Ctrl+ to switch Show Formulas off, or set the cell to General and re-enter the formula.
Numbers stored as text look like numbers but Excel treats them as words, so SUM skips them and lookups miss them. You can spot them by left alignment and a small green triangle. To fix them, select the cells, click the warning icon and choose Convert to Number, or use =VALUE(A2) in a helper column.
Excel shows ##### when a number, date or time is too wide for its column. Double-click the right edge of the column header to widen it, and the value appears. If ##### stays however wide the column is, the cell holds a negative date or time, such as an end time before a start time. Fix that formula instead.