Named Ranges in Google Sheets (Complete Guide) | Layer Blog (2023)

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) | Layer Blog (1)

Named Ranges in Google Sheets (Complete Guide) - Open Google Sheets

  1. 2. Go to Data > Named ranges.

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (2)

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) | Layer Blog (3)

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) | Layer Blog (4)

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) | Layer Blog (5)

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) | Layer Blog (6)

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) | Layer Blog (7)

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) | Layer Blog (8)

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) | Layer Blog (9)

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) | Layer Blog (10)

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) | Layer Blog (11)

Named Ranges in Google Sheets (Complete Guide) - Edit Name Or Range

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (12)

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) | Layer Blog (13)

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) | Layer Blog (14)

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) | Layer Blog (15)

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 on23 Must-Know Google Sheets Formulas.

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

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (16)

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) | Layer Blog (17)

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) | Layer Blog (18)

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) | Layer Blog (19)

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) | Layer Blog (20)

Named Ranges in Google Sheets (Complete Guide) - SUM with Named Range

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (21)

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

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.

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (22)

Named Ranges in Google Sheets (Complete Guide) - Open Google Sheets

  1. 2. Go to Insert > Link.

Named Ranges in Google Sheets (Complete Guide) | Layer Blog (23)

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) | Layer Blog (24)

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) | Layer Blog (25)

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) | Layer Blog (26)

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) | Layer Blog (27)

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) | Layer Blog (28)

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) | Layer Blog (29)

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) | Layer Blog (30)

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) | Layer Blog (31)

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) | Layer Blog (32)

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) | Layer Blog (33)

Named Ranges in Google Sheets (Complete Guide) - Color-Coded Drop-Down List

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates withConfluence, 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 todayand claim yourforever-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 belowto 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

FAQs

Named Ranges in Google Sheets (Complete Guide) | Layer Blog? ›

How to reference a range in a Google Sheets formula? To reference a single cell in a formula, use the name of the sheet followed by an exclamation mark, the column and finally the row. A cell that is in Sheet1 at the intersection of column C and row 5 will have the following reference: Sheet1!

How to do named ranges in Google Sheets? ›

Name a range
  1. Open a spreadsheet in Google Sheets.
  2. Select the cells you want to name.
  3. Click Data. Named ranges. A menu will open on the right. ...
  4. Type the range name you want.
  5. To change the range, click Spreadsheet .
  6. Select a range in the spreadsheet or type the new range into the text box, then click Ok.
  7. Click Done.

How do I reference part of a named range in Google Sheets? ›

How to reference a range in a Google Sheets formula? To reference a single cell in a formula, use the name of the sheet followed by an exclamation mark, the column and finally the row. A cell that is in Sheet1 at the intersection of column C and row 5 will have the following reference: Sheet1!

Can named ranges be dynamic Google Sheets? ›

Dynamic Named Range in Google Sheets is a range which is based on a formula. For example, if we have a shopping list in which we periodically add items to the list along with their prices and a formula that calculates the subtotal. We may add or delete items from the list and we want the subtotal to update accordingly.

How do you create multiple named ranges in Google Sheets? ›

Google Sheets makes it possible to create multiple named ranges. To do this, simply click on Add a range and repeat the same process as before. Google Sheets can also name a single cell with the same process.

Does Google Sheets support named ranges? ›

You can name ranges in Google Sheets to keep better track of them and create cleaner formulas. For example, instead of using "A1:B2" to describe a range of cells, you could name the range "budget_total." This way, a formula like "=SUM(A1:B2, D4:E6)" could be written as "=SUM(budget_total, quarter2)."

How do you use named ranges instead of cell references? ›

Follow these steps to change the range reference:
  1. On the Ribbon, click the Formulas tab.
  2. Click Name Manager.
  3. In the list, click on the name that you want to change.
  4. In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
  5. Click the check mark, to save the change.
Jun 22, 2022

How do you enter a formula referencing the named ranges? ›

Use names in formulas
  1. Select a cell and enter a formula.
  2. Place the cursor where you want to use the name in that formula.
  3. Type the first letter of the name, and select the name from the list that appears. Or, select Formulas > Use in Formula and select the name you want to use.
  4. Press Enter.

Can I use named ranges throughout the worksheet? ›

You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard.

Why are named ranges sometimes used in spreadsheets? ›

