Consolidating data from multiple sheets in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a consolidated sales report that combines monthly sales data from three different regional sheets into one summary sheet.

📊 Data: You have three sheets named North, South, and West. Each sheet contains sales data with columns: Date, Product, Units Sold, and Revenue.

🎯 Deliverable: Create a summary sheet that lists all sales records from the three regions combined, with an additional column showing the region name.

Sample Data

Date	Product	Units Sold	Revenue
2024-01-05	Widget A	10	200
2024-01-10	Widget B	5	150
2024-01-15	Widget C	8	240

1

Step 1: Go to the summary sheet where you want to consolidate data.

Select cell A2 to start the consolidated list.

Expected Result

Ready to enter formulas to pull data.

2

Step 2: Use the FILTER function to pull all data from the North sheet and add a column with the region name.

=LET(nData, FILTER(North!A2:D100, North!A2:A100<>""), nRows, ROWS(nData), HSTACK(nData, MAKEARRAY(nRows,1,LAMBDA(r,c,"North"))))

Expected Result

All rows from North sheet with an extra column showing 'North'.

3

Step 3: Use the FILTER function similarly for South sheet with region name 'South'.

=LET(sData, FILTER(South!A2:D100, South!A2:A100<>""), sRows, ROWS(sData), HSTACK(sData, MAKEARRAY(sRows,1,LAMBDA(r,c,"South"))))

Expected Result

All rows from South sheet with an extra column showing 'South'.

4

Step 4: Use the FILTER function similarly for West sheet with region name 'West'.

=LET(wData, FILTER(West!A2:D100, West!A2:A100<>""), wRows, ROWS(wData), HSTACK(wData, MAKEARRAY(wRows,1,LAMBDA(r,c,"West"))))

Expected Result

All rows from West sheet with an extra column showing 'West'.

5

Step 5: Combine all three region data arrays vertically using VSTACK.

=VSTACK(LET(nData, FILTER(North!A2:D100, North!A2:A100<>""), nRows, ROWS(nData), HSTACK(nData, MAKEARRAY(nRows,1,LAMBDA(r,c,"North")))), LET(sData, FILTER(South!A2:D100, South!A2:A100<>""), sRows, ROWS(sData), HSTACK(sData, MAKEARRAY(sRows,1,LAMBDA(r,c,"South")))), LET(wData, FILTER(West!A2:D100, West!A2:A100<>""), wRows, ROWS(wData), HSTACK(wData, MAKEARRAY(wRows,1,LAMBDA(r,c,"West"))))) )

Expected Result

A combined list of all sales records from North, South, and West sheets with a region column.

6

Step 6: Add headers in row 1 of the summary sheet: Date, Product, Units Sold, Revenue, Region.

Manually type headers in cells A1:E1.

Expected Result

Headers clearly label each column in the summary sheet.

7

Step 7: Check that the combined data updates automatically when you add new rows in any regional sheet.

Add new sales data in any regional sheet within the range A2:D100.

Expected Result

Summary sheet automatically shows the new data with correct region.

Final Result

Date       | Product  | Units Sold | Revenue | Region
-------------------------------------------------------
2024-01-05 | Widget A | 10        | 200     | North
2024-01-10 | Widget B | 5         | 150     | North
2024-01-15 | Widget C | 8         | 240     | North
2024-01-07 | Widget A | 7         | 140     | South
2024-01-12 | Widget B | 6         | 180     | South
2024-01-20 | Widget C | 9         | 270     | South
2024-01-08 | Widget A | 12        | 240     | West
2024-01-14 | Widget B | 4         | 120     | West
2024-01-18 | Widget C | 10        | 300     | West

✓You can see all sales data from three regions in one place.

✓The Region column helps identify where each sale happened.

✓Adding new data in any region sheet updates the summary automatically.

Bonus Challenge

Create a pivot table from the consolidated data to show total revenue by region and product.

Show Hint

Use the summary sheet as the data source. Set Rows to Region and Product, Values to SUM of Revenue.

Practice

1. What is the main purpose of consolidating data from multiple sheets in Excel?

easy

A. To combine data from different sheets into one summary

B. To delete duplicate sheets automatically

C. To create charts from a single sheet only

D. To protect sheets with passwords

