Concept: Protected Ranges and Defined Names in Workbooks
Workbook owners and editors can create a
defined name
for cells or ranges of cells in
workbooks to make formula-writing easier and to make formulas easier to read. Owners can
also protect
ranges to prevent all collaborators, including editors, from
changing them.Defined Names
Action | Steps | Notes |
|---|---|---|
Create a defined name. | Select the cell or range, right-click, and then select Define
Name .The Formula field contains the selected cell or range reference by
default; to define a name for a formula, constant, or non-contiguous set
of cell ranges, edit the field as needed.If you want to create a defined name containing live data that updates
automatically when you refresh the live data, use the ARRAYAREA formula.
Example: Specify something like this in the
Formula field for the defined name:
=ARRAYAREA('Sheet1'!A1) | Naming rules are consistent with popular spreadsheet products, to maximize
compatibility. Names can be between 4 and 255 characters long. Defined names must be unique per workbook, and can't be the same as any pivot table names
or live data table names. You can't edit the range of an existing defined name; you need to create
a new defined name. |
View all defined names. | Open the Defined Names panel by selecting . | When you select a defined name, its cells are highlighted in the
workbook. |
Delete a defined name. | Hover over the range in the Defined Names panel, then click
Delete . | If you delete a defined name, any formulas associated with that name will
stop working. Recreating it with the same name will restore functionality in Worksheets, but will not
work for the Slides integrating app. In this situation, you'll need to
create new links. This is because Slides uses an internally-generated ID
instead of the name to identify defined names. |
Protected Ranges
Workbook owners can protect ranges to prevent all collaborators, including editors, from
changing them.
For protected ranges, in the Protected Ranges panel the workbook owner sees
Can
Edit
below any protected range name; all other collaborators see
Can View
.When any user selects a cell in a protected range, the user interface displays a lock
indicator on the function bar icon.
Note
: When you refresh the live data in a workbook, Worksheets disregards protected
ranges when doing those refreshes. You can't prevent a live data refresh from
overwriting existing workbook content.Action | Steps | Notes |
|---|---|---|
Protect a range. | Select the cell or range, right-click, and then select Protect
Range . | Here's a tip for filtering protected data: A user with edit permission can change filter
selections, by clicking the filter icon on a column and then changing
the selected checkboxes, but they can't completely turn filtering on or
off by going to . As a best practice, if you want to allow users to
filter protected data, place a filter on the workbook before protecting
it, and then remind people with edit access to click the filter icon to
do their own filtering. |
View all protected ranges. | Open the Protected Ranges panel by selecting . | When you select a protected range, its cells are highlighted in the workbook. |
Remove protection for a range. | Hover over the range in the Protected Ranges panel, then click Delete . |