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.