Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
When a dynamic-array formula needs more cells than the grid can provide, Excel can show `#SPILL!`. This exercise uses a fictional ticket list with one deliberate obstruction. The result to check is four status values in D2:D5 and the original note preserved in F4. It demonstrates the spill range isn't blank case; diagnose a different error detail separately.
Download the practice workbook and independent answer check. Use an Excel version that supports the UNIQUE function, such as current Microsoft 365, Excel 2021 or Excel 2024.
Locate and preserve the obstruction
- Open the workbook's Statuses sheet. The source statuses are in B2:B7. Cell D2 is intentionally empty, while D4 contains
KEEP: supervisor note. - Select D2 and enter:
=UNIQUE(B2:B7)One formula should return a four-cell vertical list in D2:D5. Because D4 already holds a note, this practice is designed to produce `#SPILL!` in D2. Do not fill the formula down.
- Select the error cell. Excel's #SPILL! help says a dashed outline indicates the intended range; the error alert may offer Select Obstructing Cells. Inspect that result or the outlined range manually. Confirm the obstruction is the known D4 note, not another formula or unrelated data.
- Move, do not erase, the known note. Cut D4 and paste into F4. Verify F4 still says
KEEP: supervisor note. In a real workbook, first check whether moving an occupied cell would affect dependent work. - Recheck D2:D5 against the answer check:
Intake,Review,Approved,On hold, in that order.ReviewandIntakeoccur more than once in the source but appear once in the result. If the error remains, inspect the actual message and all cells in the intended range.
Practice data and independent check
The source sequence is Intake, Review, Approved, Review, On hold, Intake. Its distinct first appearances are the four values above. This answer is derived from the input labels, separately from Excel. The downloaded workbook contains the source and the blocker, not a pre-entered formula. In a private Excel for the web demo copy, the formula produced a spill error; after the note was preserved in F4 and D4 cleared, the four expected statuses appeared. Check the result again in your own Excel session.
FAQ
Should I simply delete every cell inside the dashed outline? No. Identify the obstruction and its purpose first. This exercise moves its known fictional note to F4 so nothing is lost.
What if Select Obstructing Cells is absent? Use the dashed range and the error detail to inspect manually. Different Excel versions and web/desktop surfaces may expose controls differently.
What if the range is blank? Microsoft documents other causes, including merged cells, a dynamic array inside an Excel Table, and a result extending beyond the worksheet edge. Fix the cause Excel reports; moving D4 only solves this supplied exercise.
Can I type into D3 after the repair? The spill result belongs to the formula anchored in D2. Change the source values or the D2 formula instead of overwriting a spilled result cell.
Source and visual note
Vendor guidance checked 11 October 2026: Microsoft #SPILL! troubleshooting and UNIQUE function. The workbook's input cells and independent expected list were checked locally, and the blocked range and recalculation were reproduced in a private Excel for the web demo copy. The exact Cut → Paste motion was not separately tested. The video uses illustrative English diagrams, not actual app captures.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.