Named ranges in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clear monthly sales report that uses named ranges to make formulas easier to read and maintain.

📊 Data: You have a table with sales data including Month, Region, and Sales Amount for the first half of the year.

🎯 Deliverable: Create a summary table showing total sales per region using named ranges in your formulas.

Sample Data

Month	Region	Sales Amount
January	North	1200
January	South	900
February	North	1500
February	South	1100
March	North	1300
March	South	1000
April	North	1600
April	South	1200
May	North	1700
May	South	1300
June	North	1800
June	South	1400

1

Step 1: Select the range of cells containing the Month data (A2:A13) and create a named range called 'Months'.

Use Data > Named ranges in Google Sheets and name the range A2:A13 as 'Months'.

Expected Result

Named range 'Months' refers to cells A2:A13.

2

Step 2: Select the range of cells containing the Region data (B2:B13) and create a named range called 'Regions'.

Use Data > Named ranges and name the range B2:B13 as 'Regions'.

Expected Result

Named range 'Regions' refers to cells B2:B13.

3

Step 3: Select the range of cells containing the Sales Amount data (C2:C13) and create a named range called 'Sales'.

Use Data > Named ranges and name the range C2:C13 as 'Sales'.

Expected Result

Named range 'Sales' refers to cells C2:C13.

4

Step 4: Create a summary table with two columns: Region and Total Sales. List 'North' and 'South' in the Region column starting at E2.

Enter 'North' in E2 and 'South' in E3.

Expected Result

Summary table Region column has 'North' in E2 and 'South' in E3.

5

Step 5: In cell F2, enter a formula to calculate total sales for the 'North' region using named ranges.

=SUMIF(Regions, E2, Sales)

Expected Result

Cell F2 shows 9100, the total sales for North region.

6

Step 6: Copy the formula from F2 to F3 to calculate total sales for the 'South' region.

Copy =SUMIF(Regions, E2, Sales) from F2 to F3 (adjusts to E3 automatically).

Expected Result

Cell F3 shows 6900, the total sales for South region.

7

Step 7: Label the summary table columns: enter 'Region' in E1 and 'Total Sales' in F1.

Type 'Region' in E1 and 'Total Sales' in F1.

Expected Result

Summary table headers are set.

Final Result

Region   | Total Sales
---------------------
North    | 9100
South    | 6900

✓The North region has total sales of 9100 for the first half of the year.

✓The South region has total sales of 6900 for the same period.

✓Using named ranges makes formulas easier to read and update.

Bonus Challenge

Create a named range for the summary table and use it in a chart to visualize sales by region.

Show Hint

Name the summary table range (E1:F3) as 'SummaryTable' and insert a chart using this named range.

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