Create Dynamic Named Range in Google Sheets (2023 Update)

1 Minute

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 

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.