HomeGoogle DocsSpreadsheetHow to View Named Ranges in Google Sheets

How to View Named Ranges in Google Sheets

Published on

This tutorial explains how to view and edit named ranges in Google Sheets.

What are Named Ranges? Named Ranges involve assigning a name to a group of cells. This allows us to quickly reference them in formulas without needing to use cell addresses.

In this tutorial, I will discuss how to view named ranges in Google Sheets and also how to delete them.

Why is this necessary?

It all begins when you are working on a shared sheet that contains formulas entered by another user and shared with you.

To comprehend the formulas in a shared spreadsheet, it’s essential to first check for the existence of named ranges in that sheet, as the use of named ranges is common among spreadsheet users.

If you are new to our page, there is already a tutorial on adding named ranges in Google Sheets. Please switch to the tutorial below and come back here. This will help you understand the usefulness of this awesome feature.

Related: How to Use Named Ranges In Google Sheets for Cleaner Formulas

Viewing Named Ranges in Google Sheets

There are two ways to view named ranges in Google Sheets, and the first method is solely for observing the range without the ability to edit or delete it.

Method 1:

In the first method, you need to know the name of the range. Enter the named range’s name in a cell, starting with an equal sign.

Example: =PurchaseValue

This will display the named range and the cell range it encompasses. To navigate to that range, follow these steps:

Use the keyboard shortcut Ctrl + G (Mac) to open the “Go to range” dialog. For Windows, press Alt + / and type “Go to” to access the “Go to range” dialog.

Enter the named range name and press Enter. This will take you to the named range.

Method 2:

Click on “Data” > “Named ranges” to view the list of named ranges in the sidebar panel.

Screenshot demonstrating the process of viewing named ranges in Google Sheets

Simply clicking on a named range in the list will direct you to the covered area (cells). This method allows you to view, edit, and delete named ranges.

How to Delete Named Ranges in Google Sheets

After exploring two methods to view named ranges in Google Sheets, let’s now focus on the process of editing or deleting them.

To edit or delete a named range, follow these steps:

  1. Navigate to “Data” > “Named ranges.”
  2. Hover your mouse over the named range that you want to edit or delete. Click on the pencil icon.
  3. To delete the named range, click on the trash bin icon. If you wish to edit it, simply modify the range in the provided field.

What happens when a formula that uses a deleted named range in Google Sheets?

While the formula might still return the correct result, upon inspection, you will notice that the named range’s name has been replaced by #REF!. It is crucial to correct the formula promptly to prevent potential issues later on.

How to Locate Formulas that Use Deleted Named Ranges in Google Sheets

The most straightforward method to locate a formula that uses a deleted named range involves using conditional formatting (cell highlighting).

Assuming your sheet contains the range A1:Z1000, follow these steps to highlight cells containing formulas that use deleted named ranges:

  1. Go to “Format” > “Conditional formatting.”
  2. In the sidebar panel, find “Apply to range.” If you already have existing rules, click “Add another rule” to locate it.
  3. Enter the range A1:Z1000.
  4. Under “Format rules,” select “Custom formula is…” and enter the following formula: =REGEXMATCH(FORMULATEXT(A1), "REF")
  5. Click Done. This will highlight all cells containing formulas with a “REF” error. This method helps identify cells with formulas using deleted named ranges.

Note: If your file has multiple sheets, you may need to apply this method to all sheets. Additionally, be aware that highlighting may impact the performance of your sheet, especially with large volumes of data.

Resources

This tutorial has outlined how to view, edit, and delete named ranges, as well as locate formulas containing deleted named ranges in Google Sheets. Here are some advanced uses of named ranges.

  1. How to Use Named Ranges in Query in Google Sheets
  2. The Use of Named Ranges in Sumif in Google Sheets
  3. The Use of Named Ranges in Vlookup in Google Sheets
  4. Importrange Named Ranges in Google Sheets
  5. Dynamic Column Id in Query Importrange Using Named Ranges
  6. Auto-Expand Named Ranges in Google Sheets to Accommodate New Rows
  7. Highlighting Named Ranges in Google Sheets
  8. Dynamic H&V Named Range in Google Sheets
  9. How to Use Named Ranges in Conditional Formatting in Google Sheets
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

  1. Thank you for your direction.

    Now that I have a good-sized spreadsheet, loaded with many named ranges, I need to obtain a list of those named ranges so that I may print the list for documentation purposes.

    Using the display function in Sheets works well, but limits my scanning through them to use the scroll feature to move up and down through the list.

    What I really need is a list of these named ranges so that when in the middle of constructing complicated formulas, I can simply consult the one-page hardcopy of the names I would like to include. Excel allows printing them. Can Sheets do the same?

    Thank you kindly.

LEAVE A REPLY

Please enter your comment!
Please enter your name here