New Numbers Update adds @ Operators to my formulas. What is that and should I be concerned?

Numbers has added support for array functions - where certain functions can accept either a range or a specific cell, and the @ sign is its way of forcing which way is used.

In the past your formula said...

=IF($A... )

which was interpreted as 'the cell in Column $A of this row'. However '$A' could also be interpreted as 'the entire column A', and there are many cases where this would be appropriate, with no clear idea as to which method you mean to use... for example, if your function said '=IF(SUM($A)), does it mean the sum of all values in column A? or the current row's column A?

So the addition of the @ sign makes it clear that you want the current value in column A, not the sum (or reference to all cells) of column A.

The notice tells you that Numbers has updated your formulas to the new format, and you should check to make sure they work as expected (e.g. you really want to reference the entire range, not just the current row's value).

As for the 'Spill', that indicates that without the @ sign, Numbers would return an array of values (in this case equal to the number of rows in the table), and would fill down the column to show those results. Again, there are cases where you want this, and cases where you don't. Now you have to be explicit.

Of course, this does mean that you could fill the entire table with one formula in B2 that doesn't use the @$A, so it retrieves all the corresponding values from column A with a single formula.

To get rid of the blue triangles I do this:

1. Click on one of the blue triangles. This opens that little window showing it's warning message.
2. Click on that little arrow to the right ► which opens further options.
3. One of the options is to remove all warnings from all cells.

I do not have the latest version but "spill" sounds like what Excel does in some cases where a formula that has an array in it will automatically fill results to multiple rows instead of having to put individual formulas in all those cells and use INDEX to get each result individually.

Here is a list of the new things:

What’s new in Numbers on Mac - Apple Support

IHadaName wrote:

the Numbers support page (List of functions by category – Apple Support (UK)) does not mention array functions.

My iPad pros all fail to finish calculating on previously working sheets … no errors no links … no explanation … just blue flags, @ added in random sports etc.

where is the documentation?!

Interesting that many of the functions are not (yet) listed at the link you provided.

You'll have better luck here:

Formulas and Functions Help - Apple Support

A search for "array" and then by "lambda "will turn up many of them.

I keep adding to my list below as I discover more. You can look each of these up at the above link. So far I've found LET() FILTER() UNIQUE() and SORT() to be immediately useful.

Also try an internet search, or AI prompt, for Excel dynamic array functions to get a general idea of what they do if you are not already familiar with them. They were introduced in Excel four or five years ago so you'll find quite a bit about them.

SG

Numbers Dynamic Array functions (and a few new non-dynamic functions)

Added in Numbers v. 14.4, April 2025

Logical & Info:

BYROW()

BYCOL()

ISOMITTED()

LAMBDA()

LAMBDA.APPLY()

LET()

MAKEARRAY()

MAP()

REDUCE()

SCAN()

Numeric:

RANDARRAY()

MDETERM()

MINVERSE()

MMULT()

MUNIT()

SEQUENCE()

Reference:

AREAS()

CHOOSECOLS()

CHOOSEROWS()

DROP()

EXPAND()

FILTER()

HSTACK()

SORT()

SORTBY()

TAKE()

TOCOL()

TOROW()

UNIQUE()

VSTACK()

WRAPCOLS()

WRAPROWS()

Statistical:

EXPONDIST

Text:

ARRAYTOTEXT()

TEXTSPLIT()

DATE and Time

ISOWEEKNUM()

Changed behavior of "old" functions

OFFSET() can now spill

XLOOKUP() can now spill ?

VLOOKUP() multiple lookup values?

XMATCH()

SUMIFS() will spill

New Operators

# spilled range operator

Used to reference the entire spilled range of a formula by placing # after a reference to a spilling origin cell.

See entry for REDUCE()

@ implicit intersection operator

Adds compatibility with Excel’s dynamic array formula system.

Hi Badunit - nice to see a familiar name!

It was a shock to open an important document that I use weekly and see all those blue flags. That, and the explanation in the pop-up seems to assume fluency with array calculations; I'm not.

As advised, I looked over the results of the calculations with the imposed "@" operator and it all looks ok. Now, how do I go about clearing all those pesky blue triangles? Any thoughts? I hope I don't have to visit each and every instance in every old sheet.

Jerry

tinmanjc wrote:

Isn’t that why you put the $ in? To differentiate between the column and the cell? eg: No $ would only include the cell as where no $ would include the entire column. The reason for no $ traditionally is so a formula can travel down a column and the table assume your formula is advancing to the next row/column and therefore the reference has also moved.

The $ was and still is an "anchor" for a range when filling a formula. It is not used to differentiate between the column and the cell. The $A in a formula means that when you fill a formula right it still refers to column A rather than adjusting to column B, C, etc.

A simple example may help show when you might want the @ operator:

In previous versions of Numbers you might use =A*B or $A*$B and the result would be one value calculated using the values in the row your formula is on , e.g. if on row 2 equivalent to =A2*B2 or =$A2*$B2

But now if you enter =A*B or $A*$B you get not just one value but an array of values "spilled" down into the cells below as shown in columns C and D of my example.

That can be convenient because you no longer have to fill the formulas down manually. You enter (or edit) a formula in one cell and Numbers takes care of the rest.

But if you want the old behavior then you can use =@A*@B or =A2*B2 and fill the formula down.

SG

Hi All,

Replying to myself here to report what I've observed in the last few minutes...

Double-clicking a cell to switch to edit mode and then clicking the Green Dot to accept the expression clears the flag. Can't say I'm real happy about that solution. So, I tried copying a block, essentially everything in the screen shot, and then pasting it over the copied area. That quickly cleared the entire mess. To anyone who is tempted to try that, make a backup first!

Jerry

Jerry! Great to see you here again. I hope life has been treating you well. I wish I could have answered your question about the blue triangles but my computers are too "old" to run the latest version of Numbers. My usefulness on this forum has taken a sharp nosedive this week.

Hi Ian,

I should get on here more often, not just when I get blindsided by a big "improvement".

Every day I get a thank you email for a decades old solution post. Sometimes I'm tempted to check my answer to see if it still makes any sense in the current operating environment, but so far I haven't done that.

Soon this 2018 MBP will be too old to support an upgrade and will be immune to the improvements. I'm not completely out of the woods though. Recently a good friend, forced to leave his home and enter a senior living arrangement, gave me a newer iMac that he no longer has room for. For the time being, that iMac is dedicated to an audio editing workstation and won't be running Numbers. But wouldn't you know it, the programmers who maintain Audacity have managed to complicate the most recent version to the point of making it more tedious to use. Must be the software writer's mantra, make it complex!

Jerry

“Numbers has added support for array functions…”

are these documented anywhere?

the Numbers support page (List of functions by category – Apple Support (UK)) does not mention array functions. You cannot search for “@“, array or spill in the support pages, and the whats new page talks at length about 3D images and ai nonsense (!!) but only links to a generic page for the functions … which do not mention any new functions explicitly!

My iPad pros all fail to finish calculating on previously working sheets … no errors no links … no explanation … just blue flags, @ added in random sports etc.

where is the documentation?!

tinmanjc wrote:

I would be very careful when using this new feature as it will not allow you to sort a spill and if the row (record) that includes the formula is deleted so is the spill and the formula forever.

The last part, that you lose the spill and the formula if you delete that one cell, is a good observation.

But you can sort. Just use the SORT function.

SG

Hi Jerry,

Welcome back! It is good to see you again. I have not yet updated to the latest ("array" with "@" warnings) version of Numbers, but I shall follow this discussion with interest.

Regards,

Ian.

I have a table with about 7,000 cells most of which had the blue flags. They referenced a column of dates. I selected all and did "copy and paste" and it worked. All the flags went away. Everything looks clean now.

Ditto here. I launched a spreadsheet with a lot of complex formulas and discovered the blue triangles, @ symbol and explanation in the numerous tables. Rather than peruse the tables for accuracy I checked the graphs plotting the results. They look OK, so far. I think if Number's interpretation of my logic was in error, as indicated by the @ symbols, the tables would have turned to garbage and the graphs similarly.

Going forward I'll stick with the @ symbol leading a formula that can be interpreted multiple ways until I am comfortable understanding Numbers' new paradigm. Reading up on the updates I see the new version as a plus overall.

The @ operator was added in a Numbers update to clarify formulas using single values from ranges. It’s usually harmless if your formulas still work.

The spill error means a formula is trying to fill multiple cells (B1:B116), but those cells already have data. Clear them if you want the formula to auto-fill.

If everything works, you’re fine but you might explore spill formulas to reduce copying formulas manually.

Everest