Solution

1. Step 1: Understand consolidation concept

   Consolidation means combining data from multiple sheets to get a summary.

2. Step 2: Identify the purpose

   It helps to see combined totals or summaries in one place.

3. Final Answer:

   To combine data from different sheets into one summary -> Option A

4. Quick Check:

   Consolidate = Combine data [OK]

Hint: Think 'combine' when you hear consolidate [OK]

Common Mistakes:

• Confusing consolidation with deleting sheets
• Thinking it only creates charts
• Assuming it protects sheets

2. Which formula correctly sums cell A1 from sheets named Sheet1, Sheet2, and Sheet3?

easy

A. =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1)

B. =SUM(A1:Sheet3!A1)

C. =SUM(Sheet1:A1, Sheet2:A1, Sheet3:A1)

D. =SUM(Sheet1!A1:A3)

Solution

1. Step 1: Understand sheet references in formulas

   To sum cells from different sheets, use sheetname!cell syntax separated by commas.

2. Step 2: Check each option

   =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1) correctly sums A1 from each sheet individually.

3. Final Answer:

   =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1) -> Option A

4. Quick Check:

   SUM(sheet!cell, ...) = correct [OK]

Hint: Use sheetname!cell for cross-sheet references [OK]

Common Mistakes:

• Using range syntax across sheets incorrectly
• Missing exclamation mark after sheet name
• Trying to sum ranges that don't exist

3. Given Sheet1!A1=10, Sheet2!A1=20, Sheet3!A1=30, what is the result of =SUM(Sheet1!A1:Sheet3!A1)?

medium

A. 60

B. 10

C. Syntax Error

D. #REF! error

Solution

1. Step 1: Understand range syntax across sheets

   Excel does not allow ranges like Sheet1!A1:Sheet3!A1 across multiple sheets in SUM.

2. Step 2: Identify the error

   This causes a #REF! error because the range is invalid.

3. Final Answer:

   #REF! error -> Option D

4. Quick Check:

   Invalid cross-sheet range = #REF! error [OK]

Hint: Cross-sheet ranges like Sheet1!A1:Sheet3!A1 cause errors [OK]

Common Mistakes:

• Assuming cross-sheet ranges work like normal ranges
• Expecting sum of all values without correct syntax
• Ignoring error messages

4. You want to sum cell B2 from sheets Jan, Feb, and Mar using =SUM(Jan!B2, Feb!B2, Mar!B2), but it returns 0. What is the likely problem?

medium

A. Formula syntax is incorrect

B. Sheet names are misspelled or missing

C. Cells B2 are empty in all sheets

D. SUM function cannot add multiple sheets

Solution

1. Step 1: Check sheet names in formula

   If sheet names are misspelled or do not exist, Excel treats references as zero.

2. Step 2: Verify cell contents

   If cells have values but formula returns 0, likely sheet names are wrong.

3. Final Answer:

   Sheet names are misspelled or missing -> Option B

4. Quick Check:

   Wrong sheet names = zero result [OK]

Hint: Double-check sheet names spelling in formulas [OK]

Common Mistakes:

• Assuming syntax error instead of name error
• Not verifying cell contents
• Thinking SUM can't add multiple sheets

5. You have monthly sales data in sheets named Jan, Feb, Mar in cell C5. You want a summary sheet cell A1 to show total sales for these months. Which formula correctly consolidates this data?

hard

A. =SUM(Jan:C5, Feb:C5, Mar:C5)

B. =SUM('Jan:Mar'!C5)

C. =SUM(Jan:Mar!C5)

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

Solution

1. Step 1: Understand 3D reference syntax

   Using Jan:Mar!C5 sums cell C5 across all sheets from Jan through Mar.

2. Step 2: Check each option

   =SUM(Jan:Mar!C5) uses correct 3D reference syntax without quotes.

3. Final Answer:

   =SUM(Jan:Mar!C5) -> Option C

4. Quick Check:

   3D reference = SUM(Jan:Mar!C5) [OK]

Hint: Use 3D references like Jan:Mar!Cell to sum across sheets [OK]

Common Mistakes:

• Adding quotes around sheet range
• Using invalid range syntax across sheets
• Trying to sum ranges instead of single cells