Named Ranges in Google Sheets (Complete Guide)

Do you frequently need to refer to specific values or formula results in your spreadsheet? For example, you may often have to refer to a table with product prices or a cell with your total revenue or profit margin. There are many ways to refer to values and calculations in Google Sheets, but none are as simple as using named ranges. Say you need to use the result of one formula in others. You can copy the value and hard-code it into the formula or use the cell reference. However, this is messy and can lead to mistakes. Instead, name that cell and use the contents by referring to that name.

In this guide, you will learn about named ranges and their purpose in Google Sheets. You have step-by-step instructions on how to create, edit, delete, and reference named ranges in Google Sheets. Additionally, you will learn how to use named ranges in formulas and how to create drop-down lists based on named ranges.

What are Named Ranges in Google Sheets?

Google Sheets allows you to name cells or ranges of cells so that you can easily refer to their contents from anywhere in your Google Sheet. You can name anything from a single cell to an entire table. Below is a list of the rules for range names in Google Sheets.

• Must contain between 1 and 250 characters
• Can only contain letters, numbers, and underscores.
• Cannot start with a number or the words “true” or “false”.
• Cannot contain spaces or punctuation marks.
• Cannot contain cell reference syntax, such as “A1” or “R1C1”.

What is the Purpose of Named Ranges?

The main purpose of named ranges is to make it easier to work with data. Since you don’t need to copy and paste values or remember cell references, the chances of making a mistake are lower, and your formulas look cleaner and are easier to read.

How to Use Named Ranges in Google Sheets?

Now that you know what it means to name ranges in Google Sheets, it’s time to learn to create, edit, delete, and reference named ranges.

How to Create a Named Range in Google Sheets?

Follow the steps below to create a named range in Google Sheets: first, for an entire table, then for a column.

1. 1. Open Google Sheets to the spreadsheet containing your data.

1. 2. Go to Data > Named ranges.

1. 3. In the sidebar, click the “Add a range” button.

1. 4. Type the name for your range.

1. 5. Select the range of cells you want to name and click “OK”.

1. 6. Once you’re happy with the selection and the name, click “Done”.

1. 7. The newly named range now appears in the sidebar.

1. 8. You can name individual columns within a named range by repeating steps 3 to 6. For example, I have also named the columns with the email addresses and the transaction amounts.

How to Edit a Named Range in Google Sheets?

Fortunately, editing named ranges in Google Sheets is easy.

1. 1. Go to Data > Named ranges.

1. 2. From the sidebar, select the named range you want to edit and click the pencil icon.

1. 3. From here, you can change both the name and the range of cells included. Click “Done” to save the changes.

How to Delete a Named Range in Google Sheets?

Deleting named ranges in Google Sheets is easy. Before you do, remember that any formulas using that named range will stop working. To avoid this, replace the named range in the formula with the corresponding cell references or values.

1. 1. Go to Data > Named ranges.

1. 2. Select the named range you want to delete and click the pencil icon.

1. 3. Click the trash can icon next to the range name to delete the named range.

How to Reference a Named Range in Google Sheets?

Named ranges are very easy to reference, of course, as that is their main purpose. For example, let’s learn how to import the data or perform calculations using ARRAYFORMULA and SUM. To learn more about Google Sheets functions, check out our guide on 23 Must-Know Google Sheets Formulas.

1. 1. Open Google Sheets to the file containing your named ranges.

1. 2. In an empty cell, type the ARRAYFORMULA. After the parenthesis, type in the name of your range. Alternatively, type in the first few letters to get the drop-down menu.

1. 3. From the drop-down, click on the range you want or use ‘Tab’ to select it.

1. 4. Press ‘Enter’ to import the entire list of customer emails.

1. 5. To sum the values in the amount column, use the SUM function and select the named range as the function’s argument.

How to Link to a Named Range in Google Sheets?

Another benefit of using named ranges is the possibility of creating quick links to access them.

1. 1. Select the cell or object to which you want to add the link.

1. 2. Go to Insert > Link.

1. 3. Click “Sheets and named ranges”, as shown below.

1. 4. Select the named range to which you want to link. If you leave the ‘Text’ area blank, Google Sheets will automatically take the range's name.

1. 5. That’s it. You can navigate directly to the table by clicking the link.

How Do I Use a Named Range in Data Validation in Google Sheets?

You can create drop-down lists with your named ranges using data validation. Go to Data > Data validation. On the sidebar, click “Add a rule”. Select “Dropdown (from a range)” under ‘Criteria’. In the range selection box, type the name of your range and press ‘Enter’. Once you’re happy with the settings, click “Done”.

In the following example, you will learn how to name a list of products and create a product drop-down list.

1. 1. Name your list of products as shown above. Be sure not to select the header as part of the list, or it will appear as one of the options.

1. 2. Select the cell where you want the drop-down list and go to Data > Data validation. I will create the drop-down list in the same sheet for this example, but it can be anywhere in your spreadsheet.

1. 3. You’ll see the data validation sidebar. Click on “+ Add rule”.

1. 4. Under ‘Criteria’, select “Dropdown (from a range)”

1. 5. In the range selection box, type the name of your range and press ‘Enter’.

1. 6. You can customize the items by assigning different colors to each. Click ‘Done’ when you’re happy with the settings.

1. 7. That’s it. You have a color-coded drop-down list based on a named range.

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

Naming frequently used cells or ranges in your Google Sheets is both useful and easy to do. You can refer to your data by name from other functions and tools in Google Sheets, and this is quicker and cleaner than hard-coding values or using cell reference syntax. Named ranges help you avoid mistakes and make your formulas easier to read.

You now know all about Google Sheets named ranges and their purpose. You have illustrated instructions on creating, editing, and deleting named ranges. Additionally, you know how to reference named ranges in formulas, how to link to them, and how to create data validation drop-down lists with them.

Check out these guides below to learn more about Google Sheets formulas, drop-down lists, and checkboxes in Google Sheets.

• How to Add a Drop-Down List in Google Sheets
• How to Add a Checkbox in Google Sheets
• 23 Must-Know Google Sheets Formulas