How to sort with Calc with a frozen first row and column

[JimArtsnova]

I have a sorting question.

With Calc it is possible to Freeze the first row of the spreadsheet and then when using the Sort function, specifying that the first row contains column labels - in which case that row is left alone and remains as row 1.

Having a spreadsheet with a lot of columns, I would like to freeze the first column so that its contents remains visible at all times. No the first column is not a label - it’s just a key field for that specific row. But this creates a real problem if I want to sort my data because sort then ignores the first column of data (automatically assuming that it is a label) when doing the sort and consequently screws up every row in the spreadsheet. Given that I don’t want to be constantly having to remember to freeze and unfreeze the first column every single time I do a sort, is there a way around this Calc shortcoming? Or is there some setting I just don’t know about?

Thanks.

Update. Using Libreoffice 6.3.22 on Windows 7. Note that I created another spreadsheet from scratch, froze the first row and column and this spreadsheet DOES sort correctly. It may be worth noting that the failing spreadsheet is an Excel export from a project mgmt tool (Wrike).

[anon73440385]

I cannot confirm what you describe in LibreOffice 6.3.4.2 / 6.2.8.2 / 5.4.7.2 - First (frozen) column gets sorted as well as other columns as long as it is selected as well (but that’s taken for granted).

Thus - the following questions

• Which operating system do you use
• Which version of LibreOffice
• Did you try to sort while running in Safe Mode (Help ->Restart in Safe Mode)?

Please do not use Add Answer but edit your original question to enhance the details of your question. Thanks in advance …

[JohnEvigt]

I was trying to sort, but first line, even frozen, was also being sorted.

Did this and worked:

1. Freezed first line (didn’t tested if it works without freezing).

2. Clicked on “AutoFilter”, on the right side of “Sort” icons (see image bellow).

3. Calc will ask if I want to add a Header, click “Yes” and done.

Hope it helps.

[Unis_Torvalds]

Not available in quick sort, but when you use the regular sort function (either by shortcut or in >Data>Sort…) there is the option: “Range contains column labels”

Check this and your first row won’t move.

[Villeroy]

Frozen rows have no effect on sorting.

Connect a Base document to your spreadsheet and define queries with sort orders. Base can sort cell ranges properly if they are shaped like database tables. One header row, numbers below numbers, text below text, dates below dates. Way better: Store all your data in a true database.

With Calc only, you should define your lists as database ranges (menu:Data>Define…). Under “Options” you can check “Contains column labels”. Now it should be possible to select the entire database range and sort it with or without header row as specified in the database range settings. You can select the database range via Data>Select… or by entering the range name into the name box left of the formula bar. When you call the sort dialog with a single cell selection, the defined databasee range will be selected before the dialog pops up.

When you click the handy quick sort buttons [A-Z] and [Z-A], a single cell selection expands to the defined database range BUT option “contains header row” is ignored. The appication tries to guess if there is any header row or not. As far as I can tell, any fiisrt row of consecutive text constants is treated as a header row UNLESS the subsequent rows are text only too. If everything is text, there is no header row.