Spill and Dynamic Range Troubleshooting
Fix #SPILL! errors and unstable ranges.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Troubleshoot dynamic array and range problems. App and version: [SPREADSHEET_APP_AND_VERSION]. The formula and the error shown: [FORMULA_AND_ERROR]. Layout around the formula (what is in the cells below and to the right): [SURROUNDING_LAYOUT]. What I want: [GOAL]. Take me through this diagnosis: 1. Identify the error: spill blocked by content (cells in the way, including invisible text or spaces), merged cells in the spill range, a table in the way (tables cannot hold spilled arrays), the spill range too large (beyond the sheet limit), unknown size, or an array in a place where it is not allowed. 2. Steps to find the blocking cell: select the error, view the highlighted spill range, clear obstacles, unmerge, and check with a search for non-empty cells. 3. Alternatives: move the formula, wrap results with INDEX/TAKE to limit the size, convert to a single value, or use helper columns. 4. Referencing spilled results with the spill range operator and why a fixed range reference can break when the size changes. 5. Make ranges dynamic for charts, validation lists, and summaries (named formulas or table references). 6. Compatibility: how the file behaves in older versions (legacy array formula display) and when sharing. Return the likely cause in my case, the corrected approach, and a prevention checklist (reserve space for spills, avoid putting data directly below spill formulas). Ask for details if the layout is unclear.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.