What does this mean
Ranges containing merged cells can only be sorted without format.
Hello,
it means what it says: You cannot sort a range, if the range contains a merged cell and option Data -> Sort -> Tab: Options -> Category: Sort Options -> Option: [x] Include Formats is set. The problem: It is set by default, thus you need it unset, if you want to sort a range containing merged cells.
Background Sorting a range without a merged cells, will keep the format of the cell’s value (which is a bit of an incorrect wording, since “format” is a property of the cell and not a property of the value). The format will be taken from the original cell of the value to the cell of its new place after the sort (In that sense: It looks like the format is taken with the value to its new place). This is not possible for merged cells, since property “being a merged cell” is part of the format (“moving” the format would require to “unmerge” the source cell at the starting position and to merge the cells at the new position of an value, which would the sheet pose a risk to data loss).
Just make a small experiment and add 10 unsorted numbers into a column, make one value bold and sort the list. In a second step, make the same list above, merge the bold faced cell with its neighbor, unset the option above and sort again. You will see, what I mean (the format sticks with the merged cell in the latter case, regardless of the value within the merged cell).
Hope that helps.
If this answers your question, please click the check mark (
) next to the answer.
I tried with the option ‘Include Formats’ turned off, and sorting the sheet doesn’t move the merged cells. Worse than that, several values disappear because they end up under merged cells. The entire sheet gets screwed up. This option is useless for sorting sheets with merged cells. Groups of rows, defined by one or more merged cells, should stay grouped!
As an alternative, select all cells and press Unmerge button. The button description is read ‘Merge and center or unmerge cells …’ and is located on the standard toolbar.