Named ranges in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a named range in Google Sheets?

A named range is a label you give to a specific cell or group of cells. It helps you easily find and use those cells in formulas without remembering cell addresses.

beginner

How do you create a named range in Google Sheets?

Select the cells you want to name, then go to Data > Named ranges, enter a name, and click 'Done'. Now you can use that name in formulas.

beginner

Why use named ranges instead of cell references like A1:B5?

Named ranges make formulas easier to read and understand. For example, using 'SalesData' is clearer than 'A1:B5'. It also helps avoid mistakes if you move cells.

beginner

Can you edit or delete a named range after creating it?

Yes! You can go back to Data > Named ranges, select the named range, and then edit its name or range, or delete it if you no longer need it.

beginner

How do you use a named range in a formula?

Just type the name of the range instead of cell addresses. For example, =SUM(SalesData) adds all numbers in the named range 'SalesData'.

What is the main benefit of using named ranges in Google Sheets?

A Automatically formats cells

B Makes formulas easier to read and manage

C Deletes duplicate data

D Creates charts automatically

Named ranges help you write clearer formulas by using meaningful names instead of cell addresses.

Where do you go to create a named range in Google Sheets?

A Format > Number

B Insert > Chart

C Data > Named ranges

D Tools > Script editor

Named ranges are created under the Data menu by selecting Named ranges.

If you move cells that are part of a named range, what happens to the named range?

A It updates automatically to the new location

B It deletes itself

C It stays fixed on the old cells

D It creates a duplicate named range

Named ranges adjust automatically when you move or insert cells inside the range.

Which of these is a valid named range name?

A A1:B5

B 2024Sales

C Sales Data

D Sales_2024

Named ranges can include letters, numbers, and underscores but cannot start with a number or contain spaces.

How do you use a named range in a formula to add values?

A =SUM(NamedRange)

B =ADD(NamedRange)

C =TOTAL(NamedRange)

D =COUNT(NamedRange)

Use =SUM(Name) to add all numbers in the named range.

Explain what a named range is and how it helps when working with formulas in Google Sheets.

Think about how naming a group of cells is like giving a nickname to a friend.

Describe the steps to create, edit, and delete a named range in Google Sheets.

Remember the menu path and what you do after selecting cells.

Practice

1. What is the main purpose of using named ranges in Google Sheets?

easy

A. To give a simple, memorable name to a group of cells for easier use in formulas

B. To change the color of cells automatically

C. To sort data alphabetically

D. To protect cells from editing

Solution

1. Step 1: Understand what named ranges do

   Named ranges assign a friendly name to a cell or range of cells, making formulas easier to read.

2. Step 2: Compare options to the purpose

   Only To give a simple, memorable name to a group of cells for easier use in formulas describes naming cells for easier formula use; others describe unrelated features.

3. Final Answer:

   To give a simple, memorable name to a group of cells for easier use in formulas -> Option A

4. Quick Check:

   Named ranges = easy formula reference [OK]

Hint: Named ranges simplify formulas by replacing cell addresses [OK]

Common Mistakes:

• Thinking named ranges change cell colors
• Confusing named ranges with sorting or protection

2. Which of the following is the correct way to use a named range called SalesData in a formula to sum its values?

easy

A. =SUM[#SalesData]

B. =SUM('SalesData')

C. =SUM(SalesData)

D. =SUM(SalesData!)

Solution

1. Step 1: Recall named range usage syntax

   Named ranges are used directly by their name without quotes or special characters in formulas.

2. Step 2: Evaluate each option

   =SUM(SalesData) uses =SUM(SalesData), which is correct. Options B, C, and D use invalid syntax with quotes, brackets, or exclamation marks.

3. Final Answer:

   =SUM(SalesData) -> Option C

4. Quick Check:

   Use named range name directly in formulas [OK]

Hint: Use named ranges directly without quotes or symbols [OK]

Common Mistakes:

• Adding quotes around named ranges
• Using brackets or exclamation marks incorrectly

3. Given a named range Prices referring to cells A1:A5 with values {10, 20, 30, 40, 50}, what is the result of the formula =AVERAGE(Prices)?

medium

A. 50

B. 150

C. 10

D. 30

Solution

1. Step 1: Understand the named range values

   The named range Prices contains values 10, 20, 30, 40, and 50.

2. Step 2: Calculate the average of these values

   Sum = 10+20+30+40+50 = 150; Count = 5; Average = 150 / 5 = 30.

3. Final Answer:

   30 -> Option D

4. Quick Check:

   Average of Prices = 30 [OK]

Hint: Sum values then divide by count for average [OK]

Common Mistakes:

• Summing values instead of averaging
• Using wrong range or forgetting count

4. You created a named range Expenses for cells B1:B4. Your formula =SUM(Expense) returns #NAME? instead of the expected sum. What is the likely problem?

medium

A. SUM function does not work with ranges

B. The named range is misspelled in the formula

C. The cells in the named range are empty

D. Named ranges cannot be used in SUM formulas

Solution

1. Step 1: Check formula spelling against named range

   The named range is named Expenses, but the formula uses Expense (missing 's').

2. Step 2: Understand effect of misspelling

   A misspelled named range is undefined, causing a #NAME? error in the formula.

3. Final Answer:

   The named range is misspelled in the formula -> Option B

4. Quick Check:

   Correct spelling of named range needed [OK]

Hint: Check exact spelling of named ranges in formulas [OK]

Common Mistakes:

• Assuming named ranges can't be used in SUM
• Ignoring spelling differences
• Thinking empty cells cause zero sum always

5. You have two named ranges: Income (C1:C3) with values {1000, 1200, 1100} and Expenses (D1:D3) with values {400, 500, 450}. Which formula correctly calculates the net total (Income minus Expenses) for each row and sums the results?

hard

A. =SUMPRODUCT(Income - Expenses)

B. =Income - Expenses

C. =SUM(Income) - Expenses

D. =SUM(Income + Expenses)

Solution

1. Step 1: Understand the goal

   We want to subtract Expenses from Income row by row, then sum all results.

2. Step 2: Evaluate each option

   =Income - Expenses subtracts ranges element-wise but spills an array of differences without summing. =SUMPRODUCT(Income - Expenses) subtracts element-wise and sums to a single value, correct. =SUM(Income) - Expenses subtracts total Income from each Expense, spilling an array, incorrect. =SUM(Income + Expenses) adds instead of subtracts.

3. Step 3: Choose best formula for row-wise net sum

   SUMPRODUCT(Income - Expenses) calculates (1000-400)+(1200-500)+(1100-450)=600+700+650=1950.

4. Final Answer:

   =SUMPRODUCT(Income - Expenses) -> Option A

5. Quick Check:

   Row-wise subtraction then sum = SUMPRODUCT [OK]

Hint: Use SUMPRODUCT for element-wise operations on named ranges [OK]

Common Mistakes:

• Using SUM with range subtraction directly
• Confusing total sums with row-wise sums
• Adding instead of subtracting ranges