Excel throws a #SPILL! error in a protected worksheet when a dynamic array formula attempts to expand into cells that have their Locked property enabled. Even if the anchor cell containing the formula is unlocked, Excel’s sheet protection engine prevents the dynamic array from writing results into any locked downstream cells. Unprotecting the worksheet, unlocking the entire anticipated spill range, and re-enabling protection resolves the error instantly.
Fast-Fix: The 45-Second Solution
Excel throws a #SPILL! error on protected sheets when locked cells block the array’s output path. Fix this by navigating to Review > Unprotect Sheet (enter the password if required). Select the anchor cell and the full target spill area, press Ctrl + 1, switch to the Protection tab, uncheck Locked, and click OK. Finally, go to Review > Protect Sheet and click OK to restore protection with the spill path unlocked.
Quick Risk Snapshot
- Severity Tier: Moderate (Blocks dynamic reporting models and input forms on locked worksheets).
- Is it safe to ignore?: No. Dependent calculations, summary cards, and charts referencing the array will display error values or empty results.
- Most common cause: Entering a dynamic array formula in an unlocked cell while leaving adjacent downstream cells in their default Locked state.
- Rare/Serious cause: Source data expanding beyond a pre-unlocked buffer range into locked lower rows on a password-protected template.
Low Risk vs. High Risk
- If the error occurs on an internal form with known password access: It is Low Risk. Unprotecting the sheet, unlocking extra buffer rows, and re-protecting takes under a minute.
- If the error occurs on a distributed, password-locked template feeding enterprise reporting pipelines: It is High Risk. Users without unprotect privileges cannot update or view array results, forcing pipeline delays or administrative intervention to update template permissions.
The Mechanics of the Break
In Excel, every cell on a worksheet has a Locked property enabled by default. However, cell locking has no physical effect until you actively turn on Sheet Protection (Review > Protect Sheet).
Dynamic array functions (FILTER, UNIQUE, SORT, SEQUENCE) evaluate in an anchor cell and then write their calculated outputs across a continuous range of neighboring cells. When you place an array formula on a protected sheet, the calculation engine evaluates the write permissions for every single cell within the required spill range:
- Anchor Cell Check: If the anchor cell is unlocked, Excel allows you to enter or edit the formula.
- Spill Path Check: The dynamic array engine determines how many rows and columns are needed to display the output array.
- Protection Conflict: The engine checks the protection status of every target cell in the path. If even a single cell in that path has its Locked box checked, the worksheet protection engine denies write access to the array engine.
Because dynamic array outputs operate as an “all-or-nothing” block, Excel cannot partially populate the unlocked cells while skipping locked ones. The engine aborts the write operation entirely and returns #SPILL!.
Think of a dynamic array formula as an automated telescoping conveyor belt mounted in an industrial facility. When activated, the belt extends outward across a sequence of floor panels to deliver cargo. The anchor cell is the control station. If even one floor panel along the expansion track has a physical padlock secured over its mounting latch, the conveyor belt’s safety sensors detect the obstruction and halt extension completely (#SPILL!) to prevent mechanical damage.
| Cell Position | “Locked” Formatting Property | Sheet Protection Status | Engine Permission | Result |
|---|---|---|---|---|
Anchor Cell (A2) | Unchecked (Unlocked) | Enabled | Write Allowed | Formula accepts input |
Spill Cell (A3) | Unchecked (Unlocked) | Enabled | Write Allowed | Cell accepts array data |
Spill Cell (A4) | Checked (Locked – Default) | Enabled | Write Denied | Triggers #SPILL! error |
All Spill Cells (A2:A10) | Unchecked (Unlocked) | Enabled | Write Allowed | Full array spills cleanly |
Probability Breakdown
- Likely (60%): Unlocking only the top-left anchor cell while leaving downstream spill cells locked before enabling sheet protection.
- Possible (30%): Source data growth causing an existing array to expand past a small, pre-unlocked cell buffer into locked rows beneath it.
- Rare (10%): Worksheet protection settings that allow user row insertion, but assign default locked formatting to newly created rows.
What Escalates the Risk
Risk escalates when dynamic arrays operate inside locked templates that receive dynamic data updates from external databases or Power Query feeds. If an automated refresh increases the record count from 20 rows to 50 rows, an array that previously fit within a small unlocked window will hit locked cells at Row 21, throwing a #SPILL! error across the entire sheet.
Additionally, running automated VBA macros that attempt to write array formulas to protected sheets without unprotecting the worksheet first will trigger macro execution halts. See Runtime Error 1004: “The cell or chart you’re trying to change is on a protected sheet.”.
Consequence Timeline
- 24 Hours: Interactive input forms and dynamic reports display
#SPILL!tags, preventing users from viewing key metrics. - 1 Week: Users without administrative passwords attempt manual workarounds, creating unvalidated side-schedules outside the master template.
- 1 Month: Audit gaps compound across distributed operational templates due to broken data links and locked calculation paths.
Common Confusion Fix
Distinguish protection-based #SPILL! errors from related formula breaks:
- Protected #SPILL! vs. Ghost Character #SPILL!: A protected
#SPILL!error is caused by cell security attributes, even if the cells appear entirely blank. A ghost character#SPILL!error occurs on an unprotected sheet because invisible spaces or non-empty values occupy the spill path. See #SPILL! Error: Non-Empty cells in the spill range (The “Ghost” character). - Protected #SPILL! vs. Protected #REF!:
#SPILL!occurs when an array formula is allowed in an unlocked anchor cell but blocked from expanding by locked spill cells.#REF!occurs on a protected sheet when a macro or formula attempts to delete or alter locked reference ranges directly. See #REF! in Protected Sheets: Missing Permissions for formula updates. - Protected #SPILL! vs. Table #SPILL!: Dynamic arrays cannot live inside formal Excel Tables under any protection status. See #SPILL! in Excel Tables: Why Dynamic Arrays can’t live in Tables.
What To Do Right Now
1. Unlock the Full Potential Spill Range
- Click Review > Unprotect Sheet.
- Select the anchor cell and highlight a generous buffer range below and to the right (e.g., select 100 extra rows if data is expected to grow).
- Press Ctrl + 1 to open the Format Cells dialog.
- Click the Protection tab.
- Uncheck Locked and click OK.
- Re-apply protection via Review > Protect Sheet.
2. Restrict Array Size using FILTER or TAKE
If you cannot unlock additional rows due to security rules, restrict the max height of the dynamic array so it fits inside the unlocked window:
=TAKE(FILTER(A2:A100, A2:A100<>""), 15)
This forces the array output to stop at 15 rows, preventing it from overflowing into locked cells below.
3. Use Implicit Intersection (@) for Single-Cell Returns
If you only need a single value on each row instead of a multi-cell array spill, apply the implicit intersection operator (@):
=@FILTER(DataRange, CriteriaRange=B2)
See #SPILL! vs. The Implicit Intersection Operator (@).
Hard-Stop Triggers
Stop editing and review template security if:
- The sheet protection password is lost, preventing modification of locked cell attributes.
- Unlocking cells exposes sensitive financial formulas or underlying data logic to unauthorized edits.
- Dynamic array spill paths overlap protected total rows or locked signature blocks at the bottom of a form.
Professional Audit Path
When auditing a protected workbook with #SPILL! errors:
- Verify Sheet Security: Look at the Review tab on the ribbon. If the button displays Unprotect Sheet, active worksheet protection is enforced.
- Identify Locked Cells: Select the anchor cell, unprotect the sheet, highlight the target spill path, press Ctrl + 1, and verify if any cells within the selection have the Locked box checked.
- Test Data Growth Limits: Temporarily insert sample records into the source dataset to confirm that the unlocked buffer range handles peak volume without triggering new
#SPILL!breaks.
Complexity & Repair Range
- Minor (Range Unlock & Re-protect): 2 minutes. Unprotecting the sheet, unchecking the Locked property for the spill path, and re-enabling protection.
- Moderate (Buffer & Boundary Redesign): 15 minutes. Redesigning sheet layout to place total lines or locked input blocks away from dynamic array expansion paths.
- Major (VBA & Template Permissions Overhaul): 45 minutes. Updating macro pipelines to programmatically unprotect sheets (
Worksheet.Unprotect Password:="..."), write dynamic arrays, and re-protect sheets during data ingestion.
Symptom Escalators
If dynamic array or protection errors persist across your workbook, reference these related troubleshooting guides:
- If non-empty cells or ghost formatting block an array spill on unprotected sheets, see #SPILL! Error: Non-Empty cells in the spill range (The “Ghost” character).
- If merged cells obstruct dynamic array expansion, see #SPILL! Error: The “Merged Cell” Blockage.
- If dynamic array formulas are placed inside formal Excel Tables, see #SPILL! in Excel Tables: Why Dynamic Arrays can’t live in Tables.
- If whole-column references cause massive memory spill errors, see #SPILL! with Indefinite References (e.g., A:A).
- If frozen pane viewports obscure hidden blocking cells, see #SPILL! in Frozen Panes: Visual rendering glitches.
- If macros trigger sheet protection errors, see Runtime Error 1004: “The cell or chart you’re trying to change is on a protected sheet.”.
- If dynamic array functions return empty array errors, see #CALC! Error: Empty Array results in FILTER.
Final Calculation
A #SPILL! error in a protected worksheet is a security lock conflict: the anchor cell allows formula entry, but one or more target cells in the spill path have their Locked property enabled. Unprotecting the sheet, highlighting the full anticipated output path (including growth buffer rows), unchecking the Locked format attribute, and re-applying sheet protection completely eliminates the error while maintaining workbook security.