How to Import Data Into Excel From Another Excel File

Pulling data from one Excel workbook into another is one of the most practical skills in everyday spreadsheet work. Whether you're consolidating monthly reports, linking a master dashboard to department files, or combining datasets from different teams, Excel gives you several ways to do it — and the method that works best depends heavily on how your data is structured and what you need to do with it afterward.

Why Importing Between Excel Files Isn't One-Size-Fits-All

Excel distinguishes between linking, importing, and embedding data — and these aren't interchangeable. A linked reference updates automatically when the source file changes. An imported dataset is a snapshot copied at a point in time. Understanding that difference shapes every decision that follows.

The version of Excel you're using also matters. Excel 365, Excel 2021, and Excel 2019 handle external data connections differently than older versions, and some features — like Power Query — aren't available in all editions.

Method 1: Copy and Paste (Static Import)

The simplest approach: open both workbooks, select your data range in the source file, copy it, and paste it into your destination workbook.

What you get: A static snapshot. No ongoing connection to the original file. Changes in the source won't appear in the destination automatically.

Best suited for: One-time data transfers, archiving, or situations where you deliberately want the data to stay fixed.

Paste Special options give you more control here. Using Paste Special → Values strips out formulas and pastes only the raw numbers — useful when the source file uses formulas you don't want to carry over or that would break without their original references.

Method 2: External Cell References (Live Links) 🔗

Excel lets you write formulas that reference cells in a completely different workbook. The syntax looks like this:

='[SourceWorkbook.xlsx]Sheet1'!$A$1 

When both files are open, these update in real time. When the source file is closed, Excel stores the last known values and prompts you to update the link when you reopen the destination file.

Key variables that affect this method:

• File location stability — if the source file moves or is renamed, the link breaks
• Shared drives vs. local storage — links across network drives or SharePoint can behave inconsistently depending on permissions and connectivity
• Number of linked cells — large volumes of external references can slow workbook recalculation noticeably

This method works well for relatively simple, stable setups where files live in predictable locations.

Method 3: Power Query (Get & Transform Data) ⚙️

Power Query is Excel's built-in data import and transformation engine, available under the Data tab as Get Data → From File → From Workbook.

You point Power Query at a source Excel file, select the sheet or table you want, apply any transformations (filtering rows, renaming columns, changing data types), and load the result into your destination workbook. The imported data lands in a proper Excel Table, and you can refresh it on demand to pull in the latest version of the source file.

What makes Power Query different:

• The connection is refreshable but not automatic by default — you control when the data updates
• You can import from multiple sheets or multiple files at once
• Transformations are recorded as repeatable steps, making the process auditable and reusable
• It handles much larger datasets more gracefully than manual copy-paste

Variables that matter here:

• Table vs. range formatting in the source — Power Query works most reliably when the source data is formatted as an Excel Table with consistent headers
• File path — just like external references, Power Query connections break if the source file moves
• Excel version — Power Query is fully integrated in Excel 2016 and later; earlier versions either lack it or have it as a separate add-in

Method 4: Paste Link (Hybrid Approach)

Using Paste Special → Paste Link creates live external references automatically for a selected range, without writing the formula syntax manually. It's a middle ground between the simplicity of copy-paste and the flexibility of hand-written external references.

The tradeoff: you get the same link-stability issues as manual external references, and the linked range isn't as easy to manage or refresh as a Power Query connection.

Comparing the Main Approaches

The Variables That Determine Which Method Makes Sense

Several factors push users toward different approaches:

How often the source data changes. If your source file is updated daily and you need your destination to reflect those changes, a static paste quickly becomes a maintenance headache. External references or Power Query become the more practical options.

How large the dataset is. A few hundred rows? Any method works. Tens of thousands of rows with multiple columns? Power Query handles that load more efficiently than live cell references.

Where the files live. Local files, network drives, SharePoint, and OneDrive all introduce different behaviors — particularly around link stability and refresh permissions. Power Query connections to SharePoint-hosted files, for example, may require authentication steps that don't apply to local files.

Whether the data needs transformation. If you're not just moving data but also cleaning it — removing blank rows, standardizing date formats, merging columns — Power Query is the only method that builds that logic into the import process itself.

Your Excel version and available features. Not every user has access to the full Power Query toolset. Older Excel versions, certain enterprise licensing configurations, or Excel for Mac can limit which options are available or how they behave. 🖥️

The method that's genuinely right for a given situation depends on how all of those variables line up in your specific environment — the files you're working with, where they're stored, how frequently they change, and what you need the data to do once it arrives.