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.