How to Auto Populate Data in Excel from Another Worksheet

Manually copying data from one Excel worksheet to another is more than just tedious - it's a recipe for errors. Whether you're creating summary reports, building a "master sheet," or simply trying to organize your workbook, you need a way for your data to update automatically. This tutorial will walk you through several methods to auto-populate data in Excel from another worksheet, ranging from simple linking to more 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

Understanding the Basics: How Excel References Other Worksheets

Before we get into specific formulas, it's important to understand Excel's syntax for referencing other sheets. When you point to a cell or range in a different worksheet, the reference follows a simple pattern:

SheetName!CellAddress

For example, if you want to pull the value from cell A1 in a worksheet named "SalesData," the reference would be SalesData!A1. If your sheet name includes spaces or special characters, you need to enclose it in single quotes, like this: 'Q4 Sales Data'!A1. Grasping this simple syntax is the foundation for all the methods we'll cover below.

Method 1: The Simple Link (Direct Cell Reference)

The easiest way to pull data from another sheet is to create a direct link between cells. This method is perfect when you need a cell in one sheet to always mirror the value of a specific cell in another. It’s ideal for bringing over a grand total from a data sheet to a summary dashboard, for instance.

Here’s how to do it step-by-step:

1. Navigate to the cell where you want the linked data to appear (we’ll call this your destination cell). Let's say it's cell B2 in your "Summary" sheet.
2. Type an equals sign (=) to start your formula.
3. Without pressing Enter, click on the tab for the worksheet containing the source data (the source sheet). Let's call it "SalesData."
4. Click on the cell whose value you want to pull (the source cell). For example, click on cell F50, which might contain your total sales amount.
5. You will see the formula bar show something like =SalesData!F50.
6. Press Enter. Excel will immediately take you back to your destination cell in the "Summary" sheet, which will now display the value from cell F50 of the "SalesData" sheet.

That's it! Now, any time the value in SalesData!F50 changes, the value in Summary!B2 will update automatically. This simple, direct link is the backbone of many Excel reports and dashboards.

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: Using VLOOKUP to Pull Specific Data

What if you don't want to just mirror a single cell? What if you need to find a specific piece of information in a large table? That's where VLOOKUP (Vertical Lookup) comes in. This is one of Excel’s most popular functions, and it's perfect for pulling related data from a table based on a unique identifier.

Imagine you have a "ProductList" sheet with product IDs, names, and prices, and you want to pull the price into your "Invoice" sheet by just typing the product ID. VLOOKUP is the right tool for the job.

Understanding the VLOOKUP Formula

The VLOOKUP formula has four parts (arguments):

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

• lookup_value: What you’re looking for (e.g., the product ID, "P-1001").
• table_array: Where to look. This is the entire data table on your other sheet (e.g., all the data in ProductList!A2:C100). Important: The column containing your lookup_value must be the first column in this range.
• col_index_num: Which column's data to return from the table_array, once a match is found. If you want the product price, and that's the 3rd column in your table, you'd enter 3.
• [range_lookup]: Optional. Use FALSE for an exact match (most common) or TRUE for an approximate match. You will almost always want to use FALSE to prevent incorrect matches.

VLOOKUP in Action: Step-by-Step Example

Let's say you have these two sheets:

Sheet 1: Products

• Column A: ProductID
• Column B: ProductName
• Column C: UnitPrice

Sheet 2: SalesForm

• Column A: You enter a ProductID (e.g., in cell A2).
• Column B: You want the ProductName to appear automatically.
• Column C: You want the UnitPrice to appear automatically.

To automatically populate the ProductName in SalesForm, click on cell B2 and enter this formula:

=VLOOKUP(A2, Products!A:C, 2, FALSE)

Breaking it down:

• A2: Look for the ProductID entered in cell A2.
• Products!A:C: Search in columns A to C on the "Products" sheet.
• 2: Return the value from the second column of that range (ProductName).
• FALSE: Look for an exact match.

