How to Use Named Ranges in Google Sheets

Named Ranges are shortcuts to specific ranges that you can define and use in your formulas. In this tutorial, you will learn how to create, use, and edit or delete named ranges.

Table of Contents
  1. How to create named ranges
  2. How to use named ranges in formulas: aggregate functions
  3. How to use named ranges in formulas: with ARRAYFORMULA
  4. How to edit or delete a named range
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. How to create named ranges
  2. How to use named ranges in formulas: aggregate functions
  3. How to use named ranges in formulas: with ARRAYFORMULA
  4. How to edit or delete a named range
5 Minutes

Do you need to process several ranges of data at the same time? Remembering their absolute or relative references can be tiresome as there is no way to easily identify them. To help you handle them, Google Sheets has a feature called Named Ranges. These are shortcuts to specific ranges that you can define and use in your formulas. In this tutorial, you will learn how to create, use, and edit or delete named ranges. Are you ready?

How to create named ranges

Step 1: Select the range to be named.

Target range selected. 

Step 2: Click Data, then select Named ranges.

Data, named ranges option highlighted.

Step 3: The sidebar Named ranges appear on the right side of Google Sheets. A textbox for the name and the range will appear. The range of the selected cells will automatically be loaded in the Range textbox. To name the range, simply type a name of your choosing (preferably something simple) in the Name textbox, then click Done. 

Named ranges sidebar, name and range specified. 

The named range will be listed in the sidebar. If you will use these named ranges frequently, it is best to keep the sidebar active.

Named ranges sidebar, named ranges list. The names and the corresponding ranges are shown. 

How to use named ranges in formulas: aggregate functions

Named ranges are designed to be used in formulas instead of referring to absolute or relative references. For example, to calculate the sum of all the values stored in the named range, we can simply use the name inside the SUM function:

=SUM(Population)

As you type the named range, the named range Population will appear in the drop-down box for autocompletion. You can either continue typing or simply press Tab or Enter to autocomplete.

Typing SUM function to the cell, with the named range as the reference.

The result will be the same as specifying the absolute reference:

Result of using named range to the SUM function. 

How to use named ranges in formulas: with ARRAYFORMULA

Named ranges work just as well when you want to use ARRAYFORMULA as a shortcut in applying a formula to an entire range. For our example, we want to divide individual entries in the range by 2. To do so, we simply need to use ARRAYFORMULA just like when the absolute or relative references are used:

=ARRAYFORMULA(Population/2)

The result is

Using ARRAYFORMULA with the named range.

You can learn more about ARRAYFORMULA here.

How to edit or delete a named range

Step 1: On the Named ranges sidebar, click the pencil symbol, labeled Edit, on the listed named range. 

Named ranges sidebar. Edit icon highlighted. 

Step 2: The textboxes will appear. You can either rename the named range by typing a new name in the Name textbox or delete it by clicking the Delete range icon on the right of the textbox.

Named ranges sidebar. Delete range icon highlighted.

If you want to rename the range, delete the existing name in the textbox and type the new one, then click Done. This will not break the formulas; they will be updated with the new name and will still work.

If you want to delete the named range, simply click Delete range. A prompt will appear reminding you that doing so will break the formulas that use the given named range. Click Remove if you are sure about your choice.

Prompt for removing the named range. 
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

April 26, 2022

How to Use Named Ranges in Google Sheets

Google Sheet spreadsheet

Do you need to process several ranges of data at the same time? Remembering their absolute or relative references can be tiresome as there is no way to easily identify them. To help you handle them, Google Sheets has a feature called Named Ranges. These are shortcuts to specific ranges that you can define and use in your formulas. In this tutorial, you will learn how to create, use, and edit or delete named ranges. Are you ready?

How to create named ranges

Step 1: Select the range to be named.

Target range selected. 

Step 2: Click Data, then select Named ranges.

Data, named ranges option highlighted.

Step 3: The sidebar Named ranges appear on the right side of Google Sheets. A textbox for the name and the range will appear. The range of the selected cells will automatically be loaded in the Range textbox. To name the range, simply type a name of your choosing (preferably something simple) in the Name textbox, then click Done. 

Named ranges sidebar, name and range specified. 

The named range will be listed in the sidebar. If you will use these named ranges frequently, it is best to keep the sidebar active.

Named ranges sidebar, named ranges list. The names and the corresponding ranges are shown. 

How to use named ranges in formulas: aggregate functions

Named ranges are designed to be used in formulas instead of referring to absolute or relative references. For example, to calculate the sum of all the values stored in the named range, we can simply use the name inside the SUM function:

=SUM(Population)

As you type the named range, the named range Population will appear in the drop-down box for autocompletion. You can either continue typing or simply press Tab or Enter to autocomplete.

Typing SUM function to the cell, with the named range as the reference.

The result will be the same as specifying the absolute reference:

Result of using named range to the SUM function. 

How to use named ranges in formulas: with ARRAYFORMULA

Named ranges work just as well when you want to use ARRAYFORMULA as a shortcut in applying a formula to an entire range. For our example, we want to divide individual entries in the range by 2. To do so, we simply need to use ARRAYFORMULA just like when the absolute or relative references are used:

=ARRAYFORMULA(Population/2)

The result is

Using ARRAYFORMULA with the named range.

You can learn more about ARRAYFORMULA here.

How to edit or delete a named range

Step 1: On the Named ranges sidebar, click the pencil symbol, labeled Edit, on the listed named range. 

Named ranges sidebar. Edit icon highlighted. 

Step 2: The textboxes will appear. You can either rename the named range by typing a new name in the Name textbox or delete it by clicking the Delete range icon on the right of the textbox.

Named ranges sidebar. Delete range icon highlighted.

If you want to rename the range, delete the existing name in the textbox and type the new one, then click Done. This will not break the formulas; they will be updated with the new name and will still work.

If you want to delete the named range, simply click Delete range. A prompt will appear reminding you that doing so will break the formulas that use the given named range. Click Remove if you are sure about your choice.

Prompt for removing the named range. 

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.