Range names are names used to refer to cell references, formula results, or values. They are often used to avoid hard-coded values appearing in formulas and to make formulas clearer in general. They are stored in what is known as the Name Manager in Excel.

Why are named ranges used in spreadsheets? ›

Excel Named Ranges makes it easy to refer to data sets in Excel. You can create a named range in Excel for each data category, and then use that name instead of the cell references. For example, dates can be named 'Date', Sales Rep data can be named 'SalesRep' and sales data can be named 'Sales'.

How do I create multiple named ranges at once? ›

How To Create Multiple Defined Names Based On Labels In Other Cells
  1. Highlight the range of cells which you want to name along with their labels.
  2. Go to the Formula tab in the ribbon.
  3. Under the Defined Names section select Create from Selection.

Can you create multiple range names at the same time? ›

Set Multiple Named Ranges at the Same Time

The simple solution to this is to use a built-in feature by Excel called “Create from Selection” under the Formulas tab within the Named Ranges group. You'll see that the named ranges have been applied to the values in the table.

What are unsupported named ranges in Google Sheets? ›

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.

How do you use a named range? ›

Create a named range from selected cells in a worksheet
  1. Select the range you want to name, including the row or column labels.
  2. Click Formulas > Create from Selection.
  3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
  4. Click OK.

What is a dynamic named range? ›

What do You Mean by Excel Dynamic Named Range? Dynamic named range in Excel is the ranges that change as the data in the range changes, and the dashboard or charts or reports associated with them. So that is why it is called dynamic. So we can name the range from the name box, so the name is a dynamic name range.

What are the two types of dynamic range? ›

The 2 Types of Dynamic Range

One is the dynamic range of the subject, the second is the dynamic range of your camera. The dynamic range of the subject is a measure of the range of light intensities from the shadows to the highlights.

What is a dynamic chart named range? ›

What is a Dynamic Chart Range? A dynamic chart range is a data range that updates automatically when you change the data source. This dynamic range is then used as the source data in a chart. As the data changes, the dynamic range updates instantly which leads to an update in the chart.

What is the scope of a named range? ›

Named ranges in Excel have something called "scope", which determines whether a named range is local to a given worksheet, or global across the entire workbook. Global names have a scope of "workbook", and local names have a scope equal to the sheet name they exist on.

What is an acceptable name for a named range? ›

Regarding a named range, the scope of a name is the location within which Excel recognizes the name without qualification. Excel recognizes a construct like 3+4= as a legitimate formula. Range names may begin with the caret (^) character. You cannot use a named range in a formula that references another worksheet.

Do named ranges behave like absolute cell references? ›

By default, named ranges behave like absolute references and don't change when you copy formulas. In addition, they make your formulas easier to read.

What are the benefits of using a named range? ›

For accountants, named ranges allow you to quickly navigate a workbook, read formulas with ease and create formulas with named ranges while waiting on data. Setting up named ranges can be done very quickly with a few clicks of the mouse. Once set up they can be used in formula and used to navigate a workbook.

Can you use a named range instead of a cell address in a formula? ›

You can use a named range instead of a cell address in a formula. Answer: The statement is true. MS Excel allows us to assign names to cells in a worksheet and use it for quickly locating specific cells by entering the names. This could be useful when working with large spreadsheets.

How do you reference a named range in 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'.

How to apply the range names to the existing formulas and functions in the worksheet? ›

Applying Range Names to Formulas
  1. Define the named range you want used in your worksheet.
  2. Select the cells that contain formulas.
  3. Display the Formulas tab of the ribbon.
  4. Click the down-arrow at the right of the Define Name tool (in the Defined Names group) and then choose Apply Names. ...
  5. Click OK.

How do you reference a named range in a workbook? ›

Click on the "Formulas" tab on the Excel Ribbon at the top of the window. Click "Define Name" button in the Formula tab. In the "New Name" dialogue box, under the field "Scope" choose the specific worksheet that the range you want to define is located (i.e. "Sheet1")- This makes the name specific to this worksheet.

What are 2 advantages of using a named range in Excel? ›

Top 5 benefits of using names in Excel
  • Excel names make formulas easier to make and read. ...
  • Excel names allow creating expandable formulas. ...
  • Excel names make formulas easier to re-use. ...
  • Named ranges simplify navigation. ...
  • Named ranges allow creating dynamic drop-down lists.
