Deleted lookup column
VLOOKUP pointed at column 5 but column D was deleted, shifting the index and returning #REF!.
What does #REF! mean in Google Sheets?
#REF! means a formula references a cell or range that no longer exists. Common causes include deleted rows or columns, removed sheets, broken IMPORTRANGE, or cut-paste that orphaned an address. The error spreads to every dependent formula until you repair the link.
When to use it
Learn #REF! when cleaning imports, refactoring tabs, or maintaining lookup models so you fix broken references before anyone opens the dashboard.
When to skip it
Do not hide every #REF! with IFERROR without finding the missing range. Masking hides structural problems in the workbook.
How it works
-
1
Click the cell and read the formula bar to see which reference is invalid.
-
2
Use Trace precedents to find deleted ranges or broken sheet names.
-
3
Restore deleted rows from version history if the delete was accidental.
-
4
Reconnect IMPORTRANGE with the correct spreadsheet id and range after renames.
-
5
Replace fragile OFFSET patterns that break when rows insert above the anchor.
-
6
Use named ranges and bounded tables so deletes are less likely to shred addresses.
Examples in Google Sheets
Removed Settings tab
='Settings'!B2 becomes #REF! when the Settings sheet is deleted from the file.
Broken IMPORTRANGE
Source file moved or permissions revoked; the receiving cell shows #REF! until access is restored.
Build this without starting from a blank cell
Use a Better Sheets tool for #REF! error, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Wrapping every formula in IFERROR without fixing the missing range.
- Deleting columns on a shared tab without checking dependent formulas.
- Using whole-column references that break differently than small ranges when rows delete.
- Copying formulas between files without updating sheet name prefixes.
- Assuming undo will fix references after someone else already saved the file.
Frequently asked questions
- Why does deleting one row cause #REF! everywhere?
- Formulas that referenced that row now point at nothing. Update the range or restore the row from version history.
- Can INDIRECT cause #REF!?
- Yes when INDIRECT builds an invalid address string. Fix the text or the target range.
- How do I find all #REF! cells?
- Use Edit > Find for #REF! or add a helper column that flags ERROR.TYPE on key formulas.
- Do charts show #REF!?
- Charts break when the source range is deleted. Re-select the data range in the chart editor.
- Why does IMPORTRANGE show #REF!?
- Confirm the URL key, tab name, range, and that your account can view the source file.
- Can array formulas return #REF!?
- Yes when the spill source is deleted or blocked. Clear obstructing cells or fix the reference.
- How do I prevent #REF! on team sheets?
- Use named ranges, protect structure, and document which columns must not be deleted.
- Is #REF! the same as #N/A?
- No. #REF! is a broken address. #N/A means the formula ran but could not find a match.
Related Tutorials
Watch how #REF! error works
How do I reference a different spreadsheet in Apps Script?
Most Common Errors and How To Handle Them in Google Sheets
Seek Errors When Coding Apps Script
Created a GPT function in Sheets
Embed a Number in a Website from a Google Sheet
Every Locale Listed in Google Sheets and a Tool to Save Your Sanity
Related blog posts
Guides that explain #REF! error in more depth.
Most Common Errors and How To Handle Them in Google Sheets
Most Common Errors and How To Handle Them in Google Sheets #N/A error #REF! error #NAME? error #DIV/0! Error #VALUE! Error #NUM! error #NULL! error
Read post →Google Sheets Locales. What are they?
Hello, Better Sheet members and non-Better Sheet members! This tutorial is about every locale listed in Google Sheets. If you live in a country that is not well represented with blog posts or someone makes a blog post about a Google Sheets issue, they create a Google Sheet formula and then you copy and paste it into your sheet and it has the wrong delimiter. How do you resolve that? I'm going to show you how you can fix that and how you can know if there's a problem. Locales. What are the...
Read post →How to Gain Confidence in Google Sheets
Building confidence in Google Sheets, and yourself is essential. It's benefits include: streamlining your workflow, ensuring data accuracy, and maximizing your productivity. If you’ve ever felt overwhelmed or unsure about using Google Sheets, this guide is here to help. Let’s explore some practical tips and strategies to boost your confidence and become a Google Sheets pro. I'll show you where you can find help inside and outside of Google Sheets. 👍Better Sheets Members can email me anytim...
Read post →Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Open range reference
An open range reference points at an entire row, column, or unbounded slice such as B:B or 2:2 instead of a fixed rectangle like B2:B500. Formulas with open ranges include every cell in that line, which is convenient for growing tables but expensive and risky when stray values live far below your data.
Read guide →Absolute reference ($)
Absolute references lock row, column, or both with dollar signs so a formula keeps pointing at the same cell when you copy it. $A$1 locks both axes. A$1 locks the row. $A1 locks the column. Mixed locks are how tax rates stay in one settings cell while a formula fills down a thousand rows.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →IMPORTRANGE
IMPORTRANGE loads values from a range in another Google spreadsheet into your current file. You pass the spreadsheet URL or key plus a range string with an optional sheet name. After you grant access once, the link stays live and updates when the source changes.
Read guide →Done reading about #REF! error?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.