Using Named Ranges in Google Sheets

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.

Using a named range in #GoogleSheets will help you create functional spreadsheets. Click To Tweet    

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.

Using named ranges helps you use a spreadsheet efficiently and allows you to manage large data sets easily #googlesheets. Click To Tweet    

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.

Digitally Certified_2

6 reasons why you should become digitally certified

One of the most important tools we have in the modern classroom is a digital one. We use laptops and tablets for communication, creation, assessment and probably most importantly, learning. And it’s no wonder that for teachers, one of the important parts of our roles as educators is to know

VIEW POST

Have fun with the Emoji Keyboard in Microsoft Word

Teaching is a serious vocation – we know that. But we also know it’s important to have some fun with your students. Using the Emoji Keyboard in Microsoft Word can be a great way to engage in the writing process, among other things, and bring lots of smiles to everyone’s faces.

VIEW POST

Pre-filled links in Google Forms – make sorting and filtering a breeze!

Google Forms are an awesome tool for gathering information from groups of people. One issue with Google Forms can be the way that users enter information – a slight difference in punctuation or spelling will mean that your ability to sort and filter the results in Google Sheets can be impacted. This blog will show you how to use a ‘Pre-filled link’ to stop this.

VIEW POST
Outclassed Podcast

Fake News and Getting Google Search to Work For You Ep 27

Get Notified Of Future Episodes: Apple Podcasts | Spotify | Google Podcast | Stitcher | TuneIn + Alex | Podcast Addict | Podchaser | Deezer | Listen Notes In this Episode: To see all the OutClassed episodes go to utb.fyi/outclassed Podcast Episode Highlights: Coming Resources and links mentioned: Transcript: Mike

VIEW POST

Here’s What
Our Clients Say

PR Industrya

Client

In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...

 

Sign Up Now
To Get The Latest Updates

 

Holiday Rescue Maker Camp

Student to agree to and tick:
Guardian to agree to and tick: