Bird
Raised Fist0
Excelspreadsheet~15 mins

Getting data from sources in Excel - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 8 steps
Sample Data
DateProductSales Amount
2024-05-01Shirts150
2024-05-02Pants200
2024-05-03Hats100
2024-05-04Shirts180

DateRegionSales Amount
2024-05-01North300
2024-05-02South250
2024-05-03East400
2024-05-04West350
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.

Practice

(1/5)
1. Which Excel feature allows you to import data from an external file like a CSV or another workbook?
easy
A. Create a Pivot Table
B. Use the SUM function
C. Apply Conditional Formatting
D. Get Data from the Data tab

Solution

  1. Step 1: Identify the feature for importing data

    Excel's Data tab includes a 'Get Data' option specifically for importing external data files.

  2. Step 2: Understand other options

    SUM adds numbers, Conditional Formatting changes cell colors, and Pivot Tables summarize data, none import files.

  3. Final Answer:

    Get Data from the Data tab -> Option D

  4. Quick Check:

    Import external data = Get Data [OK]
Hint: Look for 'Get Data' under the Data tab to import files [OK]
Common Mistakes:
  • Confusing SUM with data import
  • Thinking Pivot Tables import data
  • Using Conditional Formatting for importing
2. Which of these is the correct way to refresh imported data in Excel to get the latest updates?
easy
A. Press Ctrl + C to copy the data
B. Use the CONCATENATE function
C. Right-click the imported table and select Refresh
D. Delete and re-import the file manually

Solution

  1. Step 1: Identify how to update imported data

    Right-clicking the imported data and choosing Refresh updates it with the latest source changes.

  2. Step 2: Understand other options

    Ctrl + C copies data, CONCATENATE joins text, and deleting then re-importing is inefficient and unnecessary.

  3. Final Answer:

    Right-click the imported table and select Refresh -> Option C

  4. Quick Check:

    Refresh data = Right-click and Refresh [OK]
Hint: Right-click imported data and pick Refresh to update [OK]
Common Mistakes:
  • Trying to copy data to refresh
  • Using text functions like CONCATENATE
  • Deleting data instead of refreshing
3. You imported a table from a web page using Get Data. After refreshing, which of these will happen if the web page data changed?
medium
A. The Excel table updates to show the new data
B. Excel shows an error and deletes the table
C. The table stays the same and does not change
D. Excel automatically saves a backup copy

Solution

  1. Step 1: Understand the refresh behavior

    Refreshing imported data updates the Excel table to match the current source data, including any changes.

  2. Step 2: Consider other options

    Excel does not delete the table or show errors just because data changed; it also does not auto-save backups on refresh.

  3. Final Answer:

    The Excel table updates to show the new data -> Option A

  4. Quick Check:

    Refresh updates data = Table changes [OK]
Hint: Refresh updates your table with new source data [OK]
Common Mistakes:
  • Thinking refresh deletes data
  • Assuming no change after refresh
  • Expecting automatic backups on refresh
4. You tried to import data from a CSV file using Get Data, but the data looks wrong with extra columns. What is the likely cause?
medium
A. The delimiter setting is incorrect during import
B. The CSV file is empty
C. You forgot to save the Excel workbook
D. The SUM function was applied to the data

Solution

  1. Step 1: Identify cause of extra columns

    CSV files use delimiters like commas or semicolons; if the wrong delimiter is chosen, data splits incorrectly into extra columns.

  2. Step 2: Rule out other causes

    An empty CSV shows no data, saving Excel doesn't affect import, and SUM function does not change import layout.

  3. Final Answer:

    The delimiter setting is incorrect during import -> Option A

  4. Quick Check:

    Wrong delimiter = extra columns [OK]
Hint: Check delimiter settings if columns split oddly [OK]
Common Mistakes:
  • Assuming file is empty without checking
  • Confusing saving workbook with import issues
  • Applying functions before fixing import
5. You want to combine data from two different Excel workbooks into one table that updates automatically when either source changes. Which method should you use?
hard
A. Copy and paste data manually from both workbooks
B. Use Get Data to import both tables and create a query to merge them
C. Use VLOOKUP with hardcoded file paths
D. Save both files as CSV and open them separately

Solution

  1. Step 1: Identify method for combining and auto-updating data

    Get Data allows importing tables and merging them with queries that refresh automatically when sources change.

  2. Step 2: Evaluate other options

    Copy-paste is manual and not automatic; VLOOKUP with hardcoded paths is fragile and not ideal; saving as CSV and opening separately does not combine data.

  3. Final Answer:

    Use Get Data to import both tables and create a query to merge them -> Option B

  4. Quick Check:

    Combine and auto-update = Get Data with merge query [OK]
Hint: Merge tables with Get Data queries for auto updates [OK]
Common Mistakes:
  • Relying on manual copy-paste
  • Using fragile VLOOKUP with file paths
  • Opening CSVs separately without merging
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard