Excel guide · 8 min read
Why is my Excel formula not working?
Still not working on your sheet? Paste your cells and the formula into Cellwise Fix. The engine shows which cause it is on your data, then you get the fix, checked. Open Cellwise Fix
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | What you see | |
| 2 | 1001 | North | 1200 | =SUM(C2:C6) | |
| 3 | 1002 | North | 850 | ||
| 4 | 1003 | South | 640 | ||
| 5 | 1004 | North | 975 | ||
| 6 | 1005 | East | 410 |
Download this sheet as an Excel practice file (.xlsx)
The formula shows as text, or the result never changes
These are settings problems, not formula problems, so check them first: the formula can be perfect and still do nothing.
- You see
=SUM(C2:C6)instead of a number. The cell was formatted as Text before you typed, there is a space or apostrophe before the =, or Show Formulas is on (Formulas > Show Formulas, or Ctrl+`). E2 in the sample starts with a space, so it is just text. Fix: Excel showing formula instead of result. - The result does not update when you change the inputs. Calculation is set to Manual, often by a workbook someone else saved that way. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. Fix: Excel formula not calculating or updating.
- You fill a formula down and every row shows the same answer. Same cause (Manual calculation), or the reference is absolute ($C$2) when it should move. Fix: formula not dragging down and absolute references.
- A warning about a circular reference, and a 0. The formula refers to its own cell, directly or through another cell. Fix: find and fix a circular reference.
- The status bar shows no Sum when you select numbers. The cells hold text, or the status bar item is switched off. Fix: status bar not showing sum.
The result is 0 or too small: numbers stored as text
If the result is exactly 0, go through Excel formula returns 0, which tests the seven usual causes one by one.
This is the most common silent problem. Numbers that came from a CSV export, a web page or another system often arrive as text. They look normal, but they sit on the left of the cell, often with a small green triangle, and SUM, SUMIF and AVERAGE skip them without any error.
In the sample, C4 (640) and C5 (975) are text. SUM adds only the three real numbers, so the total is 2,460 instead of 4,075. Nothing on screen tells you something is missing.
Result: 2460
2460: only 1200 + 850 + 410. The two text amounts are skipped.
Quick check: COUNT against the number of rows
COUNT counts only real numbers. If it is lower than the number of filled cells, some of them are text. Here it returns 3 for 5 amounts, so two are text. ISTEXT on a single cell confirms which ones: =ISTEXT(C4) is TRUE.
To fix the data for good, select the column and use Data > Text to Columns > Finish, or click the green triangle warning and choose Convert to Number. All the methods are in convert numbers stored as text. If you cannot change the source (it is refreshed every day), convert inside the formula instead: the double minus in =SUMPRODUCT(--C2:C6) turns each text number into a number and returns 4,075.
The same cause makes SUMIF return 0 (SUMIF returning 0, SUMIFS not working), makes lookups miss values that are clearly there (VLOOKUP not working), and makes dates refuse to sort (dates not sorting). Other reasons for a 0: a lookup that finds an empty cell returns 0 (VLOOKUP returns 0 instead of blank), AutoSum grabbed the wrong cells (AutoSum selecting the wrong range), or the value is not 0 at all but a small decimal shown with no decimal places.
Result: 3
3 numbers out of 5 filled cells: two amounts are stored as text.
The result is wrong: hidden spaces and moving references
A count or sum that is a little off, with no error, usually means some rows do not match the criteria you think they match. The usual cause is a trailing space typed by hand or left by an export: "North " is not "North" to Excel. Here COUNTIF finds only 2 North orders out of 3, because B3 has a space at the end. =LEN(B3) returns 6 for a 5-letter word, which proves it.
Fix the data with TRIM (see remove spaces in Excel), or compare trimmed values in the formula: =SUMPRODUCT(--(TRIM(B2:B6)="North")) counts all 3. The same space breaks lookups and is cause number one in COUNTIF not working, COUNTIFS not working, XLOOKUP not working and INDEX MATCH not working.
The other common cause of a wrong answer is a reference that slides when you copy the formula. A rate in F1 becomes F2, F3 and so on down the column, pointing at empty cells. Lock it with $F$1. And a VLOOKUP without FALSE as the last argument does an approximate match, which returns a wrong row on unsorted data instead of #N/A (the VLOOKUP function page shows exact and approximate match side by side).
Result: 2
2, not 3: "North " in B3 has a trailing space, so it does not match.
An error value appears: what each one means
Error values are easier than wrong answers, because Excel is telling you which kind of problem it is. Fix the cause before you wrap anything in IFERROR: hiding a #N/A also hides the next real mistake. Once the cause is fixed, IFERROR is fine for showing a clean message for values that are genuinely missing.
- #N/A: a lookup did not find the value. Usually a typo, a trailing space, or a number looking for text. Fix #N/A.
- #VALUE!: the wrong type of value, such as real text in arithmetic (="abc"+1), or ranges of different sizes in SUMIFS. Fix #VALUE!.
- #REF!: the formula points at cells that were deleted, often after deleting a column or pasting over a range. Fix #REF!.
- #NAME?: Excel does not recognize a name. A misspelled function, text without quotes, or a newer function (such as XLOOKUP) opened in an older Excel. Fix #NAME?.
- #DIV/0!: division by zero or by an empty cell. Fix #DIV/0! and divide in Excel.
- #SPILL!: a dynamic array formula needs empty cells to spill into and something is in the way, or it is inside an Excel table. Fix #SPILL!.
- #CALC!: a calculation error in a dynamic array formula, most often FILTER returning nothing without an if_empty argument. Fix #CALC!.
- #NUM!: a number Excel cannot work with, such as a result too large or a function that cannot find an answer. Fix #NUM!.
- #NULL!: a space between two ranges that do not overlap, usually a missing comma or colon. Fix #NULL!.
- #####: not an error at all. The column is too narrow, or a date or time is negative. Excel showing hashtags. To total a column that contains errors, see sum and ignore errors.
A specific function or feature is not working
If you already know which function is misbehaving, go straight to its guide. Each one tests the causes on sample data, in the order people usually hit them.
- Lookups: VLOOKUP not working, XLOOKUP not working, INDEX MATCH not working.
- Conditional sums and counts: SUMIF returning 0, SUMIFS not working, COUNTIF not working, COUNTIFS not working.
- Logic and dates: IF formula not working, DATEDIF not working, dates not sorting, percentage format wrong.
- Formatting rules and lists: conditional formatting formula not working, drop-down list not working.
- Data tools: pivot table not working, sort not working, filter not working, chart not updating, Flash Fill not working, find and replace not working, copy and paste not working.
- Not a formula at all: arrow keys not working, keyboard shortcuts not working, Tab key not working, Enter key moves the wrong way, undo not working, cannot insert a row, unhide rows not working, wrap text not working, hyperlink not working, spell check not working, print area not working, print titles not working, Excel opens to a blank grey screen.
The 60-second check order
When you do not know where to start, go through these in order. The first four take seconds and catch most problems.
- Click the cell and read the formula bar. If the cell shows the formula, fix the Text format or the leading space first.
- Check Formulas > Calculation Options. If it says Manual, choose Automatic.
- Compare
=COUNT(range)with the number of filled cells. A lower count means numbers stored as text. - Check one criteria cell with
=LEN(cell). More characters than you can see means hidden spaces. - Press F2 on the formula: Excel colors each reference and outlines its range. Check the ranges line up and did not slide when copied.
- Use Formulas > Evaluate Formula to step through a long formula and see which part goes wrong.
Common mistakes
Wrapping the formula in IFERROR to make the error go away
The error disappears but the wrong answer stays. Find the cause first with the list above, then use IFERROR or IFNA only for values that are genuinely missing.
Changing the cell format to Number and expecting the text to convert
Changing the format does not change a value that is already text. Re-enter it (F2, Enter), use Data > Text to Columns > Finish, or Convert to Number from the green triangle.
Retyping the formula when the data is the problem
If the same formula works on other rows, the formula is fine. Check the inputs on the failing row for text numbers, spaces and blanks.
Assuming =A1+A2 and =SUM(A1:A2) behave the same
The + operator converts a text number such as "640" to a number, while SUM skips text inside a range. A sheet can add up correctly with + and come out short with SUM for that reason.
FAQ
Why is my Excel formula showing as text and not calculating?
The cell was formatted as Text before the formula was typed, there is a space or apostrophe before the =, or Show Formulas is turned on. Set the cell to General, then press F2 and Enter to re-enter the formula.
Why does my Excel formula return 0?
Usually the numbers it adds are stored as text, the criteria match nothing because of a hidden space, or a lookup found an empty cell. Compare COUNT with the number of filled cells to test for text numbers. All seven causes, with a check for each, are in Excel formula returns 0.
Why is my Excel formula not updating automatically?
Calculation is set to Manual. Go to Formulas > Calculation Options and choose Automatic, or press F9 to recalculate once. A workbook saved in Manual mode can switch the setting for every workbook you have open.
Why does my formula work in one cell but not when I drag it down?
A reference moved when it should have stayed fixed, so lower rows point at empty cells. Add $ signs to the cell that must not move (for example $F$1), or check that calculation is not set to Manual.
Why does my formula work on my computer but show #NAME? on someone else's?
They are probably on an older version of Excel that does not have the function you used, such as XLOOKUP, FILTER or UNIQUE (Excel 365 and Excel 2021 or later). Use an older equivalent such as INDEX and MATCH, or ask them to open it in Excel for the web.
Functions used
SUMAdds numbersCOUNTCounts numbersCOUNTIFCounts cells that matchLENLength of textTRIMRemoves extra spacesSUMPRODUCTMultiply then addISTEXTIs it text?IFERRORFallback when a formula errors
Related guides
- Excel showing the formula instead of the result (formula shows as text)
- Excel formula not calculating or updating: how to fix it
- Excel formula returns 0 instead of the value: 7 causes and fixes
- How to convert numbers stored as text in Excel
- VLOOKUP not working: 10 causes and fixes
- COUNTIFS not working: why it returns 0 or #VALUE!
- INDEX MATCH not working: causes and fixes
Updated