Google Sheets Formulas

Named Range in Google Sheets: Explained

In this article, you will learn the Named Range and how to use it in Google Sheets.

‍

What is the purpose of a named range?

The Named Range is one of the functions in Google Sheets, which allows you to give a name to a range or an array, and use the name (as a substitute for cell reference) in a formula. For instance, assume you have a formula such as “=SUM(B2:B6)”. If you name the range of B2:B6 as “sample” and register it, the SUM formula with the named range, “=SUM(sample)”, works properly. Thus, this function is beneficial when you repeatedly refer to a specific range or array in your formulas for your work.

‍

How to name a range

Select a cell, a range, or an array you want to name.
Navigate to the “Data” tab → “Named range”, which opens a menu on the right side.
Input a name for the selected range in the upper text box in the menu.
Ensure the proper range is selected. Revise it if it is not correct.
Click the “Done” button at the bottom right corner.

Steps 1 and 2

*How to open the Named Range menu from the menu bar in Google Sheets*

‍

Steps 3 to 5

*What the Named Ranges menu looks like in Google Sheets*

‍

What you need to bear in mind when you name a range are as follows

You can use only letters, numbers, and underscores for a name. Any spaces and punctuation are not allowed.
A name can’t start with a number or the words 'true' or 'false'.
The number of characters needs to be within one to two hundred fifty.
You can’t give a cell or range a name similar to a cell or range address like “A1” or “A2:C5”.

‍

How do the Named Ranges work in Google Sheets?

See how the Named Ranges work on a worksheet by looking at examples.

*What the Named Range looks like in Google Sheets*

‍

The first example shows how to use a Named Range representing a cell. We named cell C3 “mil”. This cell is to unit conversion. In cell E3, we want to show five million. So, we need to multiply 5 by 1,000,000, when you can use the named range. You can type “=5*mil”, and the “mil” part refers to the value contained in “mil” (cell C3). Thus, you get 5*1,000,000=5,000,000 in cell E3 as a result.

*How to use the Named Range for multiple types of formulas in Google Sheets*

‍

The other examples in the picture above show how to use a Named Range for a range.

As you can see, we named a range of cells (C7:C18) “Revenue2022”. You can input this name instead of a cell reference in each formula. These examples are relatively simple, so you may feel that you don’t need to set a named range. However, if you have a much larger data set and need to run an analysis with various formulas, the Named Range function benefits you for sure.

‍

How to edit the existing Named Ranges

(i - quicker) Go to the top corner and click the drop-down menu → “Manage named ranges” or (ii) Navigate to the “Data” tab → “Named range” or o to, both of which open a menu on the right side.
Hoover your mouse cursor above the Named Range you want to edit, showing a pencil icon.
Click the pencil icon and modify the formula.

*How to open the Named Ranges menu quickly in Google Sheets*

‍

Lastly, learn the pros and cons of the Named Ranges in the section below.

‍

Pros and Cons of the Named Ranges

Pros

The Named Ranges:

Make your worksheets easy to follow
Make your work efficient as you can insert arguments in formulas more quickly

Cons

The Named Ranges:

Get your collaborators confused when they are not named appropriately
The more significant number of named ranges you have, the more difficult it is for you to come up with short but identical names

‍

Analyze your live financial data in a snap

Are you learning this tip to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

*Automate any custom financial dashboard in Google Sheets with LiveFlow*

‍

What are the other advanced tips in Google Sheets?

If you don’t get the solution to your question in this article or you are interested in learning the other advanced tips in Google Sheets, you may find the answers in the following articles.

‍

Check the following articles to use Google Sheets more efficiently.

How to Find and Replace Words in Google Sheets

How to Enable Excel Shortcuts in Google Sheets

Relative Reference and Absolute Reference in Google Sheets: Explained

‍

Read the articles below to organize your data in Google Sheets.

How to Remove Duplicates in Google Sheets

Pivot Table in Google Sheets: Explained

‍

Go to the following pots to learn how to create a pull-down menu or limit data input in a cell.

Drop Down List in Google Sheets: Explained