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.
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.
To create a dynamic named range, in the File menu click Data and from the dropdown menu click Named ranges.
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.
Press the Done button to complete the 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:
=SUM(Q2_Data_2)
As you begin typing your dynamic named range, google will autosuggest the full name.
The Dynamic named range is now displayed. Any additional values entered in the B column will automatically be reflected in our dynamic range box.
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.
