Any solution for #SPILL error in Excel Table

Copper Contributor

Hi,

 

Is there any solution for #SPILL error in Excel Table while using array formulae?

 

Spill error in Excel Table.PNG

5 Replies

@anshul-marele 

It is simply not possible/allowed to use dynamic array functions (like UNIQUE) inside a structured table. 

But this is the big flaw, because Tables are the most structured way to work in Excel and if we cannot use ARRAY functions inside it, we cannot scale-up in such an organized way.

@anshul-marele But structured tables are by themselves already "spilling" automatically. Using a dynamic array function inside such a table would create problems.

In your example, what would be the expected result in column H ("Hdr4") with that formula?

@Riny_van_Eekelen 

I understand that, but when you use power query, power automate, etc. where table format is the default output and you need to use some array functions, you stuck.

 

Please check below a example. 

 

Spill error in Excel Table_2.PNG

@anshul-marele Just enter the following in H3:

 

=SUM(Table1[@[Hdr1]:[Hdr3]])

 

The @ sign tells Excel to look at cells on the current row. The structured table feature does the rest and auto-populates all cells below, similar the spilling. No need for BYROW here.