With named ranges, you can assign a name to a contiguous range of cells for easy reference in formulas, select, and multiselect.
Introduction to Named Ranges
Like traditional spreadsheets, Spreadsheet.com supports named ranges. The named ranges feature allows you to assign a name to a contiguous range of cells for reference in formulas, select, and multiselect.
You can access all of your named ranges from the name box in the upper left hand corner of your worksheet next to the function field. Here, you can manage your existing named ranges and create new ones.
Named ranges can be created and managed by users with Owner and Manager permissions.
Managing Named Ranges
Defining Named Ranges
Begin by selecting the range of cells you want to assign to a new named range. From the name box dropdown, select New Named Range from the bottom of the menu to open the New Named Range dialog. In this dialog, you can assign a name and define the range of cells.
Names cannot exceed 255 characters and cannot contain any spaces or special characters except for underscores.
Defining the range can be done explicitly (using the cell reference syntax found in formulas), or by selecting the cell icon next to the range field and dragging across the desired range in your workbook. Or, you can highlight the range before clicking New Named Range.
Editing Named Ranges
You can edit any existing named range by hovering over the named range in the name box dropdown and clicking the pen icon at right.
Note that editing the range or name will affect any formula that uses the named range reference.
Undefining Named Ranges
To undefine a named range, hover over the named range in the name box dropdown and click the trash can icon at right.
Removing a named range will retain the data in that range’s cells, but note that it may break formulas where the named range is referenced instead of referencing a cell range explicitly.
Using Named Ranges
Referencing Named Ranges in Formulas
You can use named ranges in place of explicit cell references in formulas. To do so, input the name of the named range (without quotation marks) where a cell or cells would otherwise be used in the formula. As you write your formula, Spreadsheet.com will autosuggest named ranges, indicated by the teal NR symbol next to the named range.
For example, let’s say that you’ve created a named range called “Q4_Earnings” including cells E6 through E10 and you want to generate a sum of these values in another cell. Normally, your function would be written as:
With your named range, you can instead write the formula as:
You can reference the same named range in any subsequent formulas you define as well, giving you a quick point of reference that doesn’t require you to remember or re-reference the original range.
Referencing Named Ranges in Select and Multiselect
You can also reference named ranges in select and multiselect options for data validation purposes. Instead of manually inputting options for each select or multiselect column, you can pass each one a named range with a list of options. The values in the named range will serve as the select or multi-select options.
From the select or multiselect options dialog, select “Named range” from the dropdown next to Source. Select your intended named range, and the select or multi-select options will auto-populate with the values from that named range.
In the example below, we’ve created the named range “Options” from cells A1:A3 and are assigning the multiselect options in Column B as the values from “Options”.