LibreOffice Calc

Folding rows/columns

Select rows or columns.

Press F12 (or use Data -> Group and Outline -> Group)

Fix header row etc

Rows and columns can be fixed with View -> Freeze Rows and Columns (which typically used to display a header row).

Formatting dates, numbers and/or text

Menu Format -> Number Format can quickly choose among some formats but is not very flexible to display a number, for example, with three decimal places.

Menu Format -> Cells then Number tab allows to format numbers with more flexibility.

Set Date acceptance patterns to something like Y-M-D (Tools -> Options | Language Settings -> Languages), but also ctrl+1 to format dates in cells.

Apparently, it is not possible to set a default format for dates!

Turning off header and footer

Format -> Page -> Header / Footer

Turning off spell checking

Menu Tools -> Langauge -> For All Text, then None.

Hiding grid lines

Tools -> Options, Calc -> View, Grid lines (in the top right corner).

Merging cells

Select cells to be merged, right click mouse, choose Merge Cells.

Formulas

Referring to other named ranges in other documents:

='file:///path/to/document.ods'#range_name

Referring to range in other documents

='file:///path/to/document.ods'#$sheet_name.b5:c9

Enable large spreadsheets

By default, Calc has a limit of 1K columns (AMJ) and 1M rows.

This limit can be lifted in the Menu Tools -> Options -> Libre Office Calc -> Defaults: Enable very large spreadsheets: 16K columns (XFD) and 16M rows.

After changing this option, it seems that Calc has some problems opening spreadsheets that were created without this option.

This option is stored in registrymodifications.xcu (under the user profile) in the value JumboSheets.

Macro to sort a range

Sub SortRange

    dim range            as Object ' ScCellRangeObj
    dim sortDesc(0 to 8) as Object
    dim sortCols(0 to 2) as New com.sun.star.table.TableSortField
    
    range    = ThisComponent.Sheets.getByName("SheetName").getCellRangeByName("RangeName")
    sortDesc = range.CreateSortDescriptor
    
    sortCols(0).Field =  5 ' 0-based 
    sortCols(1).Field =  3
    sortCols(2).Field =  2
    
    sortCols(0).IsAscending = True
    sortCols(1).IsAscending = True
    sortCols(2).IsAscending = True
    
    sortDesc(1).Value = False     ' No header
    sortDesc(3).Value = sortCols
    
    range.Sort(sortDesc)
    
End Sub