How to stop “spill” in Numbers on ipad

You need to show the formula to get a direct answer.

The long answer is that some functions can return a range of values, but versions of numbers before 14.4 would only show the first value in the range. That was fine for most cases, and many people built formulas based on that.

However, in Numbers 14.4, Apple added support for arrays and that function which used to return a single value now can return an array of values. Where possible, Numbers handles this by 'spilling' the extra values into adjacent cells.

Numbers tries to guess at whether you want the 'old' or 'new' method when opening an older spreadsheet, but sometimes it gets it wrong.

In either case, you have control over it. For any reference that returns a range, precede it with @ to use the intersection of the array and the current row, rather than the entire array.

For example, in cell B1 of any given spreadsheet, the formula:

=A

will copy the values in column A and fill down the column to match (cells B2 to the bottom of the table are 'spilled')

Setting cell B1 to:

=@A

will copy the value from column A that matches this cells' row (e.g. A1)

Technically, you could get the same result by using the formula =A1, but there are use cases where the range is simpler and easier.

WoundedWolf, I'm so frustrated with this update! I know EXACTLY what you mean.

It's been driving me nuts for weeks.

No amount of googling seems to explain anything even close to what is actually happening, when, like you say, it used to be so simple.

For example: If I have column B on a 100 row sheet, I would usually be able to calculate subtotals in rows for each separate category and then have a TOTAL cell that I entered the formula to add them all together.

It was simple - you would select the cell you wanted the result to appear in, hit = and then use the mouse to capture the range of cells that you wanted the total for. When you let go of the mouse, the result was there.

E.g Column B, row 6 = Column B Row 1:5

The total would appear and you were done.

Now when you do that it automatically spills over into the following cells and whatever the logic is that's causing that to happen is buried somewhere in no-mans land.

After A LOT of frustration and messing around, I seem to have found a solution, but it isn't as efficient as the previous method and some explanation as to what has changed for something that was once so simple would have been a courtesy for loyal customers rather than leave us less formula minded folk to work out what on earth has changed. It's messing with so many previously functional spreadsheets and creating havoc when working with them.

It wouldn't be so bad if the answers were available when you follow the links that promise to provide them, but it's nothing but dead ends.

The work around seems to be to:

1. Select the cell
2. Enter the equals sign (=)
3. Type SUM
4. Open brackets (
5. Manually type the range you need e.g. B1:B5
6. Close brackets )
7. Hit enter and your result is in the cell without spilling.

The point to the option of spilling seems a bit pointless if you are a basic user providing basic commands.

The option to use it or not, or some information about how to adapt to the new changes would be extremely useful instead of leaving loyal users frustrated and in the dark.

PractisePower wrote:

5. Manually type the range you need e.g. B1:B5

Do you have a lot of "Excel-like" references to ranges within a column in a table?

If so you might consider breaking your work up into separate tables so that you can refer to the entire column, e.g. SUM(B) rather than SUM(B1:B5).

If you haven't done so already, I recommend having a look at the built-in templates at File > New in the menu for good examples of efficient document design in Numbers.

SG

It would help if you could show the specifics of your situation, what your table looks like, exactly what formula you are using and where.

If you are summing, say, column B, make sure you have a Footer Row at the bottom of the table, and in the appropriate cell enter =SUM(B).

SG

Two thoughts come to mind...

PractisePower wrote:

...

The work around seems to be to:

Select the cell

2. Enter the equals sign (=)

3. Type SUM

4. Open brackets (

5. Manually type the range you need e.g. B1:B5

6. Close brackets )

7. Hit enter and your result is in the cell without spilling.

That's a little wordier than needed.

After typing SUM you can press return to accept that function. Numbers automatically enters the parentheses (opening and closing) and selects the parameter field for what to SUM(). You can either type or drag over the cells to mark the range, then press return again.

Beyond that, though, I suggest you're doing it wrong... or at least 'old school' :)

Specifically, to mention:

> I would usually be able to calculate subtotals in rows for each separate category and then have a TOTAL cell that I entered the formula to add them all together.

For that I suggest you look at Numbers' Categorization functions that will do all this in a couple of clicks.

For example, let's say you have this table of names and values and you want to categorize it by person:

in the Inspector -> Organize -> Categories you can tell Numbers how to categorize the data:

Choosing 'Person' results in automatic grouping of the data by person:

To get subtotals of the various categories, click any of the Payment cells alongside any name (e.g. Bob/Payment) and click the gear wheel to get a list of how you want to summarize the categories. Choose Sum:

Done:

Numbers will automatically take care of grouping each row into the respective category. It will add new categories as names are added, and will track the subtotals.

It's also easy to suppress the individual rows by toggling the disclosure triangle alongside each category label (Name, in this case). This makes it trivial to get a summary table with subtotals, without the minutiae of the every row being shown.

Manually laying out this type of form is, for most cases, no longer necessary.

In general, though, spreadsheets have evolved from massive tables of rows and columns, that you have to define to the nth degree. Let it do more of the heavy lifting for you.