How to Get Data Automatically in Excel from Another Sheet

Manually copying and pasting data between Excel sheets is not only tedious but also a recipe for costly mistakes. A single wrong paste can throw off an entire report. The good news is that you can build smart, automated worksheets that pull data from one sheet to another, ensuring your information is always in sync. This article will walk you through several methods for automatically getting data from another sheet in Excel, from simple cell references to powerful lookup functions.

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

Choose Your Method: Which Approach is Right for You?

Excel offers multiple ways to connect your data. The best method depends on what you're trying to accomplish. Here’s a quick overview of what we’ll cover, from simplest to most advanced:

• Simple Cell Linking: Best for pulling a single piece of static information that doesn't change position.
• VLOOKUP: A classic function for finding specific data in a table, like looking up a product price from a master inventory list.
• XLOOKUP: The modern, more flexible successor to VLOOKUP that can search in any direction.
• INDEX & MATCH: The traditional powerhouse combo for handling complex lookups with multiple criteria.
• FILTER Function: Ideal for automatically pulling an entire set of records that meets your criteria into a new range.
• Power Query: The ultimate tool for connecting, transforming, and loading large datasets from other sheets or workbooks automatically.

Method 1: The Foundation - Simple Cell Linking

The most straightforward way to get data from another sheet is to create a direct link to a specific cell. This is perfect for pulling a total from a summary sheet into a master dashboard or referencing a fixed value like a sales tax rate.

Let's say you have your Q4 sales total in cell F25 on a sheet named "SalesQ4". On your "Annual Summary" sheet, you want to display this total.

How to Do It:

1. Click the cell where you want the data to appear (e.g., cell C4 on "Annual Summary").
2. Type an equals sign: =
3. Without pressing enter, click on the tab for the other sheet ("SalesQ4").
4. Click on the source cell (F25).
5. Press Enter.

Excel automatically writes the formula for you in the formula bar. It will look like this:

='SalesQ4'!F25

Now, any time the value in cell F25 on the "SalesQ4" sheet changes, cell C4 on your "Annual Summary" sheet will update instantly. This is simple and effective, but it has one major drawback: if you insert a row or column and the source data moves from F25 to F26, your formula will break. For dynamic data, you need a lookup function.

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

Method 2: The Classic Workhorse - VLOOKUP

What if you need to find a specific piece of information within a large table? Imagine you have a sheet with thousands of products and their prices, and on another sheet, you want to enter a product ID and have the price automatically appear. This is a job for VLOOKUP (Vertical Lookup).

VLOOKUP scans the first column of a table for a value you specify, then moves across to a specific column in that same row to retrieve a result.

Let’s say you have a "Products" sheet with Product IDs in column A, Product Names in column B, and Prices in column C. On your "Sales" sheet, you want to type a Product ID in cell A2 and see its price pop up in B2.

VLOOKUP Formula Breakdown:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The piece of data you are looking for (in our case, the Product ID in cell A2 of the "Sales" sheet).
• table_array: The entire data range to search, including the column with the lookup value and the column with the return value. For this, you would select the data on your "Products" sheet (e.g., Products!A2:C100). Crucially, the lookup value must be in the very first column of this range.
• col_index_num: The column number in your table_array from which to retrieve the value. Since we want the price from the third column (A=1, B=2, C=3), our number is 3.
• [range_lookup]: This is optional. Use FALSE for an exact match (most common) or TRUE for an approximate match. We almost always want an exact match, so we'll use FALSE.

Putting It Together:

In cell B2 on your "Sales" sheet, you would enter this formula:

=VLOOKUP(A2, Products!A2:C100, 3, FALSE)

Now, whenever you type a valid product ID into A2, its price will be fetched from the "Products" sheet.

Common issue: If VLOOKUP can't find your lookup value, it returns an ugly #N/A error. You can wrap your formula in IFERROR to display a more friendly message:

=IFERROR(VLOOKUP(A2, Products!A2:C100, 3, FALSE), "Product Not Found")

Method 3: The Modern & Flexible Solution - XLOOKUP

For those using Excel for Microsoft 365, Excel 2021, or Excel for the web, XLOOKUP is the superior successor to VLOOKUP. It’s more intuitive, more powerful, and fixes VLOOKUP’s biggest limitations.

Why XLOOKUP is Better:

• Look anywhere: You don't have to rearrange your data. XLOOKUP can look in one column and return a value from a column to the left - something VLOOKUP can't do.
• Simpler to write: No more counting columns! You simply select the lookup column and the return column.
• Safer by default: XLOOKUP defaults to an exact match, so you don’t have to remember to add "FALSE".
• Built-in error handling: It has an optional argument for what to show if the value isn’t found, so you don't need IFERROR.

