Using Named Ranges in Google Sheets

Published
9 April 2019
By Adrian Francis

If you have ever tried to either write or copy a formula that you have written in Google Sheets only to find that the cell references get messed up, then I feel your pain. The other issue you might stumble across is having to highlight or refer to a large set of cells and have to type in the references repeatedly. If this is you, then using a named range in Google Sheets may be the answer.

[bctt tweet=”Using a named range in #GoogleSheets will help you create functional spreadsheets.” username=”adifrancis”]

The issue of having errors arise when copying formulas is common and this is due to having a relative reference for your formulas, rather than an absolute reference. Basically, the difference between relative and absolute references is that a relative cell reference moves when you copy them, but absolute references don’t. This blog post explains why it happens and how to fix it.

Using a named range is another way of addressing this issue as well as giving you a heap more options when referring to data in your spreadsheet. A named range allows you to allocate a name for a range of cells and then simply refer to those cells by typing in the name you have assigned.

Here you can see a simple sheet that finds some information about a task that was completed (this is a very small example, however it will still illustrate the point!). You will notice that in every formula I had to enter the cell references. If I decide to create a summary sheet that had a similar calculation, then I will have to go back and get the appropriate reference from my marks sheet. Named ranges can be used in any sheet and alleviate these issues.

Follow these three steps to set up a named range.

Step 1

Highlight the cells that you want to include in the range, in the example, they are C2:C6.

Step 2

Click on the Data tab and go to Named Ranges and a working column will open on the right-hand side of your spreadsheet.

Step 3

Check that the correct cells are being used and then type in a name for this range. Try to make it something that will make sense to anyone using the spreadsheet.

When you wish to carry out calculations using that range of data you can use the name you just applied to the range. So if I wanted to work out the average of Task 2 you would enter =average(Task2).

You can refer to that range in any sheet. In my summary sheet to display the average mark for Task 2, I can use the same formula.

[bctt tweet=”Using named ranges helps you use a spreadsheet efficiently and allows you to manage large data sets easily #googlesheets.” username=”adifrancis”]

This becomes really handy when writing a query, or when you have a large lookup table or when you are working with a large set of data. Check out this blog post to see how a named range has been combined with the Google Translate function.

A named range can alleviate the angst caused when you copy formulas and want to refer to data from one sheet in another. If you want to learn more ways of using the G Suite tools, then join our online professional learning community.