Google Sheets: Array result was not expanded because it would overwrite data
Last verified
#REF! Array result was not expanded because it would overwrite data in B5.What it means
Every IMPORT function returns an array that spills down and to the right from the formula cell. If any cell in the area the result needs is not empty, Sheets refuses to overwrite it and shows #REF! with the address of the first blocking cell.
The import itself worked. Sheets has the data and knows how big it is; it simply has nowhere to put it. This is why the error often appears suddenly when the remote table grows by a row or a column.
The blocking cell is not always visibly filled. A single space, an empty string written by a script, or a formula that returns "" all count as data.
What it looks like
A1: =IMPORTHTML("https://example.com/stats", "table", 1)
B5: x
#REF!
Array result was not expanded because it would overwrite data in B5.Why it happens
- Something was typed into the area the import spills into.
- The source table grew, so the result now needs more rows or columns than before.
- A cell that looks empty contains a space, an empty string, or a formula returning "".
- Two import formulas are placed so close together that their outputs overlap.
- An automation appends rows into the same area the formula spills into.
How to fix it
- Go to the cell named in the message and press Delete; then check the next cell if the address changes.
- Select the whole spill area below and to the right of the formula and clear it if it should be empty.
- Move the formula to its own tab, or leave generous empty space for growth.
- Bound the result to a fixed size with ARRAY_CONSTRAIN(IMPORTHTML(...), 100, 6) if you only need part of it.
- If a tool appends rows into the sheet, give the import a separate tab so the two never collide.
How to stop it recurring
Give each import its own tab or a clearly reserved block, and do manual work in other tabs that reference the imported data. If other automations write to the same file, keep their targets on separate tabs too. When the source can grow, leave several empty rows and columns around the spill area.
Official documentation: support.google.com/docs/answer/3093339. See all Google Sheets error messages or the Google Sheets limits and quotas.
Related errors
Definitions
Quick answers
What does "Array result was not expanded because it would overwrite data" mean in Google Sheets?
Every IMPORT function returns an array that spills down and to the right from the formula cell. If any cell in the area the result needs is not empty, Sheets refuses to overwrite it and shows #REF! with the address of the first blocking cell. The import itself worked. Sheets has the data and knows how big it is; it simply has nowhere to put it.
How do I fix "Array result was not expanded because it would overwrite data"?
1. Go to the cell named in the message and press Delete; then check the next cell if the address changes. 2. Select the whole spill area below and to the right of the formula and clear it if it should be empty. 3. Move the formula to its own tab, or leave generous empty space for growth. 4. Bound the result to a fixed size with ARRAY_CONSTRAIN(IMPORTHTML(...), 100, 6) if you only need part of it. 5. If a tool appends rows into the sheet, give the import a separate tab so the two never collide.
When an IMPORT formula cannot read the page
IMPORTXML and IMPORTHTML only see the HTML the server sends. An Autonoly extraction step reads the page in a real browser and writes rows to Google Sheets. Sites that block automated visitors can still block it.
Also see Google Sheets integration.