Faq/Calc/Sorting in Calc
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Sorting in LibreOffice Calc
This article addresses advanced users.
The article not only describes the user interface but also considers how the information is stored in the document file in ODF format.
Sorting in general
Sorting numbers
Numbers are sorted by their value, including decimal numbers and numbers in scientific notation. Dates and times are also numbers. Each has an internal serial number. If you set the format of a cell to Number, you can see this internal number.
If you get unexpected results, make sure you have got indeed numbers and not text that looks like a number. Use the toggle ▸ . If the cell contains a number, its content is shown in Blue, content shown in Black is text, and cells with Green content show formula results.
You can sort numbers that are directly entered into the cell or received from a table or query of a linked database. It is more difficult to sort numbers that are formula results. That will be discussed later in a dedicated section.
Sorting text
At a first glance, sorting text seems simple: you compare words letter by letter from left to right using the alphabet a, b, c, …, x, y, z.
In reality, however, sorting text is very complex. Problems include, for example:
- The order of words depends on the locale. Even within a locale, there may be different sorting rules depending on the purpose of the sorting or orthographic reforms, e.g., a "phone book" order in German.
- Rules are needed for umlauts, accented letters, digits, and special characters.
- How are upper and lower case letters of the same letter sorted?
- How are upper and lower case variant of the same letter sorted? They are usually not sorted according to their ASCII code points.
- Special rules are required for scripts that do not use an alphabet at all, such as Chinese.
The ICO (International Components for Unicode) group [1] is working on documentations and solutions. You can try out sorting of text on the page collation [2], for example. The ICU group also provides program libraries and tables. LibreOffice uses these.
The default sorting algorithm used in Calc is named alpha-numeric. If no special algorithm is selected (such as Natural sort) and if no user defined sort list is selected, then the text is ordered according to the ICU collation rules.
This alpha-numeric sort algorithm is used, when you apply sorting with ▸ or ▸ or with the same commands from the toolbar or from an AutoFilter or in a Pivot table. The functions SORT and SORTBY use this algorithm, and if a function expects the input data to be sorted (e.g. LOOKUP function), then this order is expected.
You can sort texts that are directly entered into the cells or received from a database table or query. It is more difficult to sort texts that are formula results. That will be discussed later in a dedicated section.
Sorting areas with mixed content
If the items to be sorted are a mixture of text cells and number cells, the numbers are treated as being smaller than text. This means that cells containing numbers appear before cells containing text in ascending order.
LibreOffice does not have a specific data type for logical values; it treats zero as FALSE and all other numbers as TRUE. Only the display format makes them appear as Boolean values. You should avoid sorting ranges in which values are used as logical values. When exchanging documents with Excel, you must be very careful with logical values, as Excel has a separate data type for logical values.
If your range contains empty cells, these will always be sorted behind cells with content, regardless of whether you sort in ascending or descending order. This has been implemented in this way to be compatible with Excel.
Sorting tools
Calc provides tools for quick sorting using defaults and it provides a sort dialog with a rich set of settings. Furthermore the functions SORT and SORTBY are available since version 24.8.
Quick sorting
Sort Ascending and Sort Descending commands
How to use the Sort AscendingToDo: Add icon and Sort Descending commands is described in the help [3] and [4] and in the chapter Quick sort in the Calc Guide [5].
Sorting with Sort Ascending or Sort Descending command uses some defaults:
- Quick sorting always sorts rows, that is top-to-bottom. It assumes each row is a data record.
- When the cell cursor is placed within a block of cells, the cell range to be sorted is expanded to include the surrounding block of cells with content. If a range larger than this block of cells is selected, for example, if the entire sheet is selected, the cell range to be sorted is reduced to a block of cells with content. However, a column at the edge of the block that contains only comments, images, or shapes in otherwise empty cells is not recognized as content.
- If a named database range is marked, the settings made in a previous use of the Sort dialog for this database range are not applied. If some data has changed and you want to apply the previous sort again, use the command ▸ .
- Quick sorting always uses the global Locale setting: you have set in ▸ ▸ ▸ ▸ .
- Quick sorting is never case-sensitive. The setting Case sensitive in ▸ ▸ ▸ ▸ is not relevant for sorting.
- Quick sorting always uses the algorithm alpha-numeric.
In case you want to add the commands to a toolbar or context menu, or you want to define a short cut for them, you can use ▸ . Make sure to use the commands .uno:SortAscending and .uno:SortDescending. There exist two other commands with exact same label and icon that do not work here.
Autofilter
Despite the word Filter in its name, AutoFilter also offers tools for sorting. In case you have enabled an AutoFilter for a column in a block of cells, you can use the items Sort Ascending and Sort Descending in the AutoFilter drop-down list same as described above.
The AutoFilter tool also offers the option of sorting by color. It is the only tool with this feature.
Pivot Table
When you create a Pivot Table from your data, you get a multi-level sort. However, rows with identical sort key values are aggregated into a single row. If you are only interested in the sort, you can use the aggregate function Count for the last sort key. This allows you to identify such aggregates.
ToDo: Screenshot of Pivot Table setting and generated result.
You can use a Pivot Table to generate an out-of-place sort result similar to the options Copy sort result to: of the Sort dialog. Same as with the out-of-place sort result you need to refresh a Pivot Table manually when the source data has been changed.
A Pivot Table has the additional feature to sort using a user defined sort list. That corresponds to the option Custom sort order of the Sort dialog.
Find more about Pivot Tables in the help [6] and in chapter Using Pivot Tables in the Calc Guide book [7].
Table Data toolbar
If you have registered an odb file, your data source is listed in the Data Source window. Open the Data Source window by ▸ or Ctrl + ⇧ Shift + F44, for example. Select a table or query in the Database Explorer on the left. The content will then be shown as table on the right. You will see icons for sorting in the Table Data toolbar. The icons might look the same as for the sorting tools mentioned above, but the underlying commands are different. They are .uno:OrderCrit, .uno:Sortup and .uno:SortDown. The command .uno:OrderCrit provides simple settings for a multi-level sorting, nothing else. These commands do not belong to Calc but are general tools for forms. You are more flexible if you link or copy the unsorted table to your spreadsheet and then use the Sort… dialog.
Data Provider
The Data Provider feature ▸ promises to be able to import CSV, HTML and XML data. A Google Summer of Code project [8] had added numerous transformations that can be applied to the data during import, including Sort Columns. The feature is described in the help [9] and in chapter 15 in the Calc Guide book [10]. Unfortunately it has several open issues tdf#120219, especially bug tdf#169049 about deficits in sorting CSV data (state Nov 2025).
You should consider to use ▸ , define a Database Range for the imported data in case such is not automatically generated, and then use the Sort… dialog.
Sort dialog
Specify the cell range to be sorted
The Sort dialog works on a selected range of cells. The selection must be one rectangular area of cells. For selecting a range do one of:
- Use ▸ . Read section Database ranges for more details.
- Open section Database ranges in the Navigator and double-click the name of the range to be sorted.
- Set the cell cursor into the column you want to use as sort key and open the Sort dialog. Calc tries to autodetect the to be sorted rectangle of cells. The range affected by sorting will be marked.
- Set the cell cursor into the column you want to use as sort key and press Ctrl + A once. Calc marks a rectangular block of cells surrounded by empty cells.
- Select a named range from the Name Box drop-down list.
- Enter a range address manually into the Name Box field.
- Select the range manually using mouse or keybord and a suitable selection mode, read section Range of contiguous cells in Chapter 1 of the Calc Guide[11]
In case you select only a part of a continuous cell range, you get a warning, as this is in most cases an error. ToDo: Add screenshot of warning
If you want to include images, shapes or comments in otherwise empty cells, make sure, that the selected range includes these cells. In addition go to tab Options in the Sort dialog and check the option Include boundary column(s) containing only comments and Include boundary column(s) containing only images. The latter affects shapes as well. In case your sort direction is Left to right the options labels have the term row(s) instead of column(s). If you do not check these options, the to be sorted area is reduced to the rectangle of non-empty cells.
Examine the sort option Headers on tab Sort criteria of the Sort dialog. Especially if the to be sorted area contains only text, the auto-detection might not work. Adjust the setting if necessary.
Specify sort keys
In the Sort dialog, you can specify multi-level sorting. This means that the records are first sorted according to the primary key Sort key 1. The sort result may contain sections with identical values in the Sort key 1 field. Each of these sections is then sorted according to the secondary key Sort Key 2, and so on.
The Sort dialog initially displays three levels. However, when Sort Key 3 is used, the dialog is expanded and provides additional levels.
The Ascending and Descending radio buttons specify separately for each level whether to sort up or down. The other settings of the Sort dialog are applied to all levels.
Multi-level sorting is also provided by the Pivot Table tool and the SORTBY function.
Specify sort direction
Database tables are usually arranged so that each row contains one data record. Sorting a database table means reordering the rows. This is also the default setting for sorting in Calc. However, in the Sort dialog, you can specify that columns should be rearranged. Select the Left to right (sort columns) option in part Direction in Sort criteria tab. When this option is selected, some labels in the dialog will change accordingly, e.g. the label Column for the sort keys changes to Row.
When you sort a named database range, the sort direction is stored as the table:orientation attribute of the <table:database-range> element in the file markup and is retained when the file is loaded. The ODF standard also supports left-to-right direction for filtering. However, left-to-right direction is only implemented for sorting in LibreOffice. Therefore, you can only specify the direction in the Sort dialog, it is not included in the initial definition of a database range.
Left-to-right sort direction is also provided by the Pivot Table tool and the SORT function.
Specify locale
Sorting always uses a locale. Sorting does neither use the language set in the text or cell style nor in the number format.
Sorting uses as default the global locale you have set in ▸ ▸ ▸ ▸ ▸ . In that case the drop-down list Locale on the Options page in the Sort dialog shows an item default - your locale. If you want to use a locale associated with the sorting of a database range, select it in that drop-down list.
The global locale has the disadvantage, that it is only stored in your user profile in the file registrymodifications.xcu. If you exchange documents with persons, who use a different locale than you, then you should explicitly set the locale for sorting even if you select the same locale as your global locale. An explicitly set locale is stored in the sorting information in the document file and will be available when the document is reloaded.
If you use a user defined sort-list, then the global locale is used regardless of the choice in the Sort dialog. The file format would allow to specify the locale in that case too, but it is not implemented yet in LibreOffice.
Specify output position
The Sort dialogs provides the feature to not sort the source data range itself but to write the sort result to a different place. Go to the Options tab in the Sort dialog and select the option Copy sort results to:. Now drop-down-list and text field are enabled.
The drop-down-list contains range names with global scope and names of database ranges. When you select a name there, then the address of the top-left cell of the range is automatically entered into the text field. Alternatively, you can enter the address of the top-left cell of the desired target range directly into the text field without first defining a name. You need not enter an absolute address, it will be later automatically converted to an absolute address. In any case, the information in the file markup contains only the address of the top-left cell of the target area.
This feature is so implemented, that first the original data are copied to the target range and then the target range is sorted. You can see these two steps, when you look into the list of Undo steps. You will see Copy and then Sort.
In case you original data contains formulas, the formulas are copied, not the formula results. Thus it might happen, that the formulas no longer work in the target range, because a cell address in a formula parameter points to a wrong cell, see section Sorting formula / Copy result to for details.
Include Formats
- When not to use? Example table template with alternating rows
- Where is it set?
- Associated ODF markup
SORT and SORTBY function
The SORT function is described in the help [12] and in the Wiki Calc_Functions/SORT. The SORTBY function is described in the help [13] and in the Wiki Documentation/Calc_Functions/SORTBY. In addition, there are videos, presentation and example files from a workshop at the LibreOffice Conference 2024 in Luxenburg. Links are in User:Regina/How_to_use_SORT_SORTBY_FILTER_functions.
Thus here we focus on the differences between using the sorting tools and using these functions.
Sorting algorithm
Sorting tools: The default sorting algorithm is alpha-numeric. Several special algorithm are available: case-sensitive, natural-sort, user defined sort list, locale dependent special sort algorithm, sorting by color.
Functions: The functions SORT and SORTBY sort always case insensitive and always with algorithm alpha-numeric.
Push vs pull paradigm
Sorting tools: Sorting pushes the sort result to the target range. The target range has no information about the source range. Source and target range can be the same, that is, in-place sorting is possible.
Functions: The functions pull the content from the source range. The source range does not "know", that its values are used. The target range shows the sorting result. However, the cells do not contain the result directly but the formula that generates the sorted result. Thus the target range "knows" its source range. Source and target range are always different.
Reaction on changes in the source
When a database range is linked to an external database, its content and size might change. To allow size changes, the option Insert or delete cells needs to be selected in the Define Database Range dialog. That is the default setting. The setting is stored as attribute table:on-update-keep-size="false" of the <table:database-range> element in the file.
If you want that the size of a database range is changed when you manually insert or delete rows or columns, then the option Expand references when new columns/row are inserted needs to be selected. That is the default setting. That is a global option in ▸ ▸ ▸ . Notice, this applies only to inserting entire rows or columns, not to inserting single cells.
Sorting tools: Changes in the content or size of the data source are not immediately reflected in the sort result. You need to use a Refresh command or allow refresh when loading the file, respectively. When you do not sort "in-place" but output the sort result to a different cell area, then the refreshed sort result will overwrite cell content beyond the old area without warning, when the new sort result is larger. That is different from Excel, which has a Spill-error.
Functions: Changes in the content of the data source are immediately reflected in the target range. LibreOffice takes care, that functions are recalculated, when referenced cells change the content. The size of the array function is not automatically adapted. You need to manually adapt it e.g. by dragging the small square at the bottom-right corner of the area to the new size. That is different from the "dynamic table" feature in Excel. The request for a similar feature is in tdf#127808.
Special sorting algorithm in Calc
Sorting text with embedded numbers
Texts might contain numbers, e.g. ISBN number, IBAN and BIC in money transfer, house number and postal code in an address, article numbers, or IP adresses. Or you might have got foreign text that would be a number in content, but the entry is a text because it includes a unit, such as 34℃ or 17mg.
Calc provides three algorithm for treating numbers embedded in text. These methods correspond to the attribute values alpha-numeric, double and integer of the table:embedded-number-behavior attribute of the <table:sort> element [14] in ODF file format. The method corresponding to integer is available since LibreOffice 26.2. These three algorithm are also available in Basic macros.
The settings to determine the sort algorithm are on page Options of the Sort dialog, e.g. via ▸ ▸ .
ToDo: Enter screenshot.
If the option Recognize numbers within strings is not checked, the algorithm alpha-numeric is used. If it is checked, then the further options are enabled. Option Use decimal numbers as a whole corresponds to double and option Split in integer and fraction corresponds to integer in ODF file format.
Algorithm Alpha-numeric
If the option Recognize numbers within strings is not checked and thus the algorithm alpha-numeric is used, digits contained in a text are treated as normal characters.
Example for locale en-US
The collation rules determine "A" < "B" < … ; < "Z" and "0" < "1" < … < "9" and digit < letter. Comparing is done from left to right. If the characters are equal at the current position, the next character is used in the comparison until there is a difference.
Thus the sequence
B32, A4, B10Z, A14, B100, B3K, B3
will be sorted to
A14, A4, B100, B10Z, B3, B32, B3K,
Natural Sort in general
If you want that numbers embedded in text are treated with their values, you need to check the option Recognize numbers within strings.
The algorithms split each to be sorted text item into text parts and number parts. The text parts are ordered by the same rules as would be uses if Recognize numbers within strings was not checked, the number parts are ordered by the value of the number. This kind of order is usually called natural sort.
When you check the option Recognize numbers within strings, the two radio buttons Use decimal numbers as a whole and Split in integer and fractions become active. Both perform a Natural Sort in principle. But they differ how decimal numbers are handled. ToDo: Labels are pending, see tdf#168899
Caution, the natural sort algorithms are not available in xlsx file format.
Use decimal numbers as a whole
ToDo: Label is pending
Example for locale en-US with dot as decimal separator.
The sequence of text items
"10.48kg", "17.6kg", "103.5kg", "1.9kg"
is sorted to
"1.9kg", "10.48kg", "17.6kg", "103.5kg"
The comparison uses the values 1.9, 10.48. 17.6, and 103.5.
The same example does not work in a locale, where the dot is not a decimal separator. For example, in locale de-DE the decimal separator is a comma. So be careful that the decimal separator corresponds to the given locale.
The algorithm implemented in LibreOffice detects numbers in scentific notation as number. Example (again in locale en-US): The sequence of text items
"1.3E+2kg", "13kg" and "1.3E-1kg"
will be sorted to
"1.3E-1kg", "13kg", "1.3E+2kg"
That is because the number values are 1.3E+2 = 130 and 1.3E-1 = 0.13 and that results in value order 0.13 < 13 < 130.
The algorithm in LibreOffice does not detect negative numbers. In the text "-1.3℃" the minus sign will be handled as character "-", followed by the number 1.3, followed by the character degree celsius sign "℃".
This algorithm was used when the option Enable natural sort was checked in the Sort dialog in LibreOffice versions prior to version 26.2.
Split in integer and fraction
ToDo: Label is pending
In this algorithm any decimal separator is treated as ordinary character, regardless of the given locale.
Example with decimal numbers and a dot as decimal separator:
The sequence of text items
"K2.5", "K10", "K1.104", "K1.2", "K2.307"
will be sorted to
"K1.2", "K1.104", "K2.5", "K2.307", "K10"
Reasoning: Because the decimal separator is treated as normal character, the to be sorted items are split into these parts:
| K2.5 | ⇒ | K | 2 | . | 5 | ||||
| K10 | ⇒ | K | 10 | ||||||
| K1.104 | ⇒ | K | 1 | . | 104 | ||||
| K1.2 | ⇒ | K | 1 | . | 2 | ||||
| K2.307 | ⇒ | K | 2 | . | 307 |
"K1.2" and "K1.104" have the common part "K1.", thus the value order 2 < 104 of the number parts "2" and "104" determines their order. Similar for comparison of "K2.5" with "K2.307". The value order 5 < 307 determines their order. So we get
| K1.2 | ⇒ | K | 1 | . | 2 | |||
| K1.104 | ⇒ | K | 1 | . | 104 | |||
| K2.5 | ⇒ | K | 2 | . | 5 | |||
| K2.307 | ⇒ | K | 2 | . | 307 | |||
| K10 | ⇒ | K | 10 |
This kind of sorting is useful, when you have got texts with embedded multilevel numbers, and the text uses the decimal separator of your locale as delimiter although it is not a decimal number in content. Examples are article ids such as M12.345F or IP addresses such as 192.0.1.62.
This kind of sorting was not available in LibreOffice versions prior to 26.2.
Case sensitivity
Sorting can be done case-sensitive or case insensitive. The setting is on the Options page of the Sort dialog. The setting is not available when using the quick sort commands. Those sort always case insensitive.
The similar setting Case sensitive in ▸ ▸ ▸ ▸ is irrelevant for sorting, but effects search tools in menu and comparison operators in formulas.
The setting case sensitive on the Options page has two meanings, depending on the locale.
Alphabet based
Some languages use an uppercase and a lowercase variant of the same letter, such as lower case d (U+64, LATIN SMALL LETTER D) and upper case D (U+44, LATIN CAPITAL LETTER D) or lower case σ (U+3C3, GREEK SMALL LETTER SIGMA) and uppercase Σ (U+3A3, GREEK CAPITAL LETTER SIGMA).
When the option Case sensitive is checked, then the sort algorithms treat uppercase and lowercase variant as distinct letters. Thereby the lowercase variants are placed before the uppercase variants. Example: The list of German strings:
alter | Blau | Arm | biss | Floh | arm | floh | Alter | blau | Biss
will be sorted case-sensitive to
alter < Alter < arm < Arm < biss < Biss < blau < Blau < floh < Floh
and will be sorted case-insensitive to
alter = Alter < Arm = arm < biss = Biss < Blau = blau < Floh = floh
Asian locales
For Asian locales special handling applies, see note in the help [15]
Example: List of Japanese strings きゅう | キユウ | きゆう | キュウ
When the option case-sensitive is not checked, the list items of the example are treated as being equal
きゅう = キユウ = きゆう = キュウ
When the option case-sensitive is checked, then the list items will be ordered to
きゅう = キュウ < キユウ = きゆう
The settings in ▸ ▸ ▸ are not relevant for sorting.
Sort lists
You are probably already familiar with sort lists as an aid to create series. For example, you write "Monday" in a cell and drag the handle in the bottom-right corner of the cell to create a series with the days of the week. Perhaps you have already added an own list for a series you often need.
These sort lists can also be used to sort data records.
For example, you have data with a column of weekday names and want the data to be sorted by weekday names. Do this: Define a database range for your data. Select this database range and open the Sort dialog. On tab Sort Criteria set the Sort key 1 to the column of the weekday names. Go to Options tab and select the option Custom sort order. Now the drop-down list is enabled and you can select the item that shows the series of weekdays.
If the column contains entries, that are not contained in the sort list, they are placed after the items of the sort list and ordered alpha-numerically using the global locale.
The items of the sort list are only recognized, if they are the only content of the cell. Sort list items contained inside other texts are not recognized. If it does not work for you, make sure your cell content has no leading or trailing spaces.
Sorting by using sort lists is also possible in Pivot Tables. Open the sort tool in the header field of the Pivot Table and click on Custom Sort. A list with the installed sort lists opens and you can select one. ToDo: insert screenshot
LibreOffice provides a set of default sort lists that contain weekday and month names, both as abbreviation and as full name. How to define and add own lists is described in section "Defining a fill series" in chapter 2 of the Calc Guide book [16].
At a first glance, this works fine. But you need to consider these properties:
- The selection of a locale on the Options tab of the Sort dialog is ignored for sorting by a sort list in the current version of LibreOffice (state 26.2). Always the globale locale is used.
- The sort list itself is not included in the file, but is only stored on your PC. This means that applications on other computers cannot reproduce the sort order, even if they fully support the ODF standard.
- The sort list used is referenced in the file via its index in your set of user lists. Therefore, if you change your set of custom sort lists, the index may become invalid, and updating the sort order may result in an incorrect sequence.
- The sort lists initially provided by LibreOffice depend on the locale you have set in ▸ ▸ ▸ . Someone with a German locale will have different predefined sort lists than someone with an English locale, for example, even if both are using the same version of LibreOffice.
- If you define an own sort list, this list is only stored in the file registrymodifications.xcu in your user profile. Someone else has no access to this sort list. The list is contained in an element
<item oor:path="/org.openoffice.Office.Calc/SortList">element. If such element exists, you cannot get the predefined sort lists of a different locale by changing the locale in ▸ .
Locale dependent sort algorithm
The directory i18npool/source/localedata/data/ [17] in the source contains files that describe the settings that apply to the respective locale. Such a file contains an <LC_COLLATION> element with the name of the usual sorting algorithms. In most cases, it only contains <Collator default="true" unoid="alphanumeric" />. However, some locales have several such <Collator> elements.
A search in this directory returns the files
de_DE.xml
ja_JP.xml
ko_KR.xml
lb_LU.xml
nds_DE.xml
zh_CN.xml
zh_HK.xml
zh_TW.xml
(I'm not sure, that I have found all.)
To set the algorithm, select the language in the Locale field in the Options tab of the Sort dialog, even if it is the same language as the one specified in the Default - … entry. This enables the Options drop-down list, allowing you to specify the desired algorithm.
If you do not know how the algorithms differ, please ask on Ask [18] in the community corresponding to the language.
Sorting by color
Sorting by color is only available from an AutoFilter.
Open the drop-down list of the AutoFilter of the column you want to use as sort key. Select . You get a list of the background and font colors currently used in that column of the cell range. It does not matter whether the color was assigned by conditional formatting, a cell style or directly.
Select the color, that you want to be first in the sorted range. The other rows are sorted ascending by their color value. Thereby the color value #rrggbb is interpreted as unsigned hexadecimal integer. For example, color #00ff00 (green) is interpreted as integer 65280 and #0000ff (blue) is interpreted as integer 255. Thus cells with green font come after cells with blue font. You can see the hexadecimal value of a color in the Color Picker tool.
The chosen color sorting settings are not written to file markup and not associated with a database range. The sort is executed and your choice is immediately forgotten then. Thus in case values or colors in the database range were changed, you cannot repeat the sorting by Refresh range. Only the fact, that AutoFilters are enabled, is stored in the file. That is the table:display-filter-buttons attribute of the <table:database-range> element.
The current implementation of sorting by color is not compatible to Excel. The compatibility problem is tracked in tdf#168043.
There is no API for sorting by color. You would have to implement the entire process yourself to perform sorting by color using a macro.
Sorting formula results
This is a shortened example. In reality such tables are likely larger.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Birthday | Month | Day | Name | |
| 2 | Mia | 1989-03-21 | 3 | 21 | Mia | |
| 3 | Evelyn | 1986-03-28 | 3 | 28 | Evelyn | |
| 4 | Noah | 2019-03-16 | 3 | 16 | Noah | |
| 5 | Alice | 2000-04-23 | 4 | 23 | Alice | |
| 6 | Arthur | 1967-11-12 | 11 | 12 | Arthur | |
| 7 | Isabella | 1975-09-01 | 9 | 1 | Isabella | |
| 8 | Terry | 1972-02-16 | 2 | 16 | Terry |
The original data are in cell range A1:B8. You want a sorted list of birthdays in the year. You cannot use the column B directly, because that would result in a list ordered by age. So you have calculated a help-table D1:F8, that contains the month in column D, the day in column E and the name in column F. Sorting that help-table on first level by Month and on second level by Day gives the desired result.
Below is the same table showing the used formulas.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Birthday | Month | Day | Name | |
| 2 | Mia | 1989-03-21 | = MONTH(B2) | = DAY(B2) | = A2 | |
| 3 | Evelyn | 1986-03-28 | = MONTH(B3) | = DAY(B3) | = A3 | |
| 4 | Noah | 2019-03-16 | = MONTH(B4) | = DAY(B4) | = A4 | |
| 5 | Alice | 2000-04-23 | = MONTH(B5) | = DAY(B5) | = A5 | |
| 6 | Arthur | 1967-11-12 | = MONTH(B6) | = DAY(B6) | = A6 | |
| 7 | Isabella | 1975-09-01 | = MONTH(B7) | = DAY(B7) | = A7 | |
| 8 | Terry | 1972-02-16 | = MONTH(B8) | = DAY(B8) | = A8 |
Update references when sorting
Now we define a database range D1:F8, select it and sort on first level by Month and on second level by Day.
We get
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Birthday | Month | Day | Name | |
| 2 | Mia | 1989-03-21 | 2 | 16 | Terry | |
| 3 | Evelyn | 1986-03-16 | 3 | 16 | Noah | |
| ⁞ | ⁞ | ⁞ | ⁞ | ⁞ | ⁞ |
Terry has the first birthday in the year and thus becomes the first record. It is the desired result .
When you inspect the formulas you see
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Birthday | Month | Day | Name | |
| 2 | Mia | 1989-03-21 | = MONTH(B8) | = DAY(B8) | = A8 | |
| 3 | Evelyn | 1986-03-16 | = MONTH(B4) | = DAY(B4) | = A4 | |
| ⁞ | ⁞ | ⁞ | ⁞ | ⁞ | ⁞ |
The references in the formulas were so adapted, that they still point to the correct record in the original table in range A1:B8.
Actually, when sorting, data records are swapped as they are. Adjusting references is therefore something special. It is generated by the global setting Update references when sorting range of cells in ▸ ▸ ▸ . This option is selected by default.
Copy result to
The above example has used relative addresses when creating the help-table to be able to drag fill the columns D, E and F.
Now let see, what happens, when you do not sort "in-place" but use the feature Copy result to:.
When you set the target to cell H1, then range D1:F8 is first copied to H1:J8. Thereby the formulas are adapted such that
= MONTH(B2) becomes = MONTH(F2)
= DAY(B2) becomes = DAY(F2)
= A2 becomes = E2
That means, that the parameters no longer reference the Birthday and Name column. Thus using relative addresses will not work.
You could use absolute addresses e.g. = MONTH($B$2). The update of references described above would still work. But you would no longer be able to drag fill the columns to generate the help-table. You must be careful which part of the address needs to be absolute. Here you want, that always the birthday is taken from column B and the name from column A. But the row numbers need to be relative, so that you can still drag fill the columns. Thus you have to use
= MONTH($B2)
= DAY($B2)
= $A2
Database ranges
Sorting in Calc is always connected to a database range. If you simply mark a rectangle area of cells and sort it, an anonymous database range is created. Each sheet can only have one anonymous database range. So if you select and sort another range on the sheet, the previous anonymous database range is forgotten.
You cannot access an anonymous database range using a Basic macro. If you want to work with macros, you must name the cell ranges to be processed. In the user interface, this can be done with ▸ . But you can also use macros to create and manage named database ranges using the service css.sheet.DatabaseRanges.
Such named database range definition is stored in the document file and will be available after reloading the document. Sort, filter and AutoFilter settings are then associated with the named database range and stored in the document file as well.
Enable the option Contains column labels in the Define Database Range dialog to specify that the database range has a Header. This information is considered, when you sort with the Sort dialog or when you enable AutoFilter for this database range. It is not considered when you sort with the "quick" sort commands Sort Ascending or Sort Descending of the toolbar. You could enable the option Contains totals row in the Define Database Range dialog, but excluding it from sorting is not yet implemented.
You can link a database range to an external database. You need to create a .odb file for the external database and register it in your LibreOffice. You can then view Data Sources (e.g. via Ctrl + ⇧ Shift + F4) and drag a query or table from the Data Explorer on the left onto a sheet. This will automatically generate a named database range Import1, and Import2 and so one if you use several imports. Find more about using an external database in chapter 7 Linking to Databases in the Base Guide [19]. The guide is not yet updated to current LO versions but the discussed principles are still valid. Chapter 12 Linking Data in the Calc Guide book [20] describes this method as well.
A database range is stored as <table:database-range> element in file markup. In case of an anonymous database range, LibreOffice uses a name like "__Anonymous_Sheet_DB__0". If you have defined a named database range, that name is used in the file. Find the specification of element <table:database-range> in section 9.4.15, part 3 ODF 1.4 [21].
Further reading
- Chapter 15 Calc as a Database in Calc Guide
https://books.libreoffice.org/en/CG252/CG25215-CalcAsADatabase.html - Chapter 9.4 Spreadsheet Document Content in part 3 of the ODF 1.4 standard
https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part3-schema.html#__RefHeading__1415640_253892949