Is there a way to create “sort criterion presets” in LibreOffice Calc? Consider for example the following:
I use this “sorting criterion” quite a lot, but I also change it regularly to others. It is a pain to have to select all these sort keys each time I want this ordering. It would be ideal to save it as a preset.
The only way I know to achieve something of the kind is to resort to user code.
(Option “Enable natural sort” not available this way afaik.)
In principle you can arrange your re-occurring settings in dedicated cell ranges then, and choose one of these ranges to read the parameters from.
The passing of predefined parameters (settings) to UI-driven tools is a general problem.
As long as you only need a small collection of settings, you can try to work with recorded macros. .
Here is what solved it for me (pretty simple and well-hidden):
Click on Insert or Edit Pivot table → Double-click on Row Fields → Options ->Sort by
Once it’s set here it keeps the sort model.
A simple way: before sorting, select the range of cells to be sorted and create DataRange: Menu / Data / Define Range…
The DataRange “remembers” its sort criteria.
Select the sort range including column labels.
menu:Data>Define… give a name to that range.
menu:File>New>Database
[x] connect to existing database of type “Spreadsheet”
[x] Register the database.
Save the database.
Click the “Tables” section. The named database range appears as a table of a database. All the other “tables” represent the used areas of your sheets. You may hide away unwanted “tables” via Tools>Table filter…
Click the “Queries” section and add a new query in design view.
Pick your named db range as source table, double-click all the columns one by one and mark the sort fields as ascending or descending. You may also change the order of columns and you may rename the columns by means of alias names.
Save the query and the database. Close the database and forget it for now. This is a link to your spreadsheet. Nothing has been converted, copied nor imported. Your data are still in the spreadsheet document and nowhere else.
Back to Calc, hit Ctrl+Shift+F4 for the data source window. Browse your database and drag the query icon from the left pane into a blank area (separate sheet).
menu:Data>Refresh updates the sort range.
Of course, all your data belong into a true database but that’s another story.
P.S. I just noticed that the query designer is not flexible enough because the order of sort criteria is the same as the order of columns. No problem. Right-click the query icon and open it in SQL view.
The SQL command (a “formula” that returns an entire table) looks like this:
SELECT "Date", "Category", "Value", "VAT"
FROM "Sheet1"
ORDER BY "Date" DESC, "Category" ASC, "Value" DESC
Simply adjust the order of sort fields in the ORDER BY clause.
I have done a feature request here: 162824 – Add a "save/load" option for complex sorts — the define data trick is neat if you just use the same sort criteria, but not if you need to switch to several different ones…
Unfortunately, it didn’t get much traction 
Base is a database component able to connect with dozens of different database engine. Base can store queries that return any subset of rows and columns in any order of rows and columns. Spreadsheets are very poor database surrogates.
Yes, I know a database will be better. But a lot of people I work with use spreadsheets, and most of them just use Excel. In that spreadsheet, I have a handful of sortings that are significant (highlighting professor’s assignations, semester order, or courses by degree), and it would be just a nice perk to be able to “save this sort criterion” and then reuse it.
Can I use a pivot table? yes. But then modifications are just one-way, and pivot tables are not automatically refreshed (like in Excel), which is a bit of a pain.
I think this could be just the “highlight current row and column”. It has been resisted for a lot of time because “it’s not in MS Excel”, and now I think it is a killing feature for working with big sheets…
But well — I am not able to add the thing myself, so I’ll continue to screenshot the sort order dialog and reproduce it when needed. Thanks anyway for your comments!
Everybody uses databases all the time, when writing to forums like this one, when shopping on amazon.com, online banking or when using any kind of book keeping, ware housing or looking up anything on any website.
If your spreadsheet range is a list, you can treat that list like a database table. A list has one row of column labels with consistent data below the labels. Text only in one column, numbers only in another column, dates only in a third column.
I recommend giving a name to that list via Data>Define…
menu:File>New>Database…
Connect to existing database
Type: Spreadsheet
Register database: Yes
Specify the spreadsheet file
Save the database.
Now you see the named database ranges and the sheet names listed as database tables.
You may hide any sheets that do not contain lists via menu:Tools>Table Filter…
Nothing has been imported, copied, converted. All your data reside in the spreadsheet document only. The Base document shows the contents of your spreadsheet document.
Now you can add queries to that database document. A query is a “formula” returning any selection of rows and columns in any order of rows and columns from a list.
Back to Calc:
Get the data source window (Ctrl+Shift+F4)
Drag a query icon into a sheet.
The result is a new database range that is linked to the query.
Edit your list data.
Save your spreadsheet.
Put the cell cursor in the linked database range.
Call menu:Data>Refresh
The linked database range reflects the modified data in the chosen order of rows and columns.
Like a pivot table, the database range is a view derived from the original data.
With advanced filter, can be done, to make it easy.
1- Add to the toolbar, an icon with ‘Autofilter’, and another one with ‘Advanced Filter’
2- Create named ranges for the ranges with the filters, marking in the range options ‘Filter’
3- Click the ‘Autofilter’ to the data range, it is no needed but help to see where the filters are, and to clean the applied filters.
4- While in the data range, click the ‘Advanced filter’, in ‘Read filter criteria from’, select the Named range with the desired filter.
To change the filter, repeat step 4.
AdvancedFilterSample.ods (19.6 KB)
IF you store pivot tables on a separate sheet(s), you can refresh them automatically when such a pivot sheet is activated.
Sub refreshActiveSheetPivots()
for each pv in thiscomponent.currentcontroller.activesheet
pv.refresh()
next pv
End SubYour cumulation formula depends on a specific sort order. This type of spreadsheet is doomed to fail, no matter if Excel or Calc.
Thanks, everyone, for the suggestion. I will explore the options you suggested here shortly.
I am not sure about the meaning of “cumulation formula”, but I’ll check it anyway.
My problem is more similar to the OP of this thread — a big spreadsheet, mostly static, generated by a Python Panda script (the only “accumulation formula” I use is some sort of =SUBTOTAL( 109, J$2:J$465) to have subtotals for selected entries). Maybe I should prepare an example.