FILTER blocked by note
A FILTER in D1 spills down but D3 has a manual note, causing #SPILL! until the note moves.
What does #SPILL! mean in Google Sheets?
#SPILL! means a dynamic array formula tried to fill multiple cells but something blocked the spill range. Another value, merged cells, or a table in the way stops the output. Clear the obstruction or move the formula to open space.
When to use it
Learn #SPILL! when using FILTER, UNIQUE, SORT, SEQUENCE, or single-cell formulas that spill down or across modern Google Sheets.
When to skip it
Do not fight spills by deleting neighboring data without checking whether those cells held formulas that other tabs reference.
How it works
-
1
Click the cell with #SPILL! and look for the dashed border showing intended spill area.
-
2
Delete or move content in any cell inside that dashed rectangle.
-
3
Unmerge cells in the spill path; merged cells block arrays.
-
4
Move the formula to a column with enough empty rows below.
-
5
Use ARRAY_CONSTRAIN if you only need a fixed number of output rows.
-
6
Avoid placing two spill formulas whose output ranges overlap.
Examples in Google Sheets
UNIQUE beside another list
Two UNIQUE formulas side by side overlap spill columns.
Merged header row
SEQUENCE spills into merged cells in row 1 and fails until unmerged.
Build this without starting from a blank cell
Use a Better Sheets tool for #SPILL! error, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Leaving total rows with SUM below a column where FILTER wants to spill.
- Putting two dynamic arrays in adjacent columns without counting spill width.
- Using whole-column references that spill millions of rows and hit grid limits.
- Assuming #SPILL! means the formula logic is wrong when it is only blocked space.
- Converting spills to CSE arrays unnecessarily instead of clearing blockers.
Frequently asked questions
- What is a spill range?
- The rectangle of cells a dynamic array formula fills starting from the formula cell.
- Can I reference a spill with A1#?
- Yes. The hash notation refers to the full spilled array from anchor A1.
- Why #SPILL! with nothing visible nearby?
- Check hidden columns, merged cells, or a spill extending off-screen to the right.
- Does ARRAYFORMULA always spill?
- Modern Sheets treats many functions as dynamic arrays. Legacy ARRAYFORMULA behavior differs slightly.
- How do I limit spill size?
- Use ARRAY_CONSTRAIN, TAKE, or FILTER with tighter conditions.
- Can protection cause #SPILL!?
- Yes if protected cells sit in the spill path and block writes.
- Is #SPILL! related to #REF!?
- Different problems. #SPILL! is blocked output space. #REF! is invalid reference.
- Can I turn off dynamic arrays?
- You work with spills in current Sheets. Design layout with open space for outputs.
Related glossary terms
FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →ARRAYFORMULA
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
Read guide →ARRAY_CONSTRAIN
ARRAY_CONSTRAIN wraps an array formula result and returns only the first num_rows and num_columns of that output. It prevents huge spills, fits charts to fixed ranges, and stabilizes ranges for IMPORTRANGE wrappers when you need predictable dimensions.
Read guide →Done reading about #SPILL! 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.