In this article:

Create Dynamic Named Range in Google Sheets (2024 Update)

May 8, 2024

In this article we will show you how to create a google sheets dynamic range in just a few clicks. Simply follow the steps below.

Create a Google Sheets Dynamic Range

For the purpose of this demonstration an example data sheet was created. Click here to access this sheet and follow along.

In the below demonstration we will use custom named ranges for numerical values in one column to create a dynamic named range with the SUM function. 

1. Click “Data” in the File menu followed by “Named ranges”  

To create a dynamic named range, in the File menu click Data and from the dropdown menu click Named ranges. 

Creating a Dynamic named range in Google Sheets 

2. Click “+ Add a range” in the sidebar and customize parameters

In the side bar that appears on the right, click + Add a range and two new textboxes will appear. 

Assign a name for your named range (this must not include spaces).

Set the cell values manually or by clicking the symbol and selecting the first cell, dragging and highlight the rest of the required cells. 

In the example below we will name this as Q2_Data_2 and select cells B2:B covering the start of the data set minus the header and covering the rest of the entire column. 

If you manually selected a cell range like B2:B13, you need to also change it to B2:B. This will ensure that any additional entries made in column B are included in this named range and what makes it a dynamic range.

Google Sheets creating dynamic named ranges

Press the Done button to complete the named range.

3. Click an empty cell and enter the custom formula =SUM(Named_Range)

Click on an empty cell and enter the formula in the format of =SUM(Named_Range)

Formula Breakdown: 

=SUM: This function is used to add together a list of numerical values.

(Named_Range) This is the dynamic named range created via the Data option in the file menu (In our example below our named range ‘Q2_Data_2’)

In our example we will enter this formula in cell F7


As you begin typing your dynamic named range, google will autosuggest the full name.

Google Sheets dynamic named range creation 

4. Press Enter to see the results 

The Dynamic named range is now displayed. Any additional values entered in the B column will automatically be reflected in our dynamic range box.

Dynamic named ranges in Google Sheets 

We hope this article has helped you and given you a better understanding on how to create a dynamic named range in Google Sheets. You might also like our articles on how to select a data range in Google Sheets and how to use named ranges in Google Sheets.

To optimize your workflow, we recommend reading our guide on how to build an email list in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->