#Spill! Error in table

%3CLINGO-SUB%20id%3D%22lingo-sub-1617349%22%20slang%3D%22en-US%22%3E%23Spill!%20Error%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617349%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20having%20an%20issue%20with%20this%20formula%20that%20is%20within%20this%20table%20I%20am%20creating.%20Whenever%20I%20convert%20a%20selected%20area%20with%20a%20formula%20within%20it%2C%20I%20get%20a%20spill%20error.%20It%20says%20to%20either%20convert%20it%20into%20a%20range%20or%20move%20the%20formula%20out%20of%20it%2C%20but%20I%20am%20required%20to%20have%20it%20stay%20as%20a%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20fix%20this%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1617349%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617384%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20Error%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769641%22%20target%3D%22_blank%22%3E%40Lucas_Yost-117%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20to%20change%20your%20formula%20so%20that%20is%20returns%20only%20one%20value%20instead%20an%20array%20of%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617388%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20Error%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769641%22%20target%3D%22_blank%22%3E%40Lucas_Yost-117%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESpill%3C%2FSTRONG%3E%20errors%20are%20an%20outgrowth%20of%20a%20nice%20new%20feature%20(don't%20you%20love%20it%3F!)%2C%20which%20is%20Excel's%20ability%20to%20handle%20Dynamic%20Arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20functions%20now%20return%20results%20in%20the%20form%20of%20an%20array%2C%20data%20displayed%20in%20rows%20and%20columns%2C%20the%20specifics%20depending%20on%20the%20function%20in%20each%20case.%20My%20guess%20is%20that%20you're%20expecting%20just%20a%20single%20cell%20to%20be%20filled%2C%20but%20the%20function%20(you%20don't%20specify%20what%20function(s)%20are%20giving%20you%20the%20error)%20is%20coming%20up%20with%20an%20answer%20that%20requires%20more%20space%20than%20is%20available.%20So%20you%20get%20the%20%3CSTRONG%3ESpill%3C%2FSTRONG%3E%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20the%20full%20result%2C%20you'll%20need%20to%20delete%20any%20content%20in%20the%20cells%20that%20are%20blocking%20the%20full%20array%20to%20show.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20YouTube%20video%20that%20I%20found%20personally%20to%20be%20very%20helpful%20on%20the%20power%20of%20these%20Dynamic%20Array%20features.%20It%20may%20help%20you%20both%20understand%20the%20%22why%22%20of%20it%2C%20and%20the%20%22how%22%20of%20taking%20advantage%20of%20these%20changes.%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617463%22%20slang%3D%22en-US%22%3ERe%3A%20%23Spill!%20Error%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769641%22%20target%3D%22_blank%22%3E%40Lucas_Yost-117%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELeaving%20raw%20and%20imported%20data%20within%20the%20table%20but%20moving%20calculations%20out%20to%20use%20the%20dynamic%20array's%20spilt%20ranges%20is%20often%20the%20best%20solution.%26nbsp%3B%20If%20you%20wish%20to%20stay%20within%20the%20bounds%20of%20the%20table%2C%20then%20the%20modifications%20required%20for%20the%20formula%20may%20be%20as%20simple%20as%20replacing%20references%20to%20%3CSTRONG%3E%5BColumn1%5D%3C%2FSTRONG%3E%20by%26nbsp%3B%3CSTRONG%3E%5B%40Column1%5D%3C%2FSTRONG%3E%26nbsp%3B%20%3CEM%3Eetc%3C%2FEM%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I am having an issue with this formula that is within this table I am creating. Whenever I convert a selected area with a formula within it, I get a spill error. It says to either convert it into a range or move the formula out of it, but I am required to have it stay as a table.

 

How do I fix this? Thanks.

4 Replies

@Lucas_Yost-117 

You have to change your formula so that is returns only one value instead an array of values.

 

@Lucas_Yost-117 

 

Spill errors are an outgrowth of a nice new feature (don't you love it?!), which is Excel's ability to handle Dynamic Arrays.

 

Many functions now return results in the form of an array, data displayed in rows and columns, the specifics depending on the function in each case. My guess is that you're expecting just a single cell to be filled, but the function (you don't specify what function(s) are giving you the error) is coming up with an answer that requires more space than is available. So you get the Spill error.

 

To get the full result, you'll need to delete any content in the cells that are blocking the full array to show.

 

Here's a YouTube video that I found personally to be very helpful on the power of these Dynamic Array features. It may help you both understand the "why" of it, and the "how" of taking advantage of these changes. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

@Lucas_Yost-117 

Leaving raw and imported data within the table but moving calculations out to use the dynamic array's spilt ranges is often the best solution.  If you wish to stay within the bounds of the table, then the modifications required for the formula may be as simple as replacing references to [Column1] by [@Column1]  etc.

thank you! this is helpful!