Appending queries in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a combined sales report for two different regions to see total sales in one place.

📊 Data: You have two tables: one with sales data from the East region and one from the West region. Each table has columns for Date, Product, and Sales Amount.

🎯 Deliverable: Create a single combined table that appends the East and West region sales data together.

Sample Data

Date	Product	Sales Amount
2024-01-01	Apples	100
2024-01-02	Bananas	150
2024-01-03	Cherries	200

East Region Sales

Date	Product	Sales Amount
2024-01-01	Apples	100
2024-01-02	Bananas	150
2024-01-03	Cherries	200

West Region Sales

Date	Product	Sales Amount
2024-01-01	Apples	120
2024-01-02	Bananas	130
2024-01-04	Dates	180

1

Step 1: Open Excel and create a new worksheet named 'Combined Sales'.

Expected Result

A blank worksheet named 'Combined Sales' is ready.

2

Step 2: Copy the East Region Sales data (Date, Product, Sales Amount) and paste it starting at cell A1 in the 'Combined Sales' sheet.

Expected Result

East Region Sales data appears in cells A1:C4 with headers in row 1.

3

Step 3: Below the East Region data, paste the West Region Sales data starting at cell A5, including headers.

Expected Result

West Region Sales data appears in cells A5:C8, headers repeated in row 5.

4

Step 4: Remove the duplicate headers in row 5 by deleting the cells A5:C5 so only data remains.

Expected Result

Combined data with one header row at A1:C1 and all sales data below without repeated headers.

5

Step 5: Select the combined data range including headers and sales rows, then create an Excel Table by pressing Ctrl+T and confirming the range with 'My table has headers' checked.

Expected Result

Data is formatted as an Excel Table for easy filtering and analysis.

6

Step 6: Add a new column named 'Region' to the right of the Sales Amount column in the table.

In the first cell under 'Region', enter the formula: =IF(ROW()-ROW(Table1[#Headers])<=3,"East","West")

Expected Result

The 'Region' column fills with 'East' for the first 3 rows and 'West' for the next 3 rows.

7

Step 7: Use the combined table to analyze total sales by region or product as needed.

Use a PivotTable: Rows=Region, Columns=Product, Values=SUM of Sales Amount

Expected Result

PivotTable shows total sales for each product by region.

Final Result

Combined Sales Table
+------------+----------+--------------+--------+
| Date       | Product  | Sales Amount | Region |
+------------+----------+--------------+--------+
| 2024-01-01 | Apples   | 100          | East   |
| 2024-01-02 | Bananas  | 150          | East   |
| 2024-01-03 | Cherries | 200          | East   |
| 2024-01-01 | Apples   | 120          | West   |
| 2024-01-02 | Bananas  | 130          | West   |
| 2024-01-04 | Dates    | 180          | West   |
+------------+----------+--------------+--------+

PivotTable Summary
+--------+---------+---------+-------+
| Region | Apples  | Bananas | Dates |
+--------+---------+---------+-------+
| East   | 100     | 150     |       |
| West   | 120     | 130     | 180   |
+--------+---------+---------+-------+

✓East region sold more Cherries but no Dates.

✓West region sold Dates but fewer Bananas than East.

✓Apples sales are similar in both regions.

Bonus Challenge

Create a dynamic combined table using Power Query to automatically append East and West sales data when updated.

Show Hint

Use Excel's Data > Get & Transform > Get Data > Combine Queries > Append to merge tables dynamically.