Spill Error in Excel when using Table format.

%3CLINGO-SUB%20id%3D%22lingo-sub-1461154%22%20slang%3D%22en-US%22%3ESpill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461154%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20keep%20getting%20the%20%23Spill%20Error%20when%20using%20the%20dynamic%20Xlookup%20function.This%20only%20happens%20when%20I%20use%20the%20Table%20format%20i.e.does%20not%20happen%20when%20I%20change%20the%20Table%20to%20a%20Range.%3C%2FP%3E%3CP%3EI%20tried%20looking%20it%20up%20onGoogle%20but%20it%20clearly%20says%20%22Spilled%20array%20formulas%20aren't%20supported%20in%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Foverview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20tables%3C%2FA%3E.%20Try%20moving%20your%20formula%20out%20of%20the%20table%2C%20or%20converting%20the%20table%20to%20a%20range%20(click%20%3CSTRONG%3ETable%20Design%20%26gt%3B%20Tools%3C%2FSTRONG%3E%20%26gt%3B%20%3CSTRONG%3EConvert%20to%20range%3C%2FSTRONG%3E)%22%3C%2FP%3E%3CP%3EIs%20there%20any%20work%20around%20to%20not%20get%20a%26nbsp%3BSpill%20Error%20using%20the%20Table%20format%20%3F%3F%3C%2FP%3E%3CP%3EThanks%20and%20Regards.%3C%2FP%3E%3CP%3EAspi.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1461154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461199%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698641%22%20target%3D%22_blank%22%3E%40akolah1966%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20array%20formulas%20retrieving%20data%20from%20Excel%20Tables%20frequently%20(ever%20since%20gaining%20access%20to%20the%20Dynamic%20Array%20functions).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20experience%20with%20the%20SPILL%20error%20is%20that%20it%20occurs%20when%20some%20cell%20or%20cells%20are%20occupied%20with%20data%20and%20in%20the%20way%20of%20the%20results%20of%20the%20array%20function%20spilling%20out%20to%20its%20fllest%20extent.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20using%20the%20formula%20IN%20the%20table%20itself%3F%20I%20can%20imagine%20that%20wouldn't%20work%3B%20never%20occurred%20to%20me%20to%20try%20it.%20They're%20excellent%20when%20retrieving%20data%20FROM%20an%20Excel%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20maybe%20you%20could%20clarify%20exactly%20what%20you're%20doing%20and%20wanting%20to%20do....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461332%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461332%22%20slang%3D%22en-US%22%3E%3CP%3EI%26nbsp%3Bagree%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%3C%2FP%3E%3CP%3EFor%20an%20array%20formula%20to%20work%20within%20a%20Table%20it%20must%20return%20only%20a%20single%20value%3B%20no%20spilling%20allowed.%26nbsp%3B%20In%20such%20situations%20it%20%3CU%3Ecan%3C%2FU%3E%20be%20useful%20to%20have%20the%20Table%20replicate%20the%20formula%20down%20the%20column%20to%20produce%20equivalent%20calculations%20for%20each%20record.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20array%20formulas%20that%20return%20multiple%20results%20as%20a%20spilt%20array%2C%20then%20one%20must%20place%20the%20formula%20outside%20the%20table%2C%20ideally%20with%20an%20intervening%20blank%20column%20(which%20can%20be%20reduced%20in%20width).%26nbsp%3B%20As%20rows%20are%20added%20to%20the%20data%20table%2C%20the%20dynamic%20array%20formula%20will%20adjust%20accordingly%20if%20it%20involves%20a%20structured%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461542%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698641%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40akolah1966%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%3CEM%3EAs%20rows%20are%20added%20to%20the%20data%20table%2C%20the%20dynamic%20array%20formula%20will%20adjust%20accordingly%20if%20it%20involves%20a%20structured%20reference.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20wanted%20to%20underscore%20the%20usefulness%20(the%20value)%20of%20that%20feature.%20It%20makes%20it%20easy%20to%20use%20a%20combination%20of%20an%20Excel%20Table%2C%20one%20or%20two%20Dynamic%20Arrays%20functions%20(notably%20UNIQUE%2C%20FILTER%20and%20SORT)%20to%20create%20Data%20Validation%20lists%20that%20are%20themselves%20dynamic%20and%20growing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20simple%20example%20(created%20in%20response%20to%20another%20posting%20dealing%20with%20Data%20Validation%2C%20where%20the%20desire%20had%20been%20to%20create%20Data%20Validation%20lists%20where%20the%20second%20selection%20would%20change%20based%20on%20the%20first).%20This%20example%20uses%20a%20table%20containing%20first%20and%20last%20names%3B%20the%20user%20can%20select%20a%20first%20name%20(with%20data%20validation%20against%20a%20list%20of%20unique%20first%20names)%2C%20followed%20by%20selection%20of%20last%20names%20only%20of%20those%20whose%20first%20name%20is%20the%20same%20as%20the%20first%20selection.%26nbsp%3B%26nbsp%3B%20The%20sheet%20includes%20commentary%20on%20the%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20this%20kind%20of%20data%20validation%20structure%20in%20my%20personal%20budget%20and%20expense%20tracking%20spreadsheet%2C%20for%20expense%20categories%20and%20sub-categories.%20It%20allows%20me%20to%20add%20new%20sub-categories%20and%20major%20categories%20on%20an%20ad%20hoc%20basis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20also%20useful%20in%20generating%20summary%20reports%2C%20almost%20like%20your%20very%20own%20pivot%20table....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elots%20of%20applications.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462040%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BHi%20Peter%2C%20thank%20you%20for%20your%20response.%20I%20now%20understand%20that%20I%20will%20need%20to%20have%20a%26nbsp%3Bblank%20column%20after%20my%20Table%20and%20then%20use%20the%20dynamic%20validation%20to%20get%20the%20unspilled%20results.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EAspi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462045%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20in%20Excel%20when%20using%20Table%20format.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BHi%20Mathetes%2C%20thank%20you%20very%20much%20for%20your%20response%20and%20for%20the%20example%20attached.%20That%20helps%20a%20lot%20to%20understand%20how%20eveything%20works.%3C%2FP%3E%3CP%3EBest%20Regards.%3C%2FP%3E%3CP%3EAspi.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

I keep getting the #Spill Error when using the dynamic Xlookup function.This only happens when I use the Table format i.e.does not happen when I change the Table to a Range.

I tried looking it up onGoogle but it clearly says "Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range)"

Is there any work around to not get a Spill Error using the Table format ??

Thanks and Regards.

Aspi.

5 Replies
Highlighted

@akolah1966 

 

I use array formulas retrieving data from Excel Tables frequently (ever since gaining access to the Dynamic Array functions).

 

My experience with the SPILL error is that it occurs when some cell or cells are occupied with data and in the way of the results of the array function spilling out to its fllest extent.

 

Are you using the formula IN the table itself? I can imagine that wouldn't work; never occurred to me to try it. They're excellent when retrieving data FROM an Excel Table.

 

So maybe you could clarify exactly what you're doing and wanting to do....

 

Highlighted

I agree with @mathetes

For an array formula to work within a Table it must return only a single value; no spilling allowed.  In such situations it can be useful to have the Table replicate the formula down the column to produce equivalent calculations for each record.

 

For array formulas that return multiple results as a spilt array, then one must place the formula outside the table, ideally with an intervening blank column (which can be reduced in width).  As rows are added to the data table, the dynamic array formula will adjust accordingly if it involves a structured reference.

Highlighted

@akolah1966 

 

@Peter Bartholomew  wrote: As rows are added to the data table, the dynamic array formula will adjust accordingly if it involves a structured reference.

 

I just wanted to underscore the usefulness (the value) of that feature. It makes it easy to use a combination of an Excel Table, one or two Dynamic Arrays functions (notably UNIQUE, FILTER and SORT) to create Data Validation lists that are themselves dynamic and growing.

 

I've attached a simple example (created in response to another posting dealing with Data Validation, where the desire had been to create Data Validation lists where the second selection would change based on the first). This example uses a table containing first and last names; the user can select a first name (with data validation against a list of unique first names), followed by selection of last names only of those whose first name is the same as the first selection.   The sheet includes commentary on the formulas.

 

I use this kind of data validation structure in my personal budget and expense tracking spreadsheet, for expense categories and sub-categories. It allows me to add new sub-categories and major categories on an ad hoc basis.

 

It's also useful in generating summary reports, almost like your very own pivot table....

 

lots of applications.

Highlighted

@Peter Bartholomew Hi Peter, thank you for your response. I now understand that I will need to have a blank column after my Table and then use the dynamic validation to get the unspilled results.

Regards

Aspi

Highlighted

@mathetes Hi Mathetes, thank you very much for your response and for the example attached. That helps a lot to understand how eveything works.

Best Regards.

Aspi.