How to Pull Data from Another Excel File

Juggling data across multiple Excel workbooks is a common headache, often leading to endless copy-pasting and a high risk of errors. Thankfully, there’s a much better way to work. This guide will walk you through a couple of powerful methods for pulling data from one Excel file into another, so your numbers update automatically and you can get back to analyzing, not just wrangling data.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Link Excel Files Instead of Copy-Pasting?

Before we get into the "how," let's quickly cover the "why." Relying on Ctrl+C and Ctrl+V to move data between spreadsheets is inefficient and dangerous for a few key reasons:

• It’s Prone to Human Error: Did you copy the right range? Paste it in the correct cell? What if you forgot a row? Manual data transfer is a recipe for mistakes that can be incredibly difficult to track down later.
• The Data is Instantly Stale: The moment you paste data, it becomes a static snapshot. As soon as the source file is updated, your report is out of date. This forces you to repeat the process every time you need an updated report.
• It’s Time-Consuming: The minutes you spend every day or week locating files, copying data, and pasting it add up quickly. This is valuable time that could be spent on higher-value tasks.

By creating a direct link between Excel files, you build a dynamic connection. When the data in your source file changes, a simple refresh updates the destination file automatically. This makes your reports more accurate, reliable, and entirely frees you from the manual copy-paste cycle.

Method 1: Direct Cell Linking (The Quick and Easy Way)

The simplest way to connect two workbooks is by creating a formula that references a cell or range in another file. This is perfect for pulling a smaller amount of data, like a summary total or a key performance indicator, into a dashboard or report.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

When to Use Direct Cell Linking

This method is your best choice when you need to:

• Pull a single number (e.g., total quarterly sales) from one report into another.
• Link to a specific piece of text (e.g., a project manager's name).
• Create a small summary table based on a few key cells from a different file.

Step-by-Step Instructions

For this to work smoothly, it's best to have both Excel files open at the same time.

Let’s say you have a file named ‘Sales-Data-Q4.xlsx’ and you want to pull the grand total of sales from it into a master reporting file called ‘Annual-Report.xlsx’.

1. Open both workbooks: Annual-Report.xlsx (your destination file) and Sales-Data-Q4.xlsx (your source file).
2. Select the destination cell: In Annual-Report.xlsx, click on the cell where you want the Q4 sales total to appear.
3. Start the formula: Type the equals sign (=) in that cell, but don't press Enter yet.
4. Switch to the source workbook: Click over to the Sales-Data-Q4.xlsx window.
5. Click the source cell: Click on the cell that contains the grand total sales figure (let's say it's cell E50 on the sheet named "Summary"). You'll see a dashed line appear around the cell.
6. Press Enter: Hit the Enter key. Excel will automatically switch you back to your Annual-Report.xlsx file, and the grand total from the other file will appear.

If you look in the formula bar, you’ll see something like this:

='[Sales-Data-Q4.xlsx]Summary'!$E$50

Breaking Down the Formula:

• '...': The single quotes wrap the file path and name.
• [Sales-Data-Q4.xlsx]: This is the name of the source workbook.
• Summary: This is the name of the worksheet within that source workbook.
• !: The exclamation point separates the sheet name from the cell reference.
• $E$50: This is the absolute reference to the specific cell you're pulling data from.

Now, any time the value in cell E50 of Sales-Data-Q4.xlsx is updated and saved, the value in Annual-Report.xlsx will update automatically the next time you open it (Excel will prompt you to enable content and refresh links).

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Method 2: Using Power Query (The Robust and Scalable Way)

For pulling entire tables or large datasets, direct cell linking becomes clunky. A much more powerful and flexible solution is to use Power Query. Power Query (also known as Get & Transform Data) is a tool built into modern versions of Excel designed specifically for connecting to, cleaning, and shaping data from a wide variety of sources, including other Excel files.

When to Use Power Query

Power Query is the ideal choice when you need to:

• Import an entire table of data.
• Combine data from multiple sheets or even multiple files.
• Filter out unneeded rows or columns before the data gets into your workbook.
• Perform transformations, like splitting columns, changing data types, or adding calculated columns.

Step-by-Step Instructions

Let's stick with our sales reporting example. This time, you want to pull the entire table of detailed sales transactions from Sales-Data-Q4.xlsx into your Annual-Report.xlsx workbook to perform a pivot table analysis.

1. Navigate to the Data Tab: In your Annual-Report.xlsx file, go to the Data tab on the ribbon.
2. Get Data From File: In the "Get & Transform Data" section, click Get Data > From File > From Workbook.
3. Select Your Source File: A file browser will pop up. Navigate to and select your source file, Sales-Data-Q4.xlsx, and click Import.
4. Choose Your Data in the Navigator: A "Navigator" window will appear, showing you a preview of all the worksheets and named tables in your source file. It's a best practice to structure your source data as a formatted Excel Table (you can create one via Insert > Table) because it makes the connection more reliable. Click on the table or sheet containing the sales transactions you want to import. A preview will appear on the right.
5. Transform or Load: At the bottom of the Navigator window, you have two main options:
6. Clean Your Data (Optional but Recommended): In the Power Query Editor, you can perform hundreds of different transformations. For example:

Every step you take is recorded in the "Applied Steps" pane on the right. These steps will be automatically reapplied every time you refresh your data.

1. Close & Load: Once you're done shaping your data, click the Close & Load button in the top-left corner. The clean, filtered data will be loaded into a new worksheet in your Annual-Report.xlsx file.

Refreshing Your Power Query Connection

The best part of using Power Query is how easy it is to update your data. When the source file (Sales-Data-Q4.xlsx) has been updated, you can refresh the data in your destination file (Annual-Report.xlsx) in a couple of ways:

• Right-click anywhere inside the imported data table and select Refresh.
• Go to the Data tab and click Refresh All.

Excel will re-run the connection, re-apply all your transformation steps in the background, and pull in the latest data from the source file. No more manual updates needed.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Tips for Managing Linked Files

To avoid frustrating errors and broken links, it's smart to follow a few best practices:

• Use Stable File Paths: Broken links are most often caused by moving or renaming the source file. Store your linked workbooks in a clear, organized folder structure, preferably on a shared drive like OneDrive or SharePoint if you're collaborating.
• Use Named Tables: In your source files, always format your raw data into an official Excel Table (Insert > Table) and give it a sensible name. Referencing a named table in Power Query is much more robust than referencing a sheet, which can break if someone adds rows above a range.
• Be Mindful of Performance: While incredibly useful, having too many complex external links, especially with the direct linking method, can sometimes slow down your workbook's performance. Keep things as simple as you can.

Final Thoughts

Learning how to dynamically link Excel files is a game-changer. Both direct cell linking and Power Query serve to eliminate manual updates, reduce errors, and ensure your reports are always built on the freshest data available. By automating your data pipeline, you free up valuable time to focus on deriving insights and making decisions.

Manually connecting spreadsheets is a huge step up from copy-pasting, but orchestrating reports across a dozen different data sources will always be a challenge. At Graphed, we created our tool to solve this exact problem. Instead of wrestling with data connections, you simply connect your platforms - like Shopify, Google Analytics, Excel, and HubSpot - and ask in plain English for the dashboard you need. We automate the entire analysis and reporting process, giving you live insights in seconds so you can get back to growing your business.