How to Link Data in Excel

Need to pull real-time data from one Excel sheet into another without the headache of copy-pasting? You're in the right place. This guide will walk you through several practical methods for linking data within a workbook or across separate Excel files, from beginner-friendly formulas to powerful, automated techniques.

Why Link Data in Excel?

Before jumping into the "how," it's helpful to understand the "why." Linking data isn't just a clever trick, it's a foundational skill for creating efficient and accurate spreadsheets. Once you master it, you can:

Create a master summary sheet or dashboard: Pull key totals and metrics from multiple detailed sheets (like monthly sales, department budgets, or project timelines) into a single, high-level view.
Centralize important information: Maintain one master source of truth (like a product price list or employee contact info) and link to it from other sheets. When the master list is updated, every linked cell updates automatically.
Eliminate manual work and reduce errors: Stop manually copying and pasting data every time something changes. Linking creates a live connection, which means your reports are always up-to-date and free from human error.
Ensure consistency: When everyone on your team is linking to the same source data, you can be confident that all reports are based on the same numbers.

In short, linking transforms your static spreadsheets into dynamic, interconnected reporting systems that do the hard work for you.

Method 1: The Easiest Way to Link Cells (External References)

This is the most direct way to link data in Excel and it's perfect for pulling a single value or a small range of cells from one location to another. You can do this within the same workbook or between two different workbooks.

Linking Data from Another Sheet in the Same Workbook

Let's say you have a workbook with two sheets: a "Sales Data" sheet with raw sales figures and a "Monthly Summary" sheet where you want to display the final total.

Here’s how to link the total from the Sales Data sheet to your summary:

Click on the cell in your "Monthly Summary" sheet where you want the linked data to appear (e.g., cell B2).
Type the equals sign (=).
Without pressing enter, navigate to the "Sales Data" sheet by clicking its tab at the bottom of the screen.
Click on the cell containing the total you want to link (e.g., cell F30).
Press Enter.

That's it! You'll be taken back to your "Monthly Summary" sheet, and cell B2 will now display the value from cell F30 on the "Sales Data" sheet. If you look at the formula bar, you'll see a reference like this:

='Sales Data'!F30

The syntax is simple: SheetName!CellAddress. Now, whenever the total in F30 of your sales sheet changes, cell B2 on your summary sheet will update instantly.

Linking Data from Another Excel Workbook

The process is nearly identical for linking data between two separate files. For best results, have both the source workbook (the one with the data) and the destination workbook (the one where you want it to appear) open.

Open both Excel workbooks.
In the destination workbook, click the cell where you want the linked data to go and type =.
Switch to the source workbook (you can use Alt+Tab or the Taskbar).
Navigate to the correct sheet and click the cell you want to link.
Press Enter.

The formula will now include the workbook name in square brackets:

='[Q1_Sales_Report.xlsx]Sheet1'!$F$30

A cool feature of this is that the link remains live even when the source workbook is closed. If you close the source file, Excel will automatically add the full file path to the formula so it doesn't lose track of where the data lives.

Heads Up: When you open a file with external links, Excel will show a security warning asking if you want to enable automatic updates for the links. If you trust the source file, you can click "Enable Content."

Method 2: Dynamically Look Up and Link Data with VLOOKUP or XLOOKUP

Simple cell linking is great, but what if you need to pull specific information from a large table? For example, you might want to find the sales price for a specific product ID or the bonus amount for a particular employee. This requires a dynamic lookup.

Using VLOOKUP

Let's imagine you have a master "Product List" sheet with columns for "Product ID," "Product Name," and "Price." On another sheet, "Invoice Template," you want to enter a Product ID in column A and have the Price automatically appear in column C. VLOOKUP is built for this.

The syntax is:

=VLOOKUP(what_you_want_to_look_for, where_to_look_for_it, column_number_to_return, exact_or_approximate_match)

Here’s how you’d use it in our example:

In cell C2 of your "Invoice Template" sheet, you’d enter the formula.
Your lookup value is the Product ID you entered in cell A2.
The table array is the entire data range on your "Product List" sheet (e.g., Product List!A2:C100).
The column index number for "Price" is 3, since it's the third column in our selected table.
You want an exact match, so you use FALSE.

