Getting data from sources in Excel - Deep Dive

Overview - Getting data from sources

What is it?

Getting data from sources means bringing information from outside your Excel workbook into your spreadsheet. This can include files like other Excel sheets, text files, databases, or online data. It helps you work with fresh and relevant data without typing it all manually. This process saves time and reduces mistakes.

Why it matters

Without the ability to get data from sources, you would have to enter all data by hand, which is slow and error-prone. It would be hard to keep your spreadsheets updated with new information. Getting data automatically means your work is more accurate and you can focus on analyzing rather than copying data.

Where it fits

Before learning this, you should know basic Excel skills like entering data and simple formulas. After this, you can learn how to clean and transform data, use formulas to analyze it, and create reports or dashboards.

Mental Model

Core Idea

Getting data from sources is like opening a door to bring fresh information into your spreadsheet so you can work with it easily.

Think of it like...

Imagine your spreadsheet is a kitchen, and getting data from sources is like opening the fridge or pantry to bring ingredients before cooking. You don’t make the ingredients yourself; you just bring them in to prepare your meal.

┌───────────────┐
│ External Data │
│ (Files, DBs,  │
│  Web, etc.)   │
└──────┬────────┘
       │ Import
       ▼
┌───────────────┐
│ Excel Workbook│
│  (Your Sheet) │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding External Data Sources

Concept: Learn what external data sources are and the types Excel can connect to.

External data sources are places outside your Excel file where data lives. Examples include other Excel files, CSV text files, databases like Access or SQL Server, and online sources like websites or APIs. Excel can connect to these to bring data inside your workbook.

Result

You know the kinds of data sources Excel can get data from and why you might want to use them.

Knowing what external data sources are helps you understand where your data can come from and why Excel supports many types.

2

Foundation

Using Excel’s Get & Transform Feature

Concept: Learn the basic tool in Excel to import and shape data called Get & Transform (Power Query).

Excel has a tool called Get & Transform that helps you connect to data sources, preview data, and clean or change it before loading it into your sheet. You find it under the Data tab as 'Get Data'. You can choose your source type and follow simple steps to bring data in.

Result

You can open the Get Data menu and start importing data from a file or web page.

Understanding Get & Transform is key because it’s the main way Excel handles importing and preparing data.

3

Intermediate

Importing Data from Excel and Text Files

🤔Before reading on: do you think importing from Excel files and text files uses the same steps or different steps? Commit to your answer.

Concept: Learn how to import data from other Excel workbooks and text files like CSV or TXT.

To import from another Excel file, use Data > Get Data > From File > From Workbook. Select the file, pick the sheet or table, and load it. For text files, choose Data > Get Data > From File > From Text/CSV, then select the file and Excel shows a preview. You can adjust delimiters (like commas or tabs) before loading.

Result

You can bring data from other Excel files or text files into your current workbook as a table.

Knowing how to import from common file types lets you combine data from different places easily.

4

Intermediate

Connecting to Online and Database Sources

🤔Before reading on: do you think connecting to online sources and databases is simpler or more complex than files? Commit to your answer.

Concept: Learn how to connect Excel to online data like web pages and databases like SQL Server.

For web data, use Data > Get Data > From Other Sources > From Web. Enter the web page URL, and Excel tries to find tables to import. For databases, choose Data > Get Data > From Database, then pick your database type. You may need server details and login info. Excel then shows tables you can import.

Result

You can pull live data from websites or databases directly into Excel.

Connecting to live sources means your data can update automatically, keeping your work current.

5

Intermediate

Refreshing and Managing Imported Data

Concept: Learn how to update imported data and manage connections in Excel.

Once data is imported, it stays static until you refresh it. Use the Refresh button on the Data tab to update data from the source. You can also set automatic refresh options. The Queries & Connections pane shows all your data links, letting you edit or delete them.

Result

You can keep your imported data up-to-date without re-importing manually.

Knowing how to refresh and manage connections saves time and prevents working with old data.

6

Advanced

Transforming Data During Import

🤔Before reading on: do you think you can change data while importing or only after it’s in Excel? Commit to your answer.

Concept: Learn how to clean and reshape data as you import it using Power Query’s tools.

Power Query lets you filter rows, remove columns, change data types, split columns, and more before loading data. For example, you can remove empty rows or combine columns. These steps are saved and run every time you refresh, so your data stays clean automatically.

Result

Imported data is ready to use without extra manual cleanup.

Transforming data during import automates repetitive cleanup tasks and ensures consistent data quality.

7

Expert

Advanced Data Source Integration and Automation

🤔Before reading on: do you think Excel can connect to APIs or automate complex data workflows? Commit to your answer.

Concept: Explore how Excel can connect to APIs, use parameters, and automate data workflows for advanced scenarios.

Excel’s Power Query can connect to web APIs by entering URLs with parameters, allowing dynamic data retrieval. You can create queries that depend on user input or other queries. Combining multiple data sources and automating refresh schedules enables complex, up-to-date reports without manual effort.

