Contact
12 programs
Explore the site

Gnumeric · Names and references · Beginner

Create and Maintain Named Ranges in Gnumeric

Define workbook and sheet names in Gnumeric, audit their scope, extend a named range and check a reversible rate change.

Documentation-backed procedure; independent fixture checks; native verification pending · Editorial standards

Use names for a small fee model, then deliberately change their definitions. A readable formula is useful only if its names still point to the intended cells.

Practice workbook

Use a new workbook and its first sheet, named Sheet1. Enter this synthetic order list in A1:B4:

Item Net
Labels 120
Folders 80
Envelopes 50

Enter Fee rate in D1 and 0.075 in D2. Leave row 5 empty for the growth test. Enter the following audit formulas outside the data block:

Cell Formula Expected result
F2 =SUM(B2:B4) 250
F3 =F2*D2 18.75
F4 =F2+F3 268.75

These direct-reference calculations are the control against which to compare the named model.

Define two workbook names

The Define Names documentation describes the following editor:

  1. Select B2:B4. Open Edit → Modify → Names.
  2. Use the plus sign on the workbook row, select the new entry and replace its placeholder name with OrderNet. Press Enter. Inspect its definition: it should identify Sheet1!$B$2:$B$4.
  3. Create FeeRate under the workbook too. Set its definition to Sheet1!$D$2; press Enter and close the dialog.
  4. Enter =SUM(OrderNet) in H2, =H2*FeeRate in H3 and =H2+H3 in H4.

Expected H2:H4: 250, 18.75, 268.75. Enter =H4-F4 in J4; expect 0. The reference contains dollar signs so a name does not shift according to the formula cell. Avoid names such as D7, which resemble cell addresses.

Change, extend and recover

Change D2 to 0.08. H3 should become 20 and H4 270, matching F3:F4. Restore D2 to 0.075; H4 returns to 268.75. This tests the rate cell without changing either formula.

Now enter Tape and 40 in A5:B5. A name ending at row 4 excludes row 5: H2 remains 250. Reopen Names and change OrderNet’s definition to Sheet1!$B$2:$B$5. The new expected results are 290, 21.75 and 311.75. For an independent comparison, change the direct-reference control F2 to =SUM(B2:B5). J4 should return to zero.

Recovery: restore the name’s endpoint to row 4 and restore F2 to =SUM(B2:B4). Both totals should again be 268.75, even with Tape still visible. This is an intentional exclusion test, not a reason to leave an incomplete production total. Finish by extending both definitions to row 5 again.

Advanced case: keep a local scenario separate

In a disposable copy, create a differently named definition, LocalFeeRate, under Sheet1, with the value 0.10. After creating it, enter a new scratch formula in L3: =SUM(OrderNet)*LocalFeeRate. Expect 29 for the extended 290 net total. Enter =L3-F3 in L4; expect 7.25. The production formula H3 still uses the workbook’s FeeRate and should remain 21.75.

Insert a second worksheet with Insert → Sheet and enter =FeeRate in A1: the workbook name should give 0.075. In A2, enter =LocalFeeRate. That local name is outside its scope, so it should not resolve to 0.10 on the second sheet. Inspect the names dialog from each sheet to verify where the definition belongs rather than accepting a copied numeric value as evidence of scope.

For cleanup, clear the scratch formulas on both sheets before removing the experimental LocalFeeRate definition using its minus sign. Do not remove the production FeeRate. Return to Sheet1 and check H3 21.75 and J4 0. Use distinct names for this exercise: the manual describes same-name precedence, but name creation and deletion can have additional implementation constraints. This guide does not promise that adding or deleting a duplicate name automatically rebinds existing formulas.

Maintenance and trouble checks

  • A correct-looking total can hide a stale endpoint. Compare the named range with the last occupied data row after every append.
  • A name absent from the dialog may be hidden by its filter or collapsed group. Clear the filter before recreating it.
  • The documented editor fixes the name after Enter; its definition remains editable. To replace a name, create the replacement, update dependent formulas and test them before deleting the old definition.
  • Save as a Gnumeric workbook, reopen it and repeat J4 and the rate-change check before handing it off. Reopen persistence is a check to perform, not a result observed for this lesson.

Documentation reviewed 2026-10-19: Defining Names, Gnumeric manual 1.12. The editor controls and scope rules above are documentation-backed. The fixture arithmetic was checked independently; this article has no native application run or screenshot. Installed-version dialog appearance and saved-file behavior remain to be verified.

See SUM for range totals and sort and filter data for keeping an order list organized.