How to Pull Data from Another Workbook in Excel

Working with data spread across multiple Excel workbooks is a common headache, often leading to endless copying and pasting that's both time-consuming and error-prone. Thankfully, you can stop this manual process. This article will show you three effective methods to pull data from another workbook directly into your current sheet, from a simple link to a powerful automated connection.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Quickest Method: Direct Cell Referencing

The simplest way to pull data from another workbook is by creating a direct link between cells. This is perfect for when you need to grab a specific value, like a grand total from a monthly summary report and display it in a master dashboard. Think of it as a live "copy and paste" that updates automatically.

How to Create a Direct Cell Link

For this technique to work smoothly the first time, have both the source workbook (the one with the data) and the destination workbook (the one receiving the data) open.

1. In your destination workbook, click on the cell where you want the external data to appear.
2. Type the equals sign (=) to start a formula.
3. Without clicking anywhere else, navigate to the source workbook. You can do this by using the Windows taskbar, pressing Alt+Tab on Windows, or Command+~ on a Mac.
4. Once in the source workbook, click on the sheet and then the specific cell containing the data you want to pull.
5. You'll see a blinking dashed line (marching ants) around the selected cell, and the formula will appear in the formula bar. Press Enter.

You will be taken back to your destination workbook, and the data from the source cell will now appear. If you click on the cell, you’ll see a formula that looks something like this:

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

Breaking Down the Formula

Excel writes this special formula for you, but understanding its parts is helpful for troubleshooting:

• [Source_Workbook_Name.xlsx]: This is the name of the source file, enclosed in square brackets. If the workbook is closed, Excel will automatically add the full file path here.
• Sheet1: This is the name of the worksheet within that source workbook that contains the data.
• '!': The exclamation mark is a standard Excel syntax that separates the sheet name from the cell reference.
• $A$1: This is the absolute reference to the specific cell you linked to.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pros and Cons of Direct Referencing

This method is quick and effective for simple tasks, but it's important to know its limitations.

• Pro: Extremely fast and easy for one-off values or small data ranges.
• Pro: The link is live. If you change the value in the source cell (while both files are open), the destination cell updates instantly.
• Con: It’s brittle. If you move, rename, or delete the source workbook, the link breaks and you’ll get a dreaded #REF! error. You’ll have to manually update the formula's file path to fix it.
• Con: It's not scalable. Managing dozens or hundreds of individual cell links can quickly become confusing and difficult to maintain.

Use direct links for simple dashboard totals or key metrics where you won't need to change the source file's location.

Finding Specific Data with VLOOKUP or XLOOKUP

What if you don't just need one cell, but need to find a specific piece of information based on a key value? For instance, you might want to pull a product's price from a master price list into your current sales sheet using its SKU. This is a perfect job for a lookup function like VLOOKUP or its more modern and flexible successor, XLOOKUP.

This method works by searching for an identifier (like an email address, order ID, or SKU) in one workbook and returning a corresponding value from the same row in another workbook.

How to Use a Lookup Function Across Workbooks

Let’s imagine we have two workbooks:

• Sales_Q1.xlsx: Contains a table with ProductID and UnitsSold. We want to add the ProductName.
• Product_Catalog.xlsx: Contains a complete catalog with ProductID, ProductName, and Price.

Our goal is to pull the ProductName from the catalog into our sales report.

1. Open both Sales_Q1.xlsx and Product_Catalog.xlsx.
2. In Sales_Q1.xlsx, click on the first empty cell in the "ProductName" column (let's say it's cell C2).
3. We'll use XLOOKUP, as it's simpler and more robust than VLOOKUP. Start typing the formula:
4. Lookup_value: Select the cell with the ProductID in your current sheet. For our example, this would be cell A2. The formula is now =XLOOKUP(A2,.
5. Lookup_array: This is the column where XLOOKUP will search for the Product ID. Switch to your Product_Catalog.xlsx workbook, find the ProductID column, and select the entire column by clicking its header. Excel automatically inserts the external reference. Don't forget to press F4 to add $ signs, locking the reference. The formula looks something like:
6. Return_array: This is the column containing the data you want to retrieve. In Product_Catalog.xlsx, select the entire ProductName column. Again, lock the reference with F4. The final formula will be:
7. Press Enter, and drop the formula down for all your rows. The product name corresponding to each ID will be pulled in from the other file.

Note: If you are on an older version of Excel that doesn't have XLOOKUP, you can achieve the same with VLOOKUP. The syntax would be

=VLOOKUP(A2, '[Product_Catalog.xlsx]Products'!$A:$C, 2, FALSE)

which assumes your product names are in the 2nd column of your selected table array ($A:$C).

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

When to Use This Method

VLOOKUP and XLOOKUP are fantastic for enriching data. Use this method whenever you have a common identifier in two datasets and you need to merge information from one into the other. It's much more reliable and efficient than matching records by hand.

The Professional's Choice: Power Query (Get & Transform Data)

When you're dealing with large datasets, messy data, or you need a connection that's refreshable, robust, and automated, Power Query is the C4 corvette. Available in modern versions of Excel (2016+, Office 365, etc.), Power Query is a data connection tool designed to import, transform, and load data from dozens of sources - including other Excel workbooks.

It creates a query that remembers the steps you took to connect to and clean your data, allowing you to refresh the connection with a single click.

How to Connect Workbooks with Power Query

1. Start in your blank destination workbook.
2. Navigate to the Data tab on the Ribbon.
3. In the "Get & Transform Data" section, click on Get Data > From File > From Workbook.
4. A file explorer window will open. Locate and select your source Excel workbook, then click Import.
5. The Navigator window will appear next. It shows you all the sheets and any officially formatted Excel Tables within the source workbook. It’s always best practice to select a Table if available, as they are more structured.
6. Select the table or sheet you want to pull data from. You will see a preview on the right.
7. Instead of clicking Load directly, click the Transform Data button. This is where the magic happens.

Working in the Power Query Editor

The Power Query Editor is a separate window where you can shape the data before it ever lands in your workbook. This is incredible for cleaning up messy files.

• Filter Rows: You can remove any unnecessary rows. For example, you can filter to only include data from a specific region or timeframe.
• Choose Columns: If the source file has 50 columns but you only need 5, you can remove the rest. This keeps your destination file clean and lightweight.
• Change Data Types: Ensure dates are recognized as dates and numbers are recognized as numbers.

Every action you take is recorded as a step in the "Applied Steps" pane on the right. When you're finished tidying up, click the Close & Load button in the top-left corner.

The data will be loaded into a new sheet in your workbook, perfectly formatted as an Excel Table. More importantly, you've created a durable connection.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Refreshing Your Data

The best part about Power Query is the refresh. After you've set up the connection, you never have to repeat those steps. When the data in your source workbook is updated, simply go to your destination sheet, right-click anywhere inside the query table, and select Refresh. Power Query will repeat all your applied steps in the background and pull in the latest data. You can also go to Data > Refresh All.

Which Method Should You Choose?

You have three great options, so choosing the right one depends on your goal:

• Choose Direct Cell Linking when... you only need to pull a few specific, static values (like summary totals) for a simple dashboard.
• Choose VLOOKUP/XLOOKUP when... you need to enrich your current dataset by pulling in matching information from another list based on a common ID.
• Choose Power Query when... you're working with large datasets, need an automated and refreshable connection, or the source data needs to be cleaned, filtered, or reshaped before being used. This is the most scalable and professional method for any recurring report.

Final Thoughts

Getting your data out of separate silos and into one place is the first step toward better analysis. By using direct links, lookup formulas, or Power Query, you can ditch the ineffective habit of copying and pasting and build more reliable and dynamic reports in Excel.

While these Excel methods are powerful, setting up complex analytics that combine data from sources beyond spreadsheets - like Google Analytics, Salesforce, or your ad platforms - can introduce an entirely new layer of complexity. This is why we created Graphed. Our platform connects directly to your marketing and sales data, serving them in one-click. From there, you just ask our AI data analyst questions in plain English - like "compare Facebook ad spend to Shopify revenue this month" - and it instantly builds the live, refreshable dashboards you need, saving you from any manual data-pulling at all.