Excel #REF! error
The formula points to a cell that no longer exists, usually because you deleted a row, column or sheet it used.
Why it appears
- You deleted rows or columns the formula referred to.
- You copied the formula so far up or left that the reference fell off the sheet.
- VLOOKUP asks for a column beyond the ones in the range.
How to fix it
- Undo with Ctrl+Z right after deleting and fix the formula first.
- Use anchored references ($A$1) or whole ranges so they do not shift.
- Check that VLOOKUP’s column number fits in the range.
Gives the error
=VLOOKUP(E2,A2:B6,3,FALSE)
Fixed
=VLOOKUP(E2,A2:C6,3,FALSE)
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Absolute references ($)
When you copy a formula down, B2 becomes B3, B4… If a cell must stay put, like a tax rate, anchor it with $: $F$1 does not change when copied. In Excel, F4 adds the dollar signs.
=B2*(1+$F$1)In C2, calculate the price with tax using the rate in F1. The formula will be copied down to C6.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Price with tax | Tax | 16% | ||
| 2 | Headphones | 650 | |||||
| 3 | Keyboard | 899 | |||||
| 4 | Mouse | 349 | |||||
| 5 | Monitor | 3,299 | |||||
| 6 | USB cable | 129 | |||||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #REF! mean in Excel?
- The formula points to a cell that no longer exists, usually because you deleted a row, column or sheet it used.
- How do I get rid of the #REF! error?
- Undo with Ctrl+Z right after deleting and fix the formula first. Use anchored references ($A$1) or whole ranges so they do not shift. Check that VLOOKUP’s column number fits in the range.
- What is #REF! called in Spanish Excel?
- Spanish Excel shows it as #¡REF!.