Result

You can build powerful, automated data solutions inside Excel that pull from many sources and update on demand.

Mastering advanced integration turns Excel into a dynamic data hub, reducing manual work and errors in professional settings.

Under the Hood

When you import data, Excel creates a query that connects to the source using a protocol like OLE DB, ODBC, or web requests. Power Query engine runs this query, fetches data, and applies any transformations you set. The result is loaded into your workbook as a table or data model. Refreshing reruns the query to get updated data.

Why designed this way?

Excel was designed to separate data retrieval from data storage to keep workbooks efficient and flexible. Using queries allows users to connect many data types without manual copying. Power Query was introduced to provide a user-friendly way to clean and shape data during import, replacing older, manual methods.

┌───────────────┐       ┌───────────────┐
│ External Data │──────▶│ Power Query   │
│ Source (File,│       │ Engine applies │
│ DB, Web)     │       │ transformations│
└───────────────┘       └──────┬────────┘
                                   │
                                   ▼
                          ┌───────────────┐
                          │ Excel Table / │
                          │ Data Model    │
                          └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does importing data into Excel create a live link that updates automatically without any action? Commit to yes or no.

Common Belief: Importing data means it will always update automatically without any user action.

Reality: Imported data stays static until you manually refresh it or set up automatic refresh schedules.

Why it matters: If you assume data updates automatically, you might analyze outdated information leading to wrong decisions.

Quick: Do you think you must have advanced coding skills to import data from external sources in Excel? Commit to yes or no.

Common Belief: Importing data from external sources requires programming or complex coding knowledge.

Reality: Excel’s Get & Transform provides easy, mostly point-and-click tools to import and shape data without coding.

Why it matters: Believing you need coding skills can stop beginners from using powerful data import features that are actually accessible.

Quick: When importing data from a web page, do you think Excel always grabs the entire page content? Commit to yes or no.

Common Belief: Excel imports the whole web page exactly as it appears in a browser.

Reality: Excel tries to detect tables or structured data on the page, not the full page content or layout.

Why it matters: Expecting the full page can cause confusion when only tables or lists appear, leading to frustration or wrong data.

Quick: Do you think you can edit imported data directly in the Excel table and have it change the original source? Commit to yes or no.

Common Belief: Editing data in Excel after import changes the original external data source.

Reality: Changes in Excel do not affect the original source; the source remains unchanged unless edited separately.

Why it matters: Assuming edits sync back can cause data integrity issues and confusion about where the true data lives.

Expert Zone

1

Power Query steps are recorded as a script in the M language, which can be edited for advanced transformations beyond the GUI.

2

Data source privacy levels in Excel control how data from different sources can be combined to protect sensitive information.

3

Using parameters and functions in Power Query allows dynamic queries that adapt based on user input or other data.

When NOT to use

Getting data from sources is not ideal when you need real-time streaming data or extremely large datasets better handled by specialized BI tools. In such cases, use dedicated data platforms or databases with live dashboards.

Production Patterns

Professionals use scheduled refreshes to keep reports current, combine multiple sources into a single data model, and automate data cleaning with Power Query scripts. They also document queries and manage data source credentials securely.

Connections

ETL (Extract, Transform, Load)

Getting data from sources in Excel is a simplified form of ETL used in data engineering.

Understanding Excel’s data import as ETL helps grasp how data pipelines work in bigger systems.

APIs (Application Programming Interfaces)

Excel can connect to web APIs to get data, similar to how software applications communicate.

Knowing API basics helps unlock advanced data import scenarios in Excel.

Supply Chain Management

Getting data from multiple sources in Excel mirrors gathering information from suppliers and partners in supply chains.

Seeing data import as part of a supply chain clarifies the importance of data freshness and accuracy.

Common Pitfalls

#1 Trying to edit imported data directly to fix errors in the source.

Wrong approach:

Changing values in the imported Excel table expecting the source file to update.

Correct approach:

Edit the original source file or database, then refresh the data in Excel.

Root cause:

Misunderstanding that imported data is a copy, not a live editable link.

#2 Not refreshing data after the source changes, leading to outdated analysis.

Wrong approach:

Import data once and never use the Refresh button or automatic refresh settings.

Correct approach:

Use the Refresh button or set automatic refresh to keep data current.

Root cause:

Assuming data updates automatically without user action.

#3 Importing large data sets without filtering or transforming, causing slow performance.

Wrong approach:

Loading entire huge tables without applying filters or removing unnecessary columns.

Correct approach:

Use Power Query to filter and select only needed data before loading.

Root cause:

Not using data transformation tools to optimize data size and relevance.

Key Takeaways

Getting data from sources brings external information into Excel to save time and reduce errors.

Excel’s Get & Transform (Power Query) is the main tool to import, clean, and shape data easily.

Imported data does not update automatically; you must refresh it to get the latest information.

You can connect to many sources including files, databases, and web pages without coding.

Advanced users can automate and customize data imports for powerful, dynamic reports.

Practice

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