Mar 20, 2023

Do named ranges slow down Excel? ›

The only problem is that named ranges, especially dynamic ranges, consume a considerable amount of working memory. Overusing them may slow down your Excel spreadsheet.

What are the disadvantages of Excel name ranges? ›

So, what are the disadvantages of name ranges? Well, at first, if you don't use them often, they can seem just a little bit fiddly. The second disadvantage of name ranges is that they don't lend themselves to copying relative references. However, there are some workarounds for this when we are creating name ranges.

How do I keep a cell reference constant in Google Sheets? ›

You can use an absolute reference to keep a row and/or column constant in the formula. An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.

Can you create a dynamic table in Google Sheets? ›

3 Steps to Build a Dynamic Dashboard In Google Sheets

The steps to build a dynamic dashboard in Google Sheets are as follows: Step 1: Load/Collect Data. Step 2: Create a Table/Chart with Raw Data. Step 3: Format for Readability.

How do I always reference the same cell in Google Sheets? ›

An absolute cell reference ensures that the formula is always refers to the same cell, even when it's copied and pasted. Absolute References remain constant no matter where they are copied. Add $ symbols to make the reference absolute.

How do I select all named ranges? ›

To select all named ranges, simply press the Shift key. And if your intention is to delete only a few named ranges, hold down the Ctrl key and the select those ranges to delete.

How do I copy all named ranges? ›

To do this, go to the Formulas tab and click on Name Manager. Then, select the named range you want to copy and click on the Copy button. You will then be able to paste the named range into another worksheet.

What characters Cannot be used when creating a range name? ›

Guidelines for creating names

The first character of a name must be a letter or an underscore character (_). Remaining characters in the name can be letters, numbers, periods, and underscore characters. In some languages, Excel may replace certain characters with underscores. Spaces are not allowed.

Why does my named range keep changing? ›

If you Insert or Delete cells, the references to the named range can change depending on the relationship of the Named Range reference to the Inserted or Deleted cells.

Do named ranges always contain a single cell? ›

Defined names can only be applied to a single cell. You can apply a defined named to multiple cells to make it easier to understand the purpose of a cell reference, constant, formula, or table.

How do you do relative references in Google Sheets? ›

Relative references
  1. Select the cell that will contain the formula. In our example, we'll select cell D4.
  2. Enter the formula to calculate the desired value. ...
  3. Press Enter on your keyboard. ...
  4. Select the cell you want to copy. ...
  5. Click and drag the fill handle over the cells you want to fill. ...
  6. Release the mouse.

Can you reference a cell for a sheet name? ›

To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address. For example, to refer to cell A1 in Sheet2, you type Sheet2!A1.

How would you refer to a specific cell? ›

Click the cell in which you want to enter the formula. , type = (equal sign). Do one of the following, select the cell that contains the value you want or type its cell reference. You can refer to a single cell, a range of cells, a location in another worksheet, or a location in another workbook.

What is cell reference and its types with examples? ›

A cell reference or cell address is a combination of a column letter and a row number that identifies a cell on a worksheet. For example, A1 refers to the cell at the intersection of column A and row 1; B2 refers to the second cell in column B, and so on.

How do you use named ranges? ›

Create a named range from selected cells in a worksheet
  1. Select the range you want to name, including the row or column labels.
  2. Click Formulas > Create from Selection.
  3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
  4. Click OK.

Can I use named range in conditional formatting? ›

Conditional Formatting Rules can be applied to ranges referenced by Name, but as soon as the rule is applied, that Name is replaced with an absolute reference to the range.

References

Top Articles
Latest Posts
Article information

Author: Laurine Ryan

Last Updated: 17/11/2023

Views: 6270

Rating: 4.7 / 5 (57 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Laurine Ryan

Birthday: 1994-12-23

Address: Suite 751 871 Lissette Throughway, West Kittie, NH 41603

Phone: +2366831109631

Job: Sales Producer

Hobby: Creative writing, Motor sports, Do it yourself, Skateboarding, Coffee roasting, Calligraphy, Stand-up comedy

Introduction: My name is Laurine Ryan, I am a adorable, fair, graceful, spotless, gorgeous, homely, cooperative person who loves writing and wants to share my knowledge and understanding with you.