Skip to content

Est. 2011ยทMicrosoft Partner ID 7033487ยทDelivery under 3 minยทSupport 7 days a week

Your vault is empty.

License Error #SPILL!

Excel #SPILL! and #CALC! Errors: Dynamic Arrays Blocked or Unsupported

12 min read Updated October 5, 2026 Outlook & Office Applications

Fix it now

#SPILL! means the formula is right and Excel cannot write the answer, because something is in the way. #CALC! means the engine reached a result it cannot place in cells. The four data-type errors mean the service behind the cell did not answer.

  1. Click the cell showing #SPILL!. Excel draws a dashed border around the range it wanted to fill, and the warning icon names the specific reason.
  2. If it says the spill range is not blank, use the warning icon to select the obstructing cells, then clear them. A cell holding only a space still counts as occupied.
  3. If it says the size is indeterminate, look for a volatile function inside the formula. RAND, RANDARRAY and RANDBETWEEN resize between calculation passes and cannot spill.
  4. If it says table formula, move the formula out of the Excel Table. Spilled array formulas are not supported inside tables.
  5. If it says spill into merged cells, unmerge them or move the formula somewhere with no merges in its target range.
  6. For #CALC! from a FILTER that matched nothing, add the if_empty argument: =FILTER(C3:D5,D3:D5<100,0).

Read the sub-message before acting. Excel publishes seven distinct #SPILL! reasons and the fix for each is different; clearing cells will not help a formula whose array size is indeterminate.

If the anchor cell shows a value and the spilled range outlines cleanly, you are done. The next section explains how spilling works and what the data-type errors are reporting.

Why it happens

A dynamic array formula lives in one cell but returns a rectangle of results. Excel calculates the shape of that rectangle, checks the cells it needs, then writes. If any of those cells contains anything at all, the write is refused entirely and the anchor cell shows #SPILL!. There is no partial spill, because a half-written array would silently overwrite somebody’s data.

Excel does not leave you to guess which of those went wrong. It publishes seven reasons and shows the relevant one beside the error: the spill range is not blank; the size is indeterminate because the array is volatile and resizes between calculation passes; it extends beyond the worksheet’s edge; it is a table formula, which is not supported; it ran out of memory; it would spill into merged cells; or it is unrecognised, where Excel cannot reconcile the cause and the advice is to check the formula’s arguments.

#CALC! comes from the calculation engine rather than from the worksheet. Microsoft documents several distinct cases, and the ones an ordinary user meets are: a nested array, where Excel cannot calculate an array within an array; an array of ranges, because arrays can only contain numbers, strings, errors, Booleans or linked data types and range references are not supported; and an empty array, where Excel cannot return an empty set – which is what a FILTER that matched nothing produces unless you supply the if_empty argument.

The four remaining errors report on a connection rather than on a calculation. #FIELD! occurs when a formula references a field a linked data type does not have, or a cell that holds no data type at all. #BLOCKED! is returned when a required resource cannot be accessed – a missing licence, not being signed in, a region where the data type is unavailable, privacy settings, or a Trust Center block. #CONNECT! occurs when a formula cannot connect to a required resource. #BUSY! is returned when a formula is waiting for a required resource that is taking a long time to access, which includes external workbook links and images.

Something is sitting in the spill range

You have this one if The sub-message says the spill range is not blank, and the dashed outline crosses cells that contain data or that appear blank but are not.

  1. Use the warning icon and choose the option to select the obstructing cells, which takes you straight to them.
  2. Select the whole intended output range and press Delete, which removes values and formulas without disturbing column widths.
  3. Test for invisible content with =COUNTBLANK(range). Fewer blanks than cells means something is in there.

The array size cannot be determined

You have this one if The sub-message says indeterminate size, and the formula contains a volatile function.

  1. Take RAND, RANDARRAY or RANDBETWEEN out of the spilling formula. Microsoft names those three specifically as causing unpredictable sizing.
  2. Where random values are genuinely needed, generate them into a fixed range first and have the array formula read that range.
  3. Recalculate and confirm the array now resolves to a stable shape.

