Named and protected ranges
The Quick Answer
- Use range names to give a cell, or group of cells, a nickname that’s easy to read and remember
- Simplify your formulas by using range names, as in =SUM(NamedRange)
- Protect data by adding access permissions for your named ranges to prevent others from modifying particular cell contents, such as complex formulas that shouldn’t be touched
About range names
Named ranges is a feature that allows you to assign a name to a cell or a group of cells. For example, instead of using "A1" to designate a cell (or "A1:B2" to designate a group of cells), you can name a cell or group "budget_total," or whatever title you choose.
Range names allow you to create meaningful categories in your spreadsheets. As a result, entering formulas becomes easier and complex spreadsheets become more readable.The range names feature has several convenient uses:
- Because it provides a mnemonic device, you don't need to catalog or remember multiple cell numbers and ranges.
- Using range names simplifies your formulas and makes them more comprehensible. Instead of entering multiple cell addresses, you can enter the corresponding range names. For example, instead of entering =SUM(A1:B2 , D4:E7), you can enter the simpler, more intuitive, =SUM(budget_total , quarter2).
Create a range name
To create a range name, follow these steps:
- Select the cell or range of cells that you want to define. (You can also specify a cell or range later.)
- Click the Data menu.
- Point your mouse to Named and protected ranges. This will open a side panel that allows you to create and manage all your ranges. You can also access this side panel by right-clicking on the selected cell(s) and selecting Name and protect range.
- In the first text field, define your range name. This will show the default assigned name (e.g. NamedRange1) until you assign a custom name.
- In the second text field, define the range itself. If you highlighted cells before opening the Named and protected ranges panel, the range will already be defined. If you’d like to enter a range, or make changes to the highlighted range, click on the spreadsheet grid icon to the right of the text box and then highlight the range. You can also define the range manually by typing in this text field.
- Click Done.
Once you have the "Named and protected ranges" panel open, you can also add new ranges by clicking on +Add a range.
Guidelines for how to name a range
- A named range can only contain letters, numbers, and underscores.
- It can't start with a number, or the words "true" or "false."
- It must be greater than zero characters, but less than 250 characters.
- It can't contain any spaces.
- It can't contain punctuation, for example commas, periods, dashes, and exclamation points.
- It can't be evaluated as a range in either A1 or R1C1 syntax. (In other words, don't give your range a name like "A1:B2" or "R1C1:R2C2", which already refer to a specific range on your spreadsheet and which may be misinterpreted as such by Google spreadsheets.)
- A named range can be renamed, however renaming a range may break formulas or references to the original range name.
Delete a range name
- Click the Data menu.
- Point your mouse to Named and protected ranges. This will open a side panel that shows all your ranges.
- Select the named range you'd like to delete. Then click the Edit button.
- The range entry will expand, and you’ll see a trash can icon to the right of the range name. Click on the trash can, and then click Remove.
When you delete a named range, any references to this name (in formulas, for example) will no longer be updated. However, if you reuse this name, with the same (or another) range, any references using this name will begin updating correctly for the range given.
Protect a named range
When creating a new range, you’ll see the option to protect your range before saving it:
- After defining and naming the range, check the Protect checkbox, and click Done. This will open a dialog box that lists your document collaborators and their access settings.
- To change a collaborator’s access to the range, click on the drop-down menu to the right of their name and choose to grant them Comment or Edit access.
- Click Done.
To protect a range you had previously named and defined, follow these steps:
- Select that range in the Named and protected ranges panel.
- Click the Edit button.
- Click Add permissions (or Modify permissions if you are making changes to range protection settings). From here, follow steps 2-3 above.
Collaborators who try to edit a cell in a protected range they don’t have edit access to will receive an error message. When opening a spreadsheet with protected ranges, individuals will see ranges they don’t have edit access to displayed with a checkered background. If the background pattern makes it difficult to read spreadsheet content, you can hide protected ranges by pointing your mouse to the View menu and unchecking Protected Ranges. The ability to protect a range is currently not available in the new Google Sheets, but will be coming soon.