Named Ranges in Google Spreadsheets

Watch How to Used Named Ranges in Google Spreadsheets:

 

Be sure Watch How to Protecting Cell Ranges in Google Spreadsheets

Named ranges allow you to use a range throughout different sheets, formulas etc. and then update that range from one location. It’s a very powerful feature that brings Google Spreadsheets.

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:

  1. Select the cell or range of cells that you want to define. (You can also specify a cell or range later.)
  2. Click the Data menu.
  3. 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.
  4. 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.
  5. 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.
  6. Click Done.

Once you have the Named and protected ranges panel open, you can also add new ranges by clicking on +Add a range.

Here are some guidelines for how to name a range:

  • A range name 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 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.
To delete a range name, follow these steps:
  1. Click the Data menu.
  2. Point your mouse to Named and protected ranges. This will open a side panel that shows all your ranges.
  3. Select the named range you'd like to delete. Then click the Edit button.
  4. 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.

 

Reference: 

Google Gooru - Using Named Ranges in Google Spreadsheets

Google Drive Help - Named and protected ranges

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk