If your Excel formula isn’t working — whether it’s showing as plain text, returning the wrong result, or throwing an error — you’re not alone. This is one of the most common issues people run into with spreadsheets, and in most cases it comes down to one of six causes. This guide walks through each one with a clear fix.
1. Cells Formatted as Text (Formula Shows as Text Instead of Calculating)
This is the most confusing one because it looks like Excel is ignoring your formula entirely. You type =A1+B1 and instead of a number, you just see =A1+B1 sitting in the cell like it’s a label.
The cause is almost always that the cell is formatted as Text before you typed the formula. When a cell is set to Text format, Excel treats everything you type as a string — including formulas.
How to fix it
- Select the affected cell or cells.
- Go to the Home tab and find the number format dropdown (it probably says “Text”).
- Change it to General.
- Now click into the cell, press F2 to enter edit mode, then press Enter.
Just changing the format isn’t always enough — Excel needs you to re-confirm the formula before it recalculates. If you have many affected cells, you can use Find & Replace (Ctrl+H) to replace = with =, which forces all formulas to re-evaluate at once.
2. Manual Calculation Mode (Formulas Don’t Update)
You update a value in one cell and nothing else changes. Your formulas are there, they look correct, but the results are frozen. This means Excel has been switched to Manual calculation mode.
This can happen accidentally, especially if you opened a file that had it enabled — Excel sometimes applies the setting from the last workbook you had open.
How to fix it
To force an immediate recalculation, press F9. To switch back to automatic permanently:
- Go to File > Options > Formulas.
- Under Calculation options, select Automatic.
- Click OK.
3. Circular Reference Error
A circular reference means a formula is referring back to its own cell, either directly or through a chain of other cells. Excel can’t resolve this and will either show a warning or return 0.
You’ll usually see a message at the bottom of the screen saying “Circular References” with a cell address.
How to fix it
- Go to Formulas > Error Checking > Circular References.
- Excel will show you which cell is causing the problem.
- Click on that cell and check whether its formula refers to itself, directly or indirectly.
- Rewrite the formula so it references different cells.
4. Wrong Absolute or Relative References (Forgetting Dollar Signs)
When you copy a formula down a column, Excel adjusts the cell references automatically. That’s usually helpful, but sometimes you want a reference to stay fixed — for example, a tax rate stored in cell B1 that every formula should refer to.
If you forget to lock that reference with a dollar sign, copying the formula will cause it to drift, and the results will be wrong in ways that aren’t immediately obvious.
How to fix it
Use the $ symbol to lock a reference:
$B$1— locks both column and row (fully absolute)B$1— locks the row only$B1— locks the column only
When editing a formula, click on the cell reference and press F4 to cycle through absolute and relative options quickly.
5. Spaces or Invisible Characters in Referenced Cells
If a cell looks empty but your formula is behaving as though it contains something — or if a lookup formula can’t find a match that you can clearly see — there are probably invisible characters or extra spaces in the data.
This is very common with data imported from other systems or copy-pasted from websites.
How to fix it
Use the TRIM function to remove leading, trailing, and extra internal spaces:
=TRIM(A1)For non-printing characters (common in imported data), use CLEAN combined with TRIM:
=TRIM(CLEAN(A1))Apply these in a helper column, then copy and paste the results as values over the original data.
6. Formula Returns the Wrong Value
Sometimes the formula runs fine — no error, no warning — but the number it returns is wrong. This usually means the range or arguments are off.
Common culprits include: selecting one row too many or too few in a SUM range, using the wrong column in a VLOOKUP, or mixing up argument order in a function.
How to fix it
- Click the cell and look at the formula bar. Check that the range highlighted in blue actually covers what you intended.
- Use Formulas > Evaluate Formula to step through the calculation and see where it goes wrong.
- For VLOOKUP: confirm the col_index_num matches the column you want, and check whether the last argument should be FALSE (exact match) not TRUE.
Common Excel Error Codes Explained
When a formula does return an error, the code tells you what went wrong:
#N/A
“Not available” — a lookup formula (VLOOKUP, MATCH, XLOOKUP) couldn’t find the value you searched for. Check the spelling, check for extra spaces, and confirm the lookup value exists in the range.
#VALUE!
You’ve tried to do maths on a cell that contains text. For example, adding a cell that contains “N/A” as text rather than a number. Find the non-numeric cell and correct it.
#REF!
A cell reference is broken — usually because you deleted a row or column that the formula was pointing to. Check the formula and update the references.
#DIV/0!
You’re dividing by zero, or by an empty cell. Wrap the formula in an IFERROR or IF check: =IF(B1=0, "", A1/B1)
#NAME?
Excel doesn’t recognise the function name. This usually means a typo — for example, typing =SUMIF correctly but accidentally writing =SUMFI. It can also happen if you type a named range that doesn’t exist.
Quick Checklist When a Formula Isn’t Working
- Is the cell format set to Text? Change to General and re-enter.
- Is calculation set to Manual? Press F9 or switch to Automatic.
- Is there a circular reference warning at the bottom of the screen?
- Are your $ signs in the right place for copied formulas?
- Does the data have extra spaces or hidden characters? Use TRIM/CLEAN.
- Is the range or argument in your formula pointing at the right cells?
Most Excel formula problems fall into one of these categories. Start with the cell format check — it causes more confusion than anything else — and work through the list from there. The Evaluate Formula tool (under the Formulas tab) is particularly useful when you can’t see what’s going wrong, as it shows you each step of the calculation.
