How to fix #Spill Error in MS Excel?


Rather than sitting on its laurels, Microsoft has been constantly updating its Office Package with new features to make life easier for its users. As a part of this package, MS Excel also gets its own share of new features in every update & one such update is the ‘Dynamic Arrays’.

Before this feature was introduced, one needs to construct a formula for handling multiple values across a column & use flower brackets ‘{ }’ for MS Excel to read that it ain’t a regular formula, followed by pressing CTRL+ENTER to get the results. But with the homecoming of the ‘Dynamic Arrays’ feature, this all ends.

Also read: How to Fix #N/A Error in MS Excel?

Let us consider the following data for instance, for which we need to find the list of unique values (i.e) stripping the list of the repetitions.

Sample Dataset
Sample Dataset

Without ‘Dynamic Array’ we either need to copy this list to a new column & use ‘Remove Duplicates’ to find the list of unique values or to write a lengthy, complicated array formula to get the job done. But now, one is saved from the misery of going through these by making use of UNIQUE!

=UNIQUE (array, [by col], [exactly once])

Given above is the syntax for this ‘Dynamic Array’ formula where,

  • array – the range of cells which is to be analysed
  • [by_col] – an optional construct in which TRUE is to be given for returning unique values across columns & FALSE is to be given for returning unique values across rows
  • [exactly_once] – an optional construct in which TRUE is to be given for returning items that appear exactly once & FALSE is to be given for returning every distinct item

TRUE is the default selection for the optional constructs given above. Let us now use this formula for extracting the unique values from the list given earlier.

Unique Formula Constructed
Unique Formula Constructed

One could observe that the optional constructs are omitted from the above formula & only the range of cells to be analysed is selected. Hit ENTER & you are in for a surprise!

Unique Formula Spilled
Unique Formula Spilled!

The formula hasn’t been pasted across the cells – NO! Once ENTER is hit, the formula has itself adjusted to fill all the unique values across the column in which it has been constructed. Such is the power of the ‘Dynamic Array’. “Enough praises for this new feature & where is the #SPILL error?” you ask. We are heading there!

The Advent of #SPILL Error:

Have a look at the image below.

#SPILL Error

Something has stopped our UNIQUE formula from spilling the results across the column (Yep, you’ve guessed it right! Since it prevents spilling of the formula, thusly the name #SPILL error). Upon closer observation, it can be seen that there’s a dot ‘.’, a tiny dot within the range in which the results are to be spilt.

The Dot Obstacle
The Dot Obstacle

Remove this dot ‘.’ & the formula will work like a charm!

Dot Obstacle Removed
Dot Obstacle Removed

#SPILL Error in Table:

Things slide from bad to worse when ‘Dynamic Arrays’ are deployed in Tables. The inherent feature of the table to apply a formula typed in any cell of a column defeats the very purpose of using this formula. The following error comes up when the entire range of column K is selected within the UNIQUE formula.

Table with SPILL Error
Table with #SPILL Error

The error can be removed using the implicit intersection operator ‘@’ as shown below. What this does is rather than using the entire array, it makes the formula in a particular cell only use the corresponding single cell value.

SPILL Error Removed in Table
#SPILL Error Removed in Table

But these results don’t provide any meaningful inference. Thusly, dynamic array formulae are better not to be used within the tables.


Have a look at this article which elaborates further on the usage of ‘@’ within the formulae. There are numerous other articles in QuickExcel that can help you to learn the tricks & nuances of using MS Excel. Cheers!