For the UnitPrice in cell C2, you would use a similar formula, just changing the column index number:

=VLOOKUP(A2, Products!A:C, 3, FALSE)

Now, anytime you type a valid ProductID in column A of your SalesForm, a VLOOKUP will fetch the correct name and price automatically.

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 3: The Modern Way with XLOOKUP

If you're using a newer version of Excel (Excel 2021 or Microsoft 365), you have access to XLOOKUP. Think of it as the powerful, more flexible successor to VLOOKUP. It solves many of VLOOKUP's limitations.

Why XLOOKUP is an Improvement:

• It can look to the left. With VLOOKUP, your lookup value must be in the first column of your data table. XLOOKUP doesn't care, it can look in any column and return data from a column to its left.
• Simpler syntax. You pick the lookup column and the return column separately, which is more intuitive.
• Defaults to an exact match. No need to remember to type FALSE at the end.

XLOOKUP Formula Breakdown

The basic XLOOKUP syntax is:

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

• lookup_value: The value you’re looking for (e.g., cell A2).
• lookup_array: The single column or row where you expect to find the lookup value (e.g., Products!A:A).
• return_array: The single column or row with the data you want back (e.g., Products!B:B for the product name).
• [if_not_found]: Optional. A value to show if no match is found (e.g., "Product Not Found"). This is much cleaner than wrapping your formula in an IFERROR function.

Using XLOOKUP in Our Example

Using the same Products and SalesForm sheets, here is how you’d use XLOOKUP to get the ProductName:

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

And for the UnitPrice:

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

The logic is much clearer: "Look for value A2 in column A of Products, and when you find it, give me the corresponding value from column C." If you have access to it, XLOOKUP is almost always a better choice than VLOOKUP.

Method 4: The Powerful Combination of INDEX and MATCH

Before XLOOKUP existed, the preferred method for advanced Excel users to overcome VLOOKUP's limitations was to combine two functions: INDEX and MATCH. This combination is still incredibly useful, especially if you're working with older versions of Excel.

Here’s a quick overview:

• MATCH: This function finds the position of a value within a range and returns it as a number. For example, MATCH("P-1003", Products!A:A, 0) might return 4, meaning "P-1003" is the 4th item in column A. The 0 specifies an exact match.
• INDEX: This function returns the value at a given position within a range. For example, INDEX(Products!C:C, 4) would return the value of the 4th cell in column C, which is the product price.

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

Putting INDEX and MATCH Together

When you combine them, you use MATCH to dynamically find the row number, and then you feed that number into INDEX to retrieve the value from the correct column.

The formula looks like this:

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

Using our example to get the ProductName:

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

Breaking it down:

1. MATCH(A2, Products!A:A, 0): Finds the row number where the ProductID from cell A2 exists in Products!A:A.
2. INDEX(Products!B:B, ...): Retrieves the value from Products!B:B at that row number (the product name).

While more complex to write, this method is more robust than VLOOKUP because it's not affected by inserting or deleting columns in your source data sheet.

Final Thoughts

Mastering how to auto-populate data pulls you out of the repetitive cycle of copy-and-paste and turns your static spreadsheets into dynamic reports. Whether you use a simple cell reference, VLOOKUP, or the more modern XLOOKUP, you are creating a reliable, single source of truth that saves time and dramatically reduces human error.

While these Excel functions are powerful, they often solve a problem that starts further upstream: getting all your data into one place. Manually exporting CSVs from tools like Shopify, Google Analytics, and Facebook Ads just to do reporting in Excel is a time-consuming chore. At Graphed, we are built to solve this exact problem. We create a seamless bridge between all your scattered data sources, so you get real-time, automatically updated dashboards without ever touching a spreadsheet. Instead of building fragile VLOOKUPs, you can simply ask a question in plain English, like "Show me my top-performing products by revenue this month," and get an instant visualization.