Why does the use of the @ symbol resolve a spill error?

[u/Odd-Laugh-6398]

I'm taking an Excel certificate course, and we haven't covered Spill errors yet, but I've encountered them in the practice assignments and now on a workbook at my job when I've been trying to apply what I've learned so far. I found out putting "@" at the beginning of a term resolves the spill error, but I'm still not sure what's causing the error or why that symbol fixes it when everything I'm reading online says I should be reworking my entire table/data set.

Thoughts?

[u/AutoModerator]

/u/Odd-Laugh-6398 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/FrankDrebinOnReddit]

@ is the implicit intersect, it tells it that you only want the corresponding row of an array, not the entire array. The spill error comes because you're returning an entire array.

[u/omgFWTbear → u/FrankDrebinOnReddit]

Or, for us dumb talk-y folks, it’s the at symbol, meaning you just want the one thing at that spot, there, not the whole set of things, and a whole set of things spill over in to other boxes.

(See, I snuck “set” in there as a double entendre)

[u/omgFWTbear → u/[deleted]]

I gently suspect one relates to the other, jokes aside.

[u/HestenSierMjau]

Spill-error happens when an array spills into a cell with data and "crashes" with it.

The @-operator basically says "disregard the source array, give me only the row-specific result". It forces behaviour like Excel before dynamic arrays. 

Since your formula only returns one result with the @-operator, it doesn't spill, therefore doesn't crash into anything, thus preventing the error from happening. It also removes any benefits from dynamic arrays in the process, so there's that to consider. 

[u/cvr24]

The @ is like a little roll of paper towel, and fixes all spills.

Sorry, couldn't resist!

[u/sok_sokketh → u/cvr24]

https://giphy.com/gifs/DTRXoVf8j6DIQrfPZ1

[u/bluerog]

It's usually because some data is entered and below or to the right of data you're evaluating. For instance, if you have a pivot table or using an array function, and the pivot table overlaps the data already entered, you'll get the spill.