The formula is inside an Excel Table

You have this one if The sub-message says table formula, and the cell sits in a range with banded rows and a Table Design tab on the ribbon.

  1. Move the formula to a cell outside the table. It can still read the table using structured references.
  2. If the table structure is no longer needed, select a cell in it and convert it to a range, then re-enter the formula.

This is a documented limitation rather than a bug. Spilled array formulas are not supported in Excel tables, however much free space sits below.

The result cannot be represented in cells

You have this one if #CALC!, most often from a FILTER that matched nothing or from an array nested inside another array.

  1. For an empty result, add the if_empty argument to FILTER so it returns something rather than nothing.
  2. For a nested array, remove the inner array: Microsoft’s example is changing =MUNIT({1,2}) to =MUNIT(2).
  3. For an array of ranges, remove the range reference from inside the array, as in changing =OFFSET(A1,0,0,{2,3}) to =OFFSET(A1,0,0,2,3).
  4. For a LAMBDA in a cell, call it by adding parentheses and arguments to the end of the formula.

The data type behind the cell is not answering

You have this one if #FIELD!, #BLOCKED!, #CONNECT! or #BUSY! on a cell carrying a data-type icon.

  1. For #FIELD!, check that the field you are referencing exists on that record and that the cell actually holds a data type.
  2. For #BLOCKED!, check the licence and sign-in state first, then region availability, then the privacy and Trust Center settings that control linked data types and external connections.
  3. For #CONNECT!, check that the linked file or service is reachable and that you are authenticated to it.
  4. For #BUSY!, wait: it is a formula waiting for a resource that is slow, and it resolves when the resource arrives.

Full reference

Telling the situations apart

What you see What it means
#SPILL! with a dashed outline over occupied cells Ordinary obstruction: clear the range
#SPILL! saying indeterminate size A volatile function inside a spilling formula
#SPILL! in a cell inside an Excel Table Structural: arrays cannot spill within a table
#CALC! from a FILTER The result set was empty and no if_empty argument was supplied
#FIELD! on a cell with a data-type icon The requested field is not present on that record
#NAME? with an _xlfn. prefix The installed build does not have the function at all

The seven published #SPILL! reasons

Sub-message What to do
Spill range isn’t blank Select the obstructing cells from the error menu and clear them
Indeterminate size Remove RAND, RANDARRAY or RANDBETWEEN from the spilling formula
Extends beyond the worksheet’s edge Reference specific lookup values, or copy down a single-row formula
Table formula Move the formula outside the table, or convert the table to a range
Out of memory Reference smaller arrays or ranges
Spill into merged cells Unmerge the cells or move the formula
Unrecognized/Fallback Check that all required arguments are present and correct

What the data-type errors actually check

Error Published meaning
#FIELD! The formula references a field the data type does not have, or a cell with no data type
#BLOCKED! A required resource cannot be accessed: licence, sign-in, region, privacy or a Trust Center block
#CONNECT! The formula cannot connect to a required resource
#BUSY! The formula is waiting for a resource that is taking a long time to access

#BLOCKED! is not one thing

This is the error people most often misdiagnose as a licensing problem, and the published list is longer than that. Microsoft names a missing licence and not being signed in, but also a data type not available in your region, privacy settings blocking data-type viewing, a connected data type that is not compatible with the current Excel version, functions blocked by an application or system setting, external workbook connections disabled, linked data types blocked in the Trust Center, Excel 4.0 macros disabled, and Dynamic Data Exchange disabled. Several of those are Trust Center settings a policy applied without anyone announcing it.

Older builds and workbooks that travel

A build without dynamic array support stores functions it does not recognise with an _xlfn. prefix, and those cells return #NAME? rather than a value. That is not damage and the file is intact; it evaluates correctly again in a build that has the functions. Legacy array formulas entered with Ctrl+Shift+Enter still evaluate in current builds, so there is no need to rewrite them, although one dynamic array formula is easier to audit than a block of identical ones.

