Sort blank cells to bottom even when resulting from a formula

[Azzu]

Hello, I have a problem with the AutoFilter sort.

If a row contains no value in a column (i.e. ISBLANK() returns true), and you sort by that column, all blank rows correctly get sorted at the bottom, no matter if you sort ascending or descending.

However, if you have a formula that returns an empty string "", these get sorted to the top when you use the “Descending” sort.

Is it somehow possible to always sort empty cells to the bottom?

I have attached a test file test.ods (24.0 KB).

I want Column 2 and 3 to have exactly the same behavior when you sort them descending. However, if you sort Column 2 descending, all “blank” values are on top.

Is there any way to get this behavior?

[JohnSUN]

Welcome @Azzu !
What about {} instead ""?

=IF(A2<0;{};A2)

[Lupp]

See LibreOffice 26.2 Calc {} for empty in formula gives {#NB} and my answer there.

[JohnSUN]

Yes, my friend, I remember that discussion. But if the “dirty hack” works, then why not use it? At least it still worked in 25.2.3.2, and Azzu didn’t say which version he was using.

[Azzu]

That does not work, the resulting value seems to now be sorted before numbers. So yes, now the descending order works, but now the ascending order has a bunch of empty values in front, column 2 and 3 in the test file still have differing behavior.

I’m using 26.2.3.2.

Edit: actually, even worse, now the empty values are located between 0 and 1, which is super counterintuitive.

[JohnSUN]

Okay, let’s try this differently - you explain in a little more detail what functionality you want to achieve from the program using sorting, and together we’ll think about alternative solutions.

[Azzu]

I have rows that each represent a physical item. I have multiple different columns that indicate a kind of “performance” for the item. However, these performance columns are each only defined for certain items, not all of them.

I now want to be able to compare these items quickly, by adjusting the sort on the performance columns. However, as soon as I adjust the sort to “descending”, now all the items where the performance value is not defined are on top.

I know that I could just exclude the (empty) value of the individual columns, hiding the items that do not have that performance value defined. That works. However, it is two more clicks each time, and when I then want to switch to sorting by another performance value, I now have to first unhide the empty values of the first column, hide the empty values of the second column, and then sort. It’s just annoying when I feel like the feature already exists (having empty cells always sorted at the bottom).

[JohnSUN]

@Azzu

having empty cells always sorted at the bottom

Yes, that’s true—the problem is that an “empty row” isn’t an “empty cell.” Are you absolutely opposed to using macros in your spreadsheet?

[Azzu]

I’m not opposed to macros, why would I?

[JohnSUN]

I had to ask After offering a fairly effective solutions more than once, veterans of this resource received the response, “I don’t want macros, they’re unsafe.” It was simply a waste of effort.
I propose a scenario: double-clicking a column header cell (does your table always start with the first row?) The macro cancels previous filters, sets the “no empty” filter for the current column, and sorts in ascending order. We can re-sort this column in descending order without a macro, using AutoFilter menu.