Referencing other worksheets in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary sheet that shows total sales by product category using data from separate monthly sales worksheets.

📊 Data: You have three worksheets named January, February, and March. Each worksheet lists sales transactions with columns: Product Category, Product Name, and Sales Amount.

🎯 Deliverable: Create a Summary worksheet that shows total sales for each product category across the three months by referencing the other worksheets.

Sample Data

Product Category	Product Name	Sales Amount
January
Electronics	Headphones	120
Clothing	T-Shirt	45
Electronics	Keyboard	75
Home	Coffee Maker	90

Product Category	Product Name	Sales Amount
February
Clothing	Jacket	150
Home	Blender	60
Electronics	Mouse	40
Clothing	Jeans	80

Product Category	Product Name	Sales Amount
March
Home	Toaster	55
Electronics	Monitor	200
Clothing	Socks	30
Home	Vacuum	120

1

Step 1: Create a new worksheet named 'Summary'. In column A, list the unique product categories: Electronics, Clothing, Home.

Manually type in cells A2:A4: Electronics, Clothing, Home

Expected Result

Column A in Summary sheet shows: Electronics, Clothing, Home

2

Step 2: In cell B1 of the Summary sheet, type 'Total Sales'. This will be the header for total sales across all months.

Type 'Total Sales' in cell B1

Expected Result

Cell B1 shows 'Total Sales'

3

Step 3: In cell B2, enter a formula to sum sales for 'Electronics' from January, February, and March sheets using SUMIF with worksheet references.

=SUMIF(January!A:A, A2, January!C:C) + SUMIF(February!A:A, A2, February!C:C) + SUMIF(March!A:A, A2, March!C:C)

Expected Result

Cell B2 shows 435 (120+75+40+200)

4

Step 4: Copy the formula from B2 down to B3 and B4 to calculate total sales for 'Clothing' and 'Home' categories.

Copy formula in B2 to B3 and B4

Expected Result

Cell B3 shows 305 (45+150+80+30), Cell B4 shows 325 (90+60+55+120)

5

Step 5: Format the Summary sheet header row with bold text and center alignment for clarity.

Select row 1, apply bold and center alignment

Expected Result

Header row is bold and centered

Final Result

Summary Worksheet

| Product Category | Total Sales |
|------------------|-------------|
| Electronics      | 435         |
| Clothing         | 305         |
| Home             | 325         |

✓Electronics category had the highest total sales of 435.

✓Clothing sales totaled 305 across three months.

✓Home category sales were 325 in total.

Bonus Challenge

Add columns to the Summary sheet to show monthly sales separately for January, February, and March by referencing the respective worksheets.

Show Hint

Use SUMIF formulas similar to step 3 but only for one month per column.

Practice

1. Which of the following is the correct way to reference cell A1 from a worksheet named Sales in another worksheet?

easy

A. =Sales:A1

B. =A1!Sales

C. =Sales!A1

D. =Sheet1.A1

Solution

1. Step 1: Understand worksheet referencing syntax

   In Excel, to reference a cell from another worksheet, use SheetName!CellAddress.

2. Step 2: Apply the syntax to the given worksheet and cell

   For worksheet Sales and cell A1, the correct reference is =Sales!A1.

3. Final Answer:

   =Sales!A1 -> Option C

4. Quick Check:

   SheetName!Cell = =Sales!A1 [OK]

Hint: Use SheetName!Cell to reference another sheet [OK]

Common Mistakes:

• Putting cell before sheet name
• Using colon instead of exclamation mark
• Using dot notation like Sheet1.A1

2. Which formula correctly references cell B2 from a worksheet named Annual Report (note the space in the name)?

easy

A. ='Annual Report'!B2

B. =AnnualReport!B2

C. =Annual_Report!B2

D. =Annual Report!B2

Solution

1. Step 1: Recognize the need for quotes with spaces in sheet names

   When a worksheet name contains spaces, it must be enclosed in single quotes in formulas.

2. Step 2: Apply correct syntax for referencing cell B2

   The correct reference is ='Annual Report'!B2 with single quotes around the sheet name.

3. Final Answer:

   ='Annual Report'!B2 -> Option A

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Put single quotes around sheet names with spaces [OK]

Common Mistakes:

• Omitting quotes around sheet names with spaces
• Replacing spaces with underscores incorrectly
• Using double quotes instead of single quotes

3. Given two worksheets: Data and Summary. Cell A1 in Data contains the number 10. What will be the result in cell B1 of Summary if the formula =Data!A1 * 2 is entered?

medium

A. 10

B. 20

C. #REF!

D. Data!A1 * 2

Solution

1. Step 1: Identify the value in Data!A1

   Cell A1 in the Data sheet contains the number 10.

2. Step 2: Calculate the formula in Summary!B1

   The formula multiplies the value from Data!A1 by 2, so 10 * 2 = 20.

3. Final Answer:

   20 -> Option B

4. Quick Check:

   10 * 2 = 20 [OK]

Hint: Multiply referenced cell value directly [OK]

Common Mistakes:

• Expecting the formula text as output
• Getting #REF! error due to wrong sheet name
• Not multiplying the value, just copying it

4. You want to reference cell C3 from a worksheet named 2023 Sales, but your formula =2023 Sales!C3 returns a #NAME? error. What is the correct fix?

medium

A. Change formula to =2023Sales!C3

B. Change formula to =2023 Sales C3

C. Change formula to =2023_Sales!C3

D. Change formula to ='2023 Sales'!C3

Solution

1. Step 1: Identify cause of #NAME? error

   The sheet name contains a space, so Excel treats 2023 as a number and Sales as an unknown name, causing the error.

2. Step 2: Fix formula by adding single quotes around sheet name

   Enclose the sheet name with spaces in single quotes: ='2023 Sales'!C3.

3. Final Answer:

   ='2023 Sales'!C3 -> Option D

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Add single quotes around sheet names with spaces [OK]

Common Mistakes:

• Removing spaces without quotes
• Using underscores instead of spaces
• Omitting quotes causing #NAME? error

5. You have a workbook with sheets named Jan, Feb, and Mar. Each sheet has sales data in cell B5. In a summary sheet, which formula correctly sums the sales from all three months?

hard

A. =SUM(Jan:Mar!B5)

B. =Jan!B5 + Feb!B5 + Mar

C. =SUM('Jan:Mar'!B5)

D. =SUM(Jan!B5:Mar!B5)

Solution

1. Step 1: Understand 3D referencing in Excel

   Excel allows summing the same cell across multiple sheets using Sheet1:Sheet3!Cell syntax.

2. Step 2: Apply 3D sum for sheets Jan to Mar

   The formula =SUM(Jan:Mar!B5) sums cell B5 across all sheets from Jan through Mar.

3. Final Answer:

   =SUM(Jan:Mar!B5) -> Option A

4. Quick Check:

   3D sum uses Sheet1:SheetN!Cell [OK]

Hint: Use SUM(Sheet1:SheetN!Cell) to sum across sheets [OK]

Common Mistakes:

• Trying to sum with plus signs for many sheets
• Putting sheet names in quotes with colon
• Using range notation for cells across sheets incorrectly