#SPILL! problem

Copper Contributor

Office 365, on PC

I have a table with categories (Table2) that I use for a dropdown menu and for sum up of my economy. So far I need to expand it so I need the drop down menu and the sum up to be dynamic.



Drop down menu:


Sum up:




This formula works fine but if I try to make it into a table it gives me an error:



Any solutions to this error?

Regards Kasper



4 Replies


You try to return entire column Table1[Kolonne1] into the cell in another table. There is no room in the cell for entire column, it could take only single value. Thus that error.

Didn't catch what actually you try to do.

Sorry, maybe my post wasn't to clear.
I would very much like the "Kategori" in column K to be the exact same as Table2(Kolonne1) in column C. So when I add more categories to Table2[kolonne1] it appears in column K. And if possible I would like the dropdown menu to change as well.
Hope it makes sense, and thanks for your time.


I  think you are looking for a pivot table.



If you want to return a column, do not place such a calculation within an Excel Table, place it outside the Table as a Spill Range.  If you get rid of the pointless stripes from the table it is easy to format the column to blend with the table.  On the other hand, if the stripes are important to you then a conditional format using ISEVEN() would achieve that.