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.