Sort function is greyed out in version 14.4 and error message

Unable to sort columns in the latest version. I use a fairly large spreadsheet with numerous XLookup formulae. When I attempt to sort a column the sort options in the right click menu are graded out and I get this error message: 'The table contains a formula that spills its results across rows.'


Running MacOS Sequoia 15.3.2.

Mac mini, macOS 15.3

Posted on Apr 17, 2025 7:42 AM

Reply
3 replies
Sort By: 

Apr 18, 2025 11:40 AM in response to bcodding

The message is telling you what the issue is.


Numbers 14.4 added support for functions. It's likely that you're using a formula that is in previous versions would return a single value, but now returns an array, which spans multiple cells. These cells can't be sorted in the same way, since there's no clear way to sort around (or within) the array.


So you need to look at your sheet for functions that return arrays. Usually Numbers will highlight these - either with a blue notice triangle telling you that it updated the formula to support arrays, or by a green hashed area around the cells that are filled with the array results. Until you identify those cells/areas, it's impossible to be more specific on what changes you need to make.

Reply

Apr 19, 2025 10:57 AM in response to bcodding

I'm just getting started with this new version 14.4 but it appears that if you use use range references like A, B, C or A2:A20, B2:B20, C2:C20 and do not preface them with an @ symbol, the formula will "spill" down. Edit the uppermost formula and add the @ symbol to all those range references then fill down like you normally would to complete the column.


I haven't opened any of my older documents yet to see how they get upgraded. When both of my computers are compatible with the latest version I will do that.


Edit: I just found out documents opened and saved or created in 14.4 will open in 13.1. That is a welcome bit of backward compatibility.

Reply

Apr 20, 2025 1:49 AM in response to Badunit

Badunit, thank you. I was working from the other direction trying to use the spilled range operator '#' to reference an array created by a formula. Here's an explanation from Apple Support Spilled Range Operator use.


Your's, however, is a more elegant solution. This is the first part of a XLOOKUP function showing where to place the ampersand. I think it will be quicker to update the many range formulae I have.

Reply

Sort function is greyed out in version 14.4 and error message

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.