Don’t forget to share this post

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.
Named Ranges in Google Sheets Complete Guide Open Google Sheets
Named Ranges in Google Sheets (Complete Guide) - Open Google Sheets
  1. 2. Go to Data > Named ranges.
Named Ranges in Google Sheets Complete Guide Data Named Ranges
Named Ranges in Google Sheets (Complete Guide) - Data > Named Ranges
  1. 3. In the sidebar, click the “Add a range” button.
Named Ranges in Google Sheets Complete Guide Add a Range
Named Ranges in Google Sheets (Complete Guide) - Add a Range
  1. 4. Type the name for your range.
Named Ranges in Google Sheets Complete Guide Type Range Name
Named Ranges in Google Sheets (Complete Guide) - Type Range Name
  1. 5. Select the range of cells you want to name and click “OK”.
Named Ranges in Google Sheets Complete Guide Select Range of Cells
Named Ranges in Google Sheets (Complete Guide) - Select Range of Cells
  1. 6. Once you’re happy with the selection and the name, click “Done”.
Named Ranges in Google Sheets Complete Guide Click Done
Named Ranges in Google Sheets (Complete Guide) - Click Done
  1. 7. The newly named range now appears in the sidebar.
Named Ranges in Google Sheets Complete Guide Named Range in Sidebar
Named Ranges in Google Sheets (Complete Guide) - Named Range in 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.
Named Ranges in Google Sheets Complete Guide Name Columns in Table
Named Ranges in Google Sheets (Complete Guide) - Name Columns in Table

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.
Named Ranges in Google Sheets Complete Guide Data Named Ranges 2
Named Ranges in Google Sheets (Complete Guide) - Data > Named Ranges
  1. 2. From the sidebar, select the named range you want to edit and click the pencil icon.
Named Ranges in Google Sheets Complete Guide Click Pencil Icon
Named Ranges in Google Sheets (Complete Guide) - Click Pencil Icon
  1. 3. From here, you can change both the name and the range of cells included. Click “Done” to save the changes.
Named Ranges in Google Sheets Complete Guide Edit Name Or Range
Named Ranges in Google Sheets (Complete Guide) - Edit Name Or Range
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

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.
Named Ranges in Google Sheets Complete Guide Data Named Ranges 3
Named Ranges in Google Sheets (Complete Guide) - Data > Named Ranges
  1. 2. Select the named range you want to delete and click the pencil icon.
Named Ranges in Google Sheets Complete Guide Click Pencil Icon 2
Named Ranges in Google Sheets (Complete Guide) - Click Pencil Icon
  1. 3. Click the trash can icon next to the range name to delete the named range.
Named Ranges in Google Sheets Complete Guide Click Trash Icon
Named Ranges in Google Sheets (Complete Guide) - Click Trash Icon

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.
Named Ranges in Google Sheets Complete Guide Named Ranges 4
Named Ranges in Google Sheets (Complete Guide) - 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.
Named Ranges in Google Sheets Complete Guide ARRAYFORMULA Function
Named Ranges in Google Sheets (Complete Guide) - ARRAYFORMULA Function
  1. 3. From the drop-down, click on the range you want or use ‘Tab’ to select it.
Named Ranges in Google Sheets Complete Guide ARRAYFORMULA with Named Range
Named Ranges in Google Sheets (Complete Guide) - ARRAYFORMULA with Named Range
  1. 4. Press ‘Enter’ to import the entire list of customer emails.
Named Ranges in Google Sheets Complete Guide Named Range Imported
Named Ranges in Google Sheets (Complete Guide) - Named Range Imported
  1. 5. To sum the values in the amount column, use the SUM function and select the named range as the function’s argument.
Named Ranges in Google Sheets Complete Guide SUM with Named Range
Named Ranges in Google Sheets (Complete Guide) - SUM with Named Range
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
Named Ranges in Google Sheets Complete Guide Open Google Sheets 2
Named Ranges in Google Sheets (Complete Guide) - Open Google Sheets
  1. 2. Go to Insert > Link.
Named Ranges in Google Sheets Complete Guide Insert Link
Named Ranges in Google Sheets (Complete Guide) - Insert Link
  1. 3. Click “Sheets and named ranges”, as shown below.
Named Ranges in Google Sheets Complete Guide Sheets Named Ranges
Named Ranges in Google Sheets (Complete Guide) - Sheets & Named Ranges
  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.
Named Ranges in Google Sheets Complete Guide Select Named Range
Named Ranges in Google Sheets (Complete Guide) - Select Named Range
  1. 5. That’s it. You can navigate directly to the table by clicking the link.
Named Ranges in Google Sheets Complete Guide Link to Named Range
Named Ranges in Google Sheets (Complete Guide) - Link to Named Range

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.
Named Ranges in Google Sheets Complete Guide Name List
Named Ranges in Google Sheets (Complete Guide) - Name List
  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.
Named Ranges in Google Sheets Complete Guide Data Data Validation
Named Ranges in Google Sheets (Complete Guide) - Data > Data Validation
  1. 3. You’ll see the data validation sidebar. Click on “+ Add rule”.
Named Ranges in Google Sheets Complete Guide Add Rule
Named Ranges in Google Sheets (Complete Guide) - Add Rule
  1. 4. Under ‘Criteria’, select “Dropdown (from a range)”
Named Ranges in Google Sheets Complete Guide Dropdown From Range
Named Ranges in Google Sheets (Complete Guide) - Dropdown From Range
  1. 5. In the range selection box, type the name of your range and press ‘Enter’.
Named Ranges in Google Sheets Complete Guide Type Named Range
Named Ranges in Google Sheets (Complete Guide) - Type Named Range
  1. 6. You can customize the items by assigning different colors to each. Click ‘Done’ when you’re happy with the settings.
Named Ranges in Google Sheets Complete Guide Customize Items
Named Ranges in Google Sheets (Complete Guide) - Customize Items
  1. 7. That’s it. You have a color-coded drop-down list based on a named range.
Named Ranges in Google Sheets Complete Guide Color Coded Drop Down List
Named Ranges in Google Sheets (Complete Guide) - Color-Coded Drop-Down List

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 31 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets