How To Named Ranges in Google Sheets : Complete Guide 2022

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : April 7, 2021 | Review Date : April 9, 2021
last Updated : May 31, 2021

Google Sheet, Spread Sheet

Why Named Ranges in Google Sheets?

When you are working with largest data set, may you have to use the same data, again and again for different perspective. Named ranges in google sheets to keep better track of data and create cleaner formulas. , you can keep them as references and using multiple time. it will help to reduce data duplication and unnecessary memory usage. In this tutorial we cover,

How to named ranges with a single sheet in a spreadsheet?
How to named ranges with multiple spreadsheets?
Implement dynamic named ranges.
When creating named ranges in google sheets, follow these rules.

About example

One of our clients has a basic expenses sheet that shows his office expenses in every 1 week of January, February, March in year. This sheet has 4 columns ( date, item, cost, expenses category and handle person ). Also in the G cell, we create a header call summary. that will present the calculated result( you can use any column for preview result ). Our client needs to calculate

Total of all expenses
Total of greater than $600 expenses

Download Example Sheet

How to named ranges with the single sheet in a spreadsheet?

Open a spreadsheet in Google Sheets.
To Highlight cells you’d like to name areas, Click column data areas that need to add named range and press Ctrl + A in windows or CMD + A in mac os( press CMD / Ctrl + Arrow left / right key used to ignore others cell)
Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ), Then select in Data > Named Ranges
Give Proper name to name range text box ( please check Range names rules ) – for above example name as costExprenses
If you need to change the name of the named ranges click the pencil icon and change the name in the textbox. You can also click the trash icon to delete the saved named range.

#Answer 01 :

Total of all expenses

Click the empty row (H:10) which across Total of all expenses and type =SU then you will auto search sum related formulas then select =SUM() within bracket type your range name it’s also auto dropdown and you can easily select your preferred named range. I type costExprenses then press Enter.

#Answer 02 :

Total of greater than $600 expenses

Click empty row (H:11) which across Total of greater than $600 expenses and type =SU then you will auto search sum related formulas then select =SUMIF(). This is a bit of an advanced formula. This method needs to give 2 parameters.

range: type name range which you need to get results ( auto dropdown will drop name rage base with you type). I type costExprenses then press Enter
criterion: to get greater than $600 expenses I type comma then >$600 within double quote

How to Name Ranges in Google Sheets-Total of greater than $600 expenses

How to open Existing Named Range

Move your mouse pointer to the top of navigation and click Data > Named Range . You will get your existing build named range on the right side of the spreadsheet.

How to Implement a dynamic named range

When you create a named range with existing data that is called set range (static ranges) that means, you have a start and end row reference. When you deal with existing row references in the name range this will not be affected. But when you enter a new data row into your spreadsheet, you’re defined as a range and don’t know that entry because it is working as a static range.

However, there is a clever trick with the named range you just need to do,

Move mouse top navigation and Click Data > Named ranges. Then will open the existing named range list right side of the google sheet.
Click the pencil icon which you want to create a dynamic named ranges.
Just remove row reference ( keep cell name only) and it will define it as an ad infinitum row reference.

That’s it. now your range with enabling for dynamic changes. It will automatically update when you add or change named range-related data.

When creating a named ranges in google sheet, follow these rules.

Name Holder contains only letters, numbers, and underscores. You cannot add any special character to name in Named Ranges.
Name Holder cannot only contain boolean values such as “true”, “false”.
Name Holder cannot contain any spaces/punctuation or emoji.
Name Holder must be a maximum of 250 characters in Named Range.
Cannot keep row or cell ref name as Name Holder in Named Range such as A1 / A1:G17

Remember This

Google Sheet languages supported by Google Translate. Your Name Ranges Allow adding Name Holder any language which supports by google translate.

#Extra Named Range Tips

How to named ranges with multiple spreadsheets?

in real-world situation is, many users keep data records in multiple sheets. You can use the name range with multiple sheet combinations.

Based on the above example, the expense spreadsheet is split as a monthly base sheet. Each sheet will only represent monthly applicable data. Let’s check how to get the total of all expenses?

How to set up a name range for multiple sheet cells (base on example).

Select cell ( column ) which needs to create name range. Based on the above example Total cell setup as name range. Don’t forget to create 3 named ranges for 3month.
Enter applicable Name for the named range.
Click empty row (B:1) which across Total of all expenses and type =SU then you will auto search sum related formulas then select =SUM() within bracket type your 3 cost-related range name its also auto dropdown and you can easily select your preferred named range. Make sure to separate each range using comma (,)

				
					=SUM(Cost_January,Cost_February,Cost_March)

Please consider that, Although SUM is specified as taking a maximum of 30 arguments,=SUM(maximum 30 arguments)

4.Dynamic range example also applicable with multi-sheets tabs in the spreadsheet

5.Total expenses cost greater than $600

				
					=SUMIF(Cost_January,">600")+SUMIF(Cost_February,">600")+SUMIF(Cost_March,">600")

Conclusion

When you deal with a large data set or multiple datasheets. You cannot remember cell references, formula results, or values. It will reuse, again and again, also it needs to be real-time updated. Named ranges is the type of memory location that keeps data cell reference and it can be re-used for formulas very well. It can be reduced a ton of query time. The most valuable thing is named range enable you to use meaningful names in your formulas without thinking about cell references.