Named Ranges in Google Sheets can be used to define a cell or a range of cells and give it a name. Now, instead of using the cell references, you can use the name of the named range in calculations.
For example, suppose I have sales transaction data for a store, and I need to calculate metrics such as the sum of all the sales, count of sales of item A, average sale value, maximum/minimum sale value.
Instead of using the cell references of the range that has the sales data, I can create a named range (with a descriptive name such as ‘SalesData’) and use this name instead.
Using Named Ranges have the following benefits:
Suppose I have a sales transaction data as shown below, and I want to create a named range for the sales data:
Here are the steps to create a named range in Google Sheets:
This will create the Named Range – SalesData.
Once created, you can use the named range in formulas. Here are some examples where you can use the SalesData named range:
There are some rules you need to follow while creating named ranges in Google Sheets.
A named range:
Once a Named Range is created, you may want to change it or update it.
For example, if you have a named range that refers to the sales data, and you have three new transactions that need to be added to the data, you would have to update the named range to include these three additional data points.
Here are the steps to update/edit a named range in Google Sheets:
Named Ranges are great as it allows you to update the named range once and all the formulas using that named range automatically update.
However, doing this manually may seem tedious if you have to frequently update the data or if you have too many named ranges.
While in Excel, it’s possible to create a named range using the OFFSET or INDIRECT formula, it doesn’t work in Google Sheets.
However, you can do that using a neat INDIRECT function trick.
Suppose you have a dataset as shown below and you want to create a named range for the sales data in a way that whenever new data is added, the named range automatically updates.
Here are the steps to create a dynamic named range in Google Sheets:
Now, you can use the following formula to refer to the dynamic named range: =INDIRECT(SalesData).
The INDIRECT function would use the named range and refer to cell F2, which in turn has the reference for the sales data. Since we have made the range in F2 dynamic (by using =“Sheet1!C2:C”&E2), the named range also becomes dynamic.
For example, if you now want to calculate the sum of sales, you can use the formula =SUM(INDIRECT(SalesData)). If more transaction records are added, the formula would automatically update and give you the new sum of sales.
You May Also Like the Following Google Sheets Tutorials: