Arrow left
Back to product guides
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

  1. Select a cell, a range, or an array you want to name.
  2. Navigate to the “Data” tab → “Named range”, which opens a menu on the right side.
  3. Input a name for the selected range in the upper text box in the menu.
  4. Ensure the proper range is selected. Revise it if it is not correct.
  5. 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

  1. You can use only letters, numbers, and underscores for a name. Any spaces and punctuation are not allowed.
  2. A name can’t start with a number or the words 'true' or 'false'.
  3. The number of characters needs to be within one to two hundred fifty.
  4. 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

  1. (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.
  2. Hoover your mouse cursor above the Named Range you want to edit, showing a pencil icon.
  3. 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:

  1. Make your worksheets easy to follow
  2. 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 build a financial KPI dashboard or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. 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

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711