Getting data from sources in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to combine sales data from two different sources into one Excel workbook to analyze total sales.

📊 Data: You have two data sources: one Excel file with sales data by product and another CSV file with sales data by region. Each file has columns for Date, Product or Region, and Sales Amount.

🎯 Deliverable: Create a single Excel workbook that imports both data sources into separate sheets and then combines them into a summary table showing total sales by product and region.

Sample Data

Date	Product	Sales Amount
2024-05-01	Shirts	150
2024-05-02	Pants	200
2024-05-03	Hats	100
2024-05-04	Shirts	180

Date	Region	Sales Amount
2024-05-01	North	300
2024-05-02	South	250
2024-05-03	East	400
2024-05-04	West	350

1

Step 1: Open a new Excel workbook and create two sheets named 'Product Sales' and 'Region Sales'.

No formula needed.

Expected Result

Two empty sheets named 'Product Sales' and 'Region Sales' are ready.

2

Step 2: Import the product sales data into the 'Product Sales' sheet.

Go to Data tab > Get Data > From File > From Workbook, select the product sales Excel file, and load the data into 'Product Sales' sheet.

Expected Result

Product sales data with columns Date, Product, Sales Amount appears in 'Product Sales' sheet.

3

Step 3: Import the regional sales data from the CSV file into the 'Region Sales' sheet.

Go to Data tab > Get Data > From File > From Text/CSV, select the regional sales CSV file, and load the data into 'Region Sales' sheet.

Expected Result

Regional sales data with columns Date, Region, Sales Amount appears in 'Region Sales' sheet.

4

Step 4: Create a new sheet named 'Summary' to combine total sales by Product and Region.

No formula needed.

Expected Result

'Summary' sheet is created and ready for formulas.

5

Step 5: In 'Summary' sheet, list unique products in column A starting from A2 and unique regions in column C starting from C2.

Manually type or use UNIQUE function if available.

Expected Result

Column A has products: Shirts, Pants, Hats; Column C has regions: North, South, East, West.

6

Step 6: Calculate total sales for each product from 'Product Sales' sheet using SUMIF.

=SUMIF('Product Sales'!B:B, A2, 'Product Sales'!C:C)

Expected Result

For Shirts in A2, formula returns 330 (150+180); for Pants and Hats, returns 200 and 100 respectively.

7

Step 7: Calculate total sales for each region from 'Region Sales' sheet using SUMIF.

=SUMIF('Region Sales'!B:B, C2, 'Region Sales'!C:C)

Expected Result

For North in C2, formula returns 300; for South, East, West returns 250, 400, 350 respectively.

8

Step 8: Format the 'Summary' sheet with headers: 'Product', 'Product Sales', 'Region', 'Region Sales'.

No formula needed.

Expected Result

Headers appear in row 1: A1='Product', B1='Product Sales', C1='Region', D1='Region Sales'.

Final Result

Summary Sheet:

| Product | Product Sales | Region | Region Sales |
|---------|---------------|--------|--------------|
| Shirts  | 330           | North  | 300          |
| Pants   | 200           | South  | 250          |
| Hats    | 100           | East   | 400          |
|         |               | West   | 350          |

✓Shirts have the highest product sales at 330 units.

✓East region has the highest regional sales at 400 units.

✓Combining data from two sources helps understand sales by product and region.

Bonus Challenge

Create a PivotTable that shows total sales by product and region together.

Show Hint

Use Power Query to merge the two data tables on Date, then create a PivotTable with Product and Region as rows and sum of Sales Amount as values.