The final formula in cell C2 would be:

=VLOOKUP(A2, 'Product List'!A2:C100, 3, FALSE)

The Better, Modern Alternative: XLOOKUP

While VLOOKUP is a classic, it has limitations (like not being able to look to the left). If you have a newer version of Excel (2021, Microsoft 365), you should use XLOOKUP. It's more powerful, flexible, and intuitive.

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

Using the same product example, the XLOOKUP formula is much simpler:

Your lookup value is still the Product ID in A2.
The lookup array is just the column containing the Product IDs on your "Product List" sheet (e.g., 'Product List'!A:A).
The return array is the column containing the prices you want to get back (e.g., 'Product List'!C:C).

The final XLOOKUP formula is:

=XLOOKUP(A2, 'Product List'!A:A, 'Product List'!C:C)

Now, any time you enter a valid Product ID in your invoice sheet, the correct price will be pulled in instantly.

Method 3: Consolidate and Link Multiple Sheets with Power Query

If you need to do more than just link a few cells - if you need to consolidate entire tables from multiple sheets or even multiple files into one master table - then Power Query is the tool you need.

Power Query (also known as Get & Transform Data) is Excel's built-in data connection and preparation engine. It's perfect for tasks like combining monthly sales data from 12 separate sheets into one annual summary table.

Here’s a basic workflow for combining multiple sheets from the same workbook:

Go to the Data tab on the Ribbon.
In the "Get & Transform Data" group, click Get Data > From File > From Excel Workbook.
Browse and select the workbook that contains the sheets you want to combine (even if it’s the one you're currently in).
In the Navigator window, check the box for Select multiple items at the top.
Now, select all the sheets (e.g., "Jan Sales," "Feb Sales," etc.) that you want to consolidate.
Instead of clicking "Load," click the dropdown arrow beside it and choose Transform Data.

This opens the Power Query Editor, which is a new window separate from your main spreadsheet. Here, you'll combine the data:

In the Home tab of the Power Query Editor, find the Append Queries option. Clicking it dropdown gives you two choices. If you're combining more than two tables, choose Append Queries as New.
In the Append dialog box, select the tables you want to stack on top of each other and add them to the "Tables to append" list. Click OK.

Power Query will create a new, consolidated query containing all the rows from the sheets you selected.

Once you're happy, click Close & Load in the top-left corner.

Excel will insert a new sheet into your workbook containing all your combined data formatted as an official Excel Table. The best part? This link is refreshable. If you add new sales data to the January sheet next month, you just have to go to your summary table, right-click, and select Refresh - and the new data will automatically be pulled in.

How to Manage and Fix Broken Data Links

Your linked data is only reliable if the connections are healthy. If you rename a source file or move it to a different folder, your links will break and you'll see a #REF! error.

You can manage and update your connections from one central place.

Go to the Data tab.
In the "Queries & Connections" group, click on Edit Links. (Note: this option will be greyed out if your active workbook has no external links.)
This opens the Edit Links dialog box, where you can see all the source files your current sheet is linked to.

From here, you have several useful options:

Update Values: Manually refresh the links to pull in the latest data from the source files.
Change Source: If a source file was moved or renamed, you can use this option to reconnect the link by pointing Excel to the new file location.
Break Link: This permanently severs the connection to the source file. The formulas will be replaced with their current static values. Use this with caution, as it cannot be undone.
Check Status: Quickly check if your links are working correctly.

Final Thoughts

Linking data is a core Excel skill that separates casual users from proficient ones. By moving beyond manual updates, you create reports that are more accurate, dynamic, and far less time-consuming to maintain. Whether you're using simple cell references, dynamic lookups with XLOOKUP, or sophisticated data pipelines with Power Query, a well-linked spreadsheet is a powerful asset.

Mastering these techniques in Excel is a huge step forward. But as you start connecting data from other platforms - like Google Analytics, Shopify, QuickBooks, or whatever else you use to run your business - you'll find that even the best Excel workflow becomes a bottleneck. We built Graphed to solve exactly this problem. Instead of wrestling with data exports and Power Query, you simply connect your tools with one click, then ask for what you need in plain English. Graphed automatically pulls your live data and creates interactive dashboards, helping you generate reports in 30 seconds instead of 3 hours.