Sortting ranges with merged cells

users
#1

Hi,

If we try to sort ranges with merged cells in the Calc, we see the message
«Ranges containing merged cells can only be sorted without formats». This
message implies that such ranges can be sorted without splitting cells. But I
do not understand how to do it.
Could anyone explain how to do it to me, please?
Thank you in advance.

Best regards,
Lera

#2

In the Sort dialogue (at Data | Sort...), on the Options tab, remove the tick from "Include formats".

I trust this helps.

Brian Barker

#3

Hi Brian,

In the Sort dialogue (at Data | Sort...), on the
Options tab, remove the tick from "Include formats".

I trust this helps.

Yes, it is what I was looking for. But it works a bit in different from I have
imagined. I will investigate this issue further.
Thanks again.

Best regards,
Lera

#4

Lera - I believe this is a poorly worded information message. I have not
found any way to sort a range with merged cells including selecting the
whole range, right-clicking and selecting "Clear direct formating".

It seems merging of cells is treated as a formating option, at least in the
case of this message.

#5

Well, the message has certainly misled you. You are suggesting here that "without formats" means that you cannot sort cells to which some sort of formatting has been applied. But no: that's not what it means.

When you sort cells generally, the range may have different formatting applied to different cells: imagine, for example, that one of the cells has a different font colour. Is that colouring associated with the particular cell or with the value it contains? It could be either. When you sort the values, do you want that colouring to stay in the same cell or to move with the value as the range is sorted? That will depend on why you have applied that colouring, so you have a choice. This is controlled by "Include formats" on the Options tab of the Sort dialogue. If you have this ticked, then formatting moves with the values; otherwise it stays in the same cells.

You can certainly sort a range that includes merged cells, but you can do so only if you leave the formatting where it is, not move it with the values. If you try to sort a range including merged cells with the "Include formats" option ticked (which appears to be so by default), you will be warned that - although you can sort the range - you can do so only with that option deselected.

Brian Barker

#6

Thank you Brian, that was new to me. When I did my experiments I tried
several formating options but used the sort button for sorting so never saw
the sort dialog. In the past when I have used the dialog I'm sure I never
took notice of the "Include formats" selection.

Seems this is an area where the help could be improved. The explanation for
the choice is simply "Preserves the current cell formatting". It makes no
mention that any formats are incompatible with sorting or which formats
they may be. Nor does it make clear, at least to me, that the meaning of
"Preserves the current cell formatting" is that the formating moves with
the value as opposed to the formating stays with the cell.

#7

Aha! Yes, I hadn't thought of that. As you say, if you use either of the Sort buttons in the Standard toolbar, you don't get to see the Sort dialogue and don't get a chance to notice that the "Include formats" option is ticked.

Brian Barker