Named ranges in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This sheet lists products and their prices in columns A and B. Column D shows quantities bought. The named range 'Prices' refers to B2:B4.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
B1	Price
B2	1.2
B3	0.5
B4	2.0
D1	Quantity
D2	3
D3	5
D4	2

Formula Trace

=SUMPRODUCT(Prices, D2:D4)

Step 1: Prices

Step 2: D2:D4

Step 3: SUMPRODUCT([1.2, 0.5, 2.0], [3, 5, 2])

Cell Reference Map

    A       B       C       D
1 |Product| Price |       |Quantity
2 | Apple |  1.2  |       |   3    
3 |Banana |  0.5  |       |   5    
4 |Cherry |  2.0  |       |   2    

Named Range 'Prices' -> B2:B4
Formula references 'Prices' and D2:D4

The formula uses the named range 'Prices' which points to B2:B4 and the range D2:D4 for quantities.

Result

    A       B       C       D       E
1 |Product| Price |       |Quantity| 
2 | Apple |  1.2  |       |   3    | 
3 |Banana |  0.5  |       |   5    | 
4 |Cherry |  2.0  |       |   2    | 
5 |       |       |       |        | 10.1

Cell E5 shows the formula result: 10.1

The total cost calculated by the formula =SUMPRODUCT(Prices, D2:D4) is 10.1, shown in cell E5.

Sheet Trace Quiz - 3 Questions

What cells does the named range 'Prices' refer to?

A A2:A4

B D2:D4

C B2:B4

D B1:B3

Key Result

Named ranges can be used in formulas like ranges; here SUMPRODUCT multiplies corresponding elements and sums them.

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