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:
- Named Ranges are easier to use in formulas. You can give a descriptive name to named ranges, and you don't have to worry about knowing the range reference. For example, =AVERAGE(SalesData) is easier than =AVERAGE(B2:B21)
- You can update a named range and all the formulas using it would now refer to the new range. This is a big time saver.
Creating a Named Range in Google Sheets
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:
- Select the data for which you want to create a named range.
- Go to the Data Tab.
- Click on Named Ranges from the drop down.
- In the Named ranges pane, enter the name in the first field box. In this example, we are using the name SalesData.
- Make sure the right data range is listed in the second field box. If incorrect, change it.
- Click on Done.
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:
- To get the sum of all the sales: =SUM(SalesData)
- To find the average sales value: =AVERAGE(SalesData)
- To find the maximum sales value: =MAX(SalesData)
Naming Rules While Creating Named Ranges in Google Sheets
There are some rules you need to follow while creating named ranges in Google Sheets.
A named range:
- Can't contain any spaces or punctuation. For example, you can not have ‘Sales Data'. However, you can use underscore between words. Hence ‘Sales_Data' is fine.
- Can contain only letters, numbers, and underscores.
- Can't start with a number, or the words “true” or “false.”
- Must not be more than 250 characters long.
- Can't be something that refers to a range. For example, you can not name it A1, as it already refers to a cell. Similarly, you can not name it A1:A100.
Editing 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:
- Go to the Data Tab.
- Click on Named Ranges.
- Hover the mouse on the Named range that you want to edit and click on the Edit icon.
- Update the Name or the Range.
- Click on Done.
Creating a Dynamic 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:
- In a cell (E2 in this example) enter the formula =COUNT(C2:C100)+1. This will give us the number of cells that have a number in it. 1 is added to the formula as our sales data starts from row number 2. Also note that we have used C2:C100 so that if data is added in future, it will automatically be counted. Also, we have used COUNT function as the data is all numeric. Based on your data, you can use COUNTIF as well.
- In another cell (F2 in this example), enter the formula =“Sheet1!C2:C”&E2. This would give us the reference that would cover the entire sales data column. For example, if there are 10 sales transactions, it would give Sheet1!C2:C11. If there are 15 transactions, it will give Sheet1!C2:C16
- Go to the Data tab and select Named Ranges.
- Create a named range with the name SalesData and use Sheet1!F2 as the range.
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:
- How to Create a Drop Down List in Google Sheets.
- Creating a Dependent Drop Down List in Google Sheets.
- How to Insert Checkbox in Google Sheets.