Loading to worksheet or data model in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to load sales data into Excel and decide whether to load it into a worksheet or the data model for better analysis.

📊 Data: You have a sales data table with columns: Date, Region, Product, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a workbook where the sales data is loaded properly, showing how to load to worksheet and how to load to data model, with a simple pivot table from the data model.

Sample Data

Date	Region	Product	Quantity Sold	Sales Amount
2024-01-01	North	Apples	10	50
2024-01-02	South	Bananas	15	45
2024-01-03	East	Oranges	8	40
2024-01-04	West	Apples	12	60
2024-01-05	North	Bananas	20	60
2024-01-06	South	Oranges	5	25
2024-01-07	East	Apples	7	35
2024-01-08	West	Bananas	10	30

1

Step 1: Open Excel and import the sales data from your source (e.g., CSV or database). Choose to load the data into a worksheet.

In the import wizard, select 'Load to Worksheet'.

Expected Result

Sales data appears as a table in a new worksheet.

2

Step 2: Import the same sales data again, but this time choose to load it into the Data Model.

In the import wizard, select 'Load to Data Model' only (do not load to worksheet).

Expected Result

Data is loaded into Excel's Data Model without appearing in a worksheet.

3

Step 3: Create a PivotTable using the data from the Data Model to analyze total Sales Amount by Region.

Insert > PivotTable > Use this workbook's Data Model > Rows: Region, Values: Sum of Sales Amount

Expected Result

PivotTable shows total sales amount for each region.

4

Step 4: Explain the difference: Loading to worksheet shows raw data directly, loading to Data Model allows advanced analysis with large data and relationships.

No formula needed; explanation step.

Expected Result

You understand when to use worksheet load (small data, simple view) and when to use Data Model (large data, complex analysis).

Final Result

Sales Data Worksheet:
+------------+--------+---------+---------------+--------------+
| Date       | Region | Product | Quantity Sold | Sales Amount |
+------------+--------+---------+---------------+--------------+
| 2024-01-01 | North  | Apples  | 10            | 50           |
| ...        | ...    | ...     | ...           | ...          |
+------------+--------+---------+---------------+--------------+

PivotTable from Data Model:
+--------+----------------+
| Region | Sum of Sales   |
+--------+----------------+
| North  | 110            |
| South  | 70             |
| East   | 75             |
| West   | 90             |
+--------+----------------+

✓Loading data to worksheet shows raw data for direct viewing.

✓Loading data to Data Model supports creating PivotTables without cluttering worksheets.

✓Data Model is better for large datasets and complex analysis.

✓PivotTable from Data Model quickly summarizes sales by region.

Bonus Challenge

Load multiple related tables (e.g., Sales and Products) into the Data Model and create a PivotTable that shows sales by product category.

Show Hint

Use Power Query to load both tables into the Data Model and create a relationship between them on the Product column.