Create and Maintain Named and Database Ranges in LibreOffice Calc

Use a named range for readable formulas and a database range for a rectangular set of records with retained data-operation settings. They solve different maintenance problems: a name can simplify a formula without defining the table you intend to sort.

Before you start

Use a disposable ODS workbook with sheets named Budget, Review, and Stock. These standard-menu instructions follow Calc Guide 26.2 (February 2026) and the online Help labeled 26.8, consulted 19 October 2026. The examples and arithmetic were independently checked; no native Calc run or screenshots are claimed. Save/reopen checks below are checks for you to perform.

1. Give a formula range an explicit scope

On Budget, enter this table in A1:B4:

Cost	Amount
Materials	120
Delivery	35
Packaging	15

1. Select B2:B4 and choose Sheet → Named Ranges and Expressions → Define.
2. Set Name to PlanCosts, the range to $Budget.$B$2:$B$4, and Scope to Document (Global). Click Add.
3. On Review, enter =SUM(PlanCosts) in B2. Expected: 170.
4. Back on Budget, define LocalCosts for the same range, but select Budget as its scope. Enter =SUM(LocalCosts) in D2 on Budget. Expected: 170.
5. Open Sheet → Named Ranges and Expressions → Manage. Check both names, absolute addresses, and scopes. The document-wide name is the one to use for this workbook’s cross-sheet report; the sheet-local name is intentionally confined to Budget. Avoid duplicate names with different scopes until you need that complexity.

The Define Names help describes the scope and reference controls. Use simple identifiers such as these, without spaces or cell-address-like names.

2. Extend the name deliberately

Enter Handling in A5 and 10 in B5. Inspect PlanCosts in Manage: a reference ending at B4 does not include B5. For that reference, Review.B2 remains 170. Edit its end address to $B$5, confirm the dialog, and verify 180. LocalCosts still points at B2:B4 and should remain 170.

As a change test, change B3 from 35 to 40: the two sums should now be 185 and 175. Restore B3 to 35: 180 and 170. This proves which cells each name includes; a plausible total alone does not.

If your edition adjusts a range automatically, inspect its address first rather than assuming either expansion or non-expansion. To recover the exercise, set the references explicitly to the stated addresses and retest.

3. Define a separate record block

On Stock, enter A1:C4:

SKU	Depot	Units
K11	West	8
K12	East	5
K13	West	12

Select A1:C4. Choose Data → Define Range, name it StockRecords, and inspect $Stock.$A$1:$C$4. Expand Options; enable Contains column labels, leave Contains totals row off, then Add → OK. This local fixture has no linked external database.

Use Data → Select Range to select StockRecords. Choose Data → Sort, with column labels and top-to-bottom direction, and sort Units descending. Expect complete rows in SKU order K13, K11, K12. Reopen Define Range and inspect the range’s Operations information for Sort. Database ranges retain operation settings; they are not simply alternative formula names. These controls and the Modify workflow are documented in Calc Guide 26.2, Chapter 15.

4. Grow and recheck the record block

Enter a fourth record in row 5: K14, East, 20. Reopen Define Range, select StockRecords, and inspect its Range field. Set it explicitly to $Stock.$A$1:$C$5; click Modify → OK. Select the range again and repeat the Units-descending sort. Expected SKU order: K14, K13, K11, K12; expected units total: 45.

Do not use Insert or delete cells as proof of automatic growth for manually typed rows. The guide describes that option in connection with linked external data. Check the address and operation settings whenever a table grows. Define Range help identifies the available options.

Recovery and final check

• A missing name or unexpected total: inspect spelling, scope, and the reference before changing the formula.
• A SKU paired with the wrong depot or units: undo the sort immediately, select the entire block, and repeat. A total of 45 cannot detect separated record fields.
• Save, close, and reopen the ODS. Recheck PlanCosts = 180, LocalCosts = 170, both scopes, StockRecords ending at C5, and the retained Sort information.
• Keep this as ODS. CSV cannot preserve this workbook’s multiple sheets and range definitions. This exercise does not test structured references or cross-format portability.

For a practical use of a formula name, see a drop-down list from another sheet. Review SUM for the total calculation.