how do I sort rows without including first row?
Let us recap this topic.
- Select the entire range and define a database range with column headers. This will never harm. Now the sort dialog (menu:Data>Sort…) will always recognize the column headers.
The quick sort buttons may detect them if your database range includes more than one column (but may be not).
- In order to make the quick-sort buttons [A-Z], [Z-A] work with column labels make sure that the entire range has a full sequence of text cell above the data (no blanks, no numbers).
2a) Click any cell in the column to be sorted and click a quick sort button. If this does not detect the column headers, hit Ctrl+Z (Undo) and proceed with 2b)
2b) Click the cell below the first header and hit Ctrl+Shift+End in order to select all cells to be sorted, hit the Tab key until the cell cursor reaches the column to be sorted and click a quick sort button.
2c) If 2a) does not detect the headers, another work-around is to insert a blank row between data and header and hide that row. Then you may click a single cell in the column to be sorted and call dialog Data>Sort… or click any cell within the column to be sorted (but not in the separated first row) and click on a quick-sort button.
- Sorting only works reliably, predictably and easily when you store lists in a database.
Unfortunately, this setup is not recognized by the quick-sort buttons [A-Z] and [Z-A] on the main toolbar. If you prefer the quick sort buttons, insert a blank, hidden row between the header row and the first data row. Then click any cell within the data column you want to sort by and click a sort button. This will detect the “current region” of adjacent non-blank cells and sort this region by the column where the cell cursor is in.
WIthout a blank separator row, any header row is automatically detected when the first row of the current region has text only (no numbers, no blanks) and at least one value below that row has a number.
Or define the range once with Data → Define Range… and keep Options Contains column labels activated. Those named database ranges are used during Sort if the current cursor cell is contained.
Or use sheet protection (tdf#119804) … or use autofilter.
The quick-start buttons ignore this.
Oh right, but only if it is a single column range defined, I’d consider that a bug.
which is a bug
and a regression (tested with 126.96.36.199).
I suppose that “regression” is undeserved, though. I guess that it was a fallout from some tweak of heading detection, and in those old versions (including 5.0), a text-only column behaves as having a heading regardless of if it had a database range defined or not…
It has been like this since OOo 1.0. The one and only way to get consistent and convenient sorting is a database.
And I thought that this is still the same mis-behaviour as in OpenOffice Calc because I always tested this quickly with some text in a single column. Years later I learn that the quick-sort buttons do obey the database range settings.
Please have a look at this:
locale_data.ods (53.7 KB)
It is a text-only database range with 8 column labels and auto-filter buttons. Quick-sort buttons fail to do the right thing.
That’s odd, I tried with just a 2 columns by 3 rows range (without AutoFilter) and that worked. Anyway, bug that should be reported.
I click one row below the header, then click Ctrl+Shift END, then sort. This works every time. When I first tried this I added a sheet and copied my original so as not to loose the data.
Thanks that seems to work. But these drop down arrows inserted themselves. How do I remove them? See below:
They are inserted with Data>AutoFilter and this is also the place where you remove them. They belong into the header row.
Thank you! That was the ticket~
I haven’t had that problem but I would try selecting that row and pressing “Esc”.