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.
Contents
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
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 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 (,)
4.Dynamic range example also applicable with multi-sheets tabs in the spreadsheet
5.Total expenses cost greater than $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.