Checks that save time

  • Reference a spill range from elsewhere with the hash operator, as in =COUNT(E2#), to confirm it is the size you expected.
  • Change a source value so the array grows by a row and confirm it expands rather than erroring, which is the real test that the range below is clear.
  • Watch for merged cells in inherited workbooks. A merge occupies its whole footprint even though only the top-left cell appears to hold anything.
  • Check the sub-message every time rather than assuming obstruction. Two of the seven reasons have nothing to do with occupied cells.
  • For data-type errors on one machine only, check sign-in and Trust Center settings on that machine before blaming the workbook.

When a licence is the actual fix

Most of this article costs nothing to act on: obstructions, tables, merged cells and FILTER arguments are all worksheet problems. The data-type errors are where a licence can genuinely be the constraint, but be precise about it, because #BLOCKED! covers more than licensing. Microsoft’s published causes for it start with a licence being required and the user not being signed in, then run through region availability, privacy settings, a connected data type incompatible with the installed version, and several Trust Center blocks on linked data types, external connections and legacy macros. Check the free ones first: sign-in state and Trust Center settings account for a good share of what looks like a licensing wall. Where the answer really is entitlement – a data type or a compute feature the current licence does not include – Arco can check what your tenant holds today and what the tier above it actually adds, so you buy against a specific blocked feature rather than against an error message.

Every code this article covers

Code What it points at Source
#SPILL! The array cannot be written to the worksheet; Excel publishes seven reasons, shown as a sub-message beside the error Microsoft Support
#CALC! The calculation engine cannot produce the result: a nested array, an array of ranges, an empty array and several other documented cases Microsoft Support
#FIELD! The formula references a field the linked data type does not have, or a cell that holds no data type Microsoft Support
#BLOCKED! A required resource cannot be accessed: licence, sign-in, region, privacy settings or a Trust Center block Microsoft Support
#CONNECT! The formula cannot connect to a required resource Microsoft Support
#BUSY! The formula is waiting for a required resource that is taking a long time to access Microsoft Support

Confirm the fix worked

  1. The anchor cell shows a value and the spilled range outlines with a thin border when you select it.
  2. =COUNT(E2#) against the spill range returns the number of results you expected.
  3. Changing a source value so the array grows by a row expands it without a new spill error.
  4. Saving, closing and reopening the workbook leaves the arrays recalculating cleanly.
  5. Any data-type cell that showed #BUSY! has resolved to a value rather than staying stuck.

Questions people ask about this

Why does the same formula work in one column and fail in the next?

Because the obstruction is in the target range, not the formula. Move a working formula one column across and it fails too if something occupies the cells it needs.

The error says indeterminate size. What does that mean?

Excel could not work out how big the array would be because it is volatile and resizes between calculation passes. Microsoft names RAND, RANDARRAY and RANDBETWEEN as the functions that cause it. Take them out of the spilling formula.

Do I have to rewrite my old Ctrl+Shift+Enter formulas?

No. Legacy array formulas still evaluate in current builds, though one dynamic array formula is easier to audit than a block of identical ones.

Someone opened my workbook in an older Excel and the formulas broke. Is the file damaged?

No. Older builds store unrecognised functions with an _xlfn. prefix and return #NAME? for them. The file is intact and evaluates correctly again in a build that has the functions.

Is #BLOCKED! always a licensing problem?

No, and assuming so wastes money. Microsoft’s published causes include not being signed in, region availability, privacy settings, and Trust Center blocks on linked data types, external connections and legacy macros, as well as a licence requirement. Check the free ones first.

Related error codes

Was this article helpful?

Your feedback helps us improve our documentation.

Related articles

License Error Unlicensed Product 0xC004F017 in Word, Excel, Visio and Project: Activation Failed Free Fix Access Error 3078 and 2465: Cannot Find the Input Table, Query or Field License Error Send/Receive Timeouts 0x8004210A and 0x8004210B: Outlook Gives Up Waiting on Server Free Fix VBA Runtime Error 91 Object Variable or With Block Variable Not Set
โ† Back to Knowledge Base