XLOOKUP Formula Breakdown:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

• lookup_value: The value you’re looking for (Sales!A2).
• lookup_array: The column where you want to search (Products!A2:A100).
• return_array: The column from which you want to return a value (Products!C2:C100).
• [if_not_found]: An optional value or message to return if no match is found (e.g., "Product Not Found").

Putting It Together:

Using the same example, the XLOOKUP formula in cell B2 on your "Sales" sheet would be:

=XLOOKUP(A2, Products!A:A, Products!C:C, "Product Not Found")

This is much easier to read and less prone to errors. If you have access to it, make XLOOKUP your go-to lookup function.

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

Method 4: Complex Lookups with INDEX & MATCH

Before XLOOKUP existed, the combination of INDEX and MATCH was the gold standard for advanced lookups that VLOOKUP couldn’t handle. While XLOOKUP has made it less necessary, it's still found in many existing spreadsheets and is powerful to know.

How They Work Together:

• MATCH finds the position (the row number) of a lookup value in a range.
• INDEX returns the value at a specific position (row and column number) in a range.

When you put them together, you tell MATCH to find the row your item is on, and then you feed that row number into INDEX to grab a value from a different column on that same row.

Formula:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

Putting It Together:

In our sales example, the formula in cell B2 on the "Sales" sheet would be:

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

• MATCH(A2, Products!A:A, 0) searches for the Product ID from cell A2 within column A of the "Products" sheet and finds its row number. The 0 specifies an exact match.
• INDEX(Products!C:C, ...) then takes that row number and returns the value from that position in column C (the prices).

Method 5: Pulling Entire Datasets with the FILTER Function

What if you don't just want one value? What if you want to pull a whole list of records that match a certain criterion - for example, all sales made by a specific employee or all transactions from the "East" region?

The FILTER function is perfect for this. It spills an entire array of results automatically. It's only available in newer versions of Excel.

Let’s say you have a "MasterSales" sheet with Date, Region, and Sale Amount columns. On a new sheet, you want to create a report that shows only the sales from the "North" region.

FILTER Formula Breakdown:

=FILTER(array, include, [if_empty])

• array: The entire range of data you want to filter (e.g., MasterSales!A2:C1000).
• include: The condition. This is a logical test. We want to check if the region column equals "North" (e.g., MasterSales!B2:B1000="North").
• [if_empty]: An optional message to display if no matching records are found (e.g., "No Sales for this Region").

Putting It Together:

On your new report sheet, in cell A2, you'd write:

=FILTER(MasterSales!A2:C1000, MasterSales!B2:B1000="North", "No Sales Found")

Excel will instantly populate cell A2 and all the necessary cells below and to the right of it with every single sales record from the "North" region. If you update the "MasterSales" sheet, this report updates automatically.

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

Method 6: The Pro-Level Solution - Power Query

When you're dealing with very large datasets, need to pull data from a closed workbook, or have to clean and reshape your data before it even gets to your sheet, Power Query is the tool to use. It’s a data connection and transformation engine built right into Excel (found in the "Data" tab).

When to use Power Query:

• Your data is messy and needs cleaning (e.g., removing extra spaces, changing text case, splitting columns).
• You need to combine data from multiple sheets or files.
• You're working with tens of thousands or hundreds of thousands of rows.
• You want to fully automate the data refresh process with a single click.

While a full Power Query tutorial is extensive, the basic process involves:

1. Going to the Data tab and selecting Get Data > From File > From Workbook.
2. Navigating to your Excel file and selecting the sheet or table you want to pull from.
3. Using the Power Query Editor to apply transformation steps (like filtering rows, removing columns, etc. – these steps are recorded and saved).
4. Clicking Close & Load To... to load the result into a new table on your desired worksheet.

Once this connection is built, all you have to do is go to the Data tab and click Refresh All, and Power Query will automatically repeat all your recorded steps with the latest data.

Final Thoughts

Automating your Excel workflow by linking sheets saves time, prevents errors, and turns your static spreadsheets into dynamic dashboards. From a simple cell reference for totals to using flexible tools like XLOOKUP or the FILTER function for dynamic datasets, there is an effective method for almost any scenario.

Once you get comfortable with these techniques, you'll find your reporting becomes much simpler. The real challenge often comes when your data isn't just in another sheet but scattered across a dozen different tools like Google Analytics, Shopify, Salesforce, and Facebook Ads. Stitching that data together with CSV exports would take hours. That's actually why we built Graphed. We provide one-click integrations to connect all your marketing and sales data sources, allowing you to ask questions in plain English and instantly get back real-time dashboards and reports, so you can spend less time wrangling formulas and more time acting on insights.