#SPILL! in Protected Worksheets: Range lock issues

A #SPILL! error occurs on a protected worksheet when a dynamic array formula attempts to spill results into cells configured with the “Locked” property. In Excel, cells are locked by default, but that lock only takes effect once worksheet protection is turned on. Even if the cell containing the original dynamic formula is unlocked, if any cell across its downstream spill corridor remains locked, Excel cannot write data into it. To prevent unauthorized overwrites, the calculation engine stops and displays #SPILL!.

Quick Fix

Temporarily unprotect the sheet via Review > Unprotect Sheet. Select the entire area where the array needs to spill, press Ctrl + 1, go to the Protection tab, uncheck Locked, and click OK. Once the entire spill range is unlocked, re-enable protection via Review > Protect Sheet.

---

How Sheet Protection Intersects with Dynamic Spilling

Every cell on a standard Excel worksheet has an underlying format state called Locked:

[ Cell Properties: Default State ]
  ├── Cell Value: Empty
  └── Protection State: LOCKED (Active only when sheet protection is ON)

When you unlock a single cell (e.g., C2) so a user can enter a dynamic formula like =SORT(UNIQUE(A2:A20)), the formula attempts to write values down through C3:C15.

If C3:C15 have not been manually unlocked:

1. Excel evaluates the formula in unlocked cell C2.
2. It detects that writing the output to C3:C15 requires editing locked cells.
3. Because active worksheet protection rules prohibit changes to locked cells, Excel refuses to write partial results.
4. The formula aborts and cell C2 displays #SPILL!, a core dynamic array conflict detailed in Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide.

---

Step-by-Step Diagnostic Routine

If a dynamic array formula works perfectly on an unprotected sheet but immediately breaks with #SPILL! when protection is enabled, follow this diagnostic process.

Step 1: Verify the Error Flag

Select the formula cell and check the yellow warning flag:

• Hover over the error icon.
• If it states “Spill range is in a locked cell”, protection boundaries are the direct cause.

Step 2: Unprotect and Trace the Spill Area

1. Go to the Ribbon and select Review > Unprotect Sheet (enter password if prompted).
2. The #SPILL! error will immediately disappear, and the array will populate with data.
3. Observe the thin blue boundary line surrounding the active results. This confirms the exact cells the formula needs to occupy.

Step 3: Unlock the Entire Spill Corridor

To ensure the formula continues working when protection is restored:

1. Highlight the starting formula cell plus an adequate buffer of empty cells below and to the right to accommodate future data growth.
2. Press Ctrl + 1 to open the Format Cells dialog.
3. Switch to the Protection tab.
4. Uncheck the box labeled Locked.
5. Click OK.
6. Return to Review > Protect Sheet, configure your permissions, and click OK.

---

The Dynamic Expansion Trap

A frequent maintenance problem with dynamic arrays on protected sheets is the variable-size crash:

Week 1: List contains 5 items.
        Unlocked cells: C2:C6.
        Formula works properly.

Week 2: New products added; list expands to 8 items.
        Spill attempts to write into C7:C9.
        Cells C7:C9 are LOCKED.

Result: Formula breaks with #SPILL! across all rows.

When a formula expands beyond the pre-unlocked zone, the whole formula fails.

Prevention Strategies:

• Unlock a Generous Buffer: If your list currently returns 10 items but could grow, unlock 50 to 100 rows in that column.
• Keep Arrays on Dedicated Calculation Sheets: Keep your raw dynamic arrays (FILTER, UNIQUE, SORT) on a separate, hidden, or unprotected calculation sheet. Then, reference individual outputs on your protected presentation sheet using standard lookups.
• Use VBA Automation for Dynamic Unlocking: For enterprise templates where unlocked buffers create security risks, use a macro to update protection programmatically when source data changes:

Sub RefreshDynamicSpill()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Report")

    ws.Unprotect Password:="YourPassword"
    ' Allow Excel calculation engine to resize arrays
    ws.Calculate
    ' Unlock the exact newly spilled range
    ws.Range("C2").SpillParent.SpillingToRange.Locked = False
    ws.Protect Password:="YourPassword"
End Sub

For broader guidance on preserving template formulas and resolving write-permission breaks during operational data entry, see #REF! in Protected Sheets: Missing Permissions for formula updates and Protecting Formulas from #REF! errors during user input.