How to Populate Data from Another Sheet in Excel

Tired of manually copying and pasting values from one Excel sheet to another? It's a tedious process that's prone to errors and guarantees your reports are instantly out of date. This article will show you how to automatically populate data from another sheet in Excel, saving you time and keeping your data accurate. We'll cover everything from the simplest direct link to more powerful lookup functions like VLOOKUP and XLOOKUP.

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

Why You Should Stop Copying and Pasting Data Between Sheets

Before jumping into the "how," it's important to understand the "why." Manually updating cells across sheets isn't just annoying, it's a risky way to manage your data. Here are the main problems:

• It's easy to make mistakes. A single typo, a copy-paste error, or grabbing data from the wrong cell can throw off an entire report. These tiny mistakes can be incredibly difficult to track down later.
• Your data becomes outdated instantly. The second the source data changes, your manually copied value is wrong. This forces you into a constant cycle of checking and re-copying, and decision-makers might be working with old information.
• It consumes a huge amount of time. The hours spent hunting for data, cross-referencing sheets, and updating summary tables could be spent on actual analysis and strategy.

Automating this process solves all three issues. It creates a live, direct link to your source data, ensuring accuracy, saving time, and giving your confidence in your reports.

The Simplest Method: Direct Cell Referencing

The most straightforward way to pull data from another sheet is to create a direct link to a specific cell. This is perfect when you know exactly which cell contains the data you need, like a summary total or a key performance indicator (KPI).

How it Works

The formula syntax is simple and intuitive. You just need to tell Excel the name of the sheet and the cell address you want to pull from.

=SheetName!CellAddress

For example, if you want the value from cell C10 in a sheet named "SalesData," your formula would be =SalesData!C10. If your sheet name has spaces or special characters, Excel will automatically add single quotes around it, like this: 'Sales Data'!C10.

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

Step-by-Step Example

Let's imagine you have a workbook with two sheets: "Monthly Traffic" and "Annual Report." You want to pull the total traffic for January from the "Monthly Traffic" sheet into your "Annual Report" summary.

1. In your "Monthly Traffic" sheet, let's say the total traffic for January is in cell F35.
2. Switch to your "Annual Report" sheet. Click on the cell where you want the January traffic total to appear (let's say it's cell B2).
3. Instead of typing the formula manually, you can use your mouse. Start by typing the equals sign (=) in cell B2.
4. Without pressing Enter, click on the "Monthly Traffic" sheet tab at the bottom of the window.
5. Click on cell F35. You'll see Excel build the formula for you in the formula bar.
6. Press Enter.

That's it! Excel will take you back to your "Annual Report" sheet, and cell B2 will now display the value from cell F35 on the "Monthly Traffic" sheet. Better yet, if the number in "Monthly Traffic!F35" ever changes, the value in "Annual Report!B2" will update automatically.

Finding Data Dynamically: Using VLOOKUP

Direct cell referencing is great for static totals, but what if you need to find a specific piece of information in a large table? Manually finding the right row would take forever. This is where VLOOKUP comes in.

VLOOKUP (Vertical Lookup) searches for a value in the first column of a data table and returns a corresponding value from a different column in that same row. It's one of the most essential functions for anyone managing data in Excel.

Breaking Down the VLOOKUP Formula

The VLOOKUP formula has four arguments:

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

• lookup_value: This is the piece of data you know. For instance, a Product ID, a customer email, or an employee name.
• table_array: This is the range of data where you want to search. It’s crucial that the column containing your lookup_value is the first column in this range. A pro tip is to make this an absolute reference (using dollar signs, e.g., $A$1:$D$100) so it doesn't shift if you drag the formula down.
• col_index_num: This is the numerical index of the column from which you want to pull a value, counting from the left side of your table_array.
• range_lookup: This is an optional argument, but for most business cases, you should use FALSE. This tells Excel to look for an exact match to your lookup_value. If you omit it or use TRUE, Excel searches for an approximate match, which can lead to unexpected results.

A Practical Example

Imagine you have two sheets: "Order Details" and "Product Prices." The "Order Details" sheet lists order IDs and product IDs, but it doesn't include the price. The prices live in your "Product Prices" sheet.

Here’s how to use VLOOKUP to pull the price for each order:

1. Your "Product Prices" sheet has product IDs in column A and their corresponding prices in column C.
2. In your "Order Details" sheet, the product ID for the first order is in cell B2. You want to populate the price in cell C2.
3. Click on cell C2 and start typing your formula: =VLOOKUP(
4. For the lookup_value, click on cell B2. A comma follows: =VLOOKUP(B2,
5. For the table_array, click over to the "Product Prices" sheet and select the entire data range, from the first column (Product ID) to the last relevant column (e.g., A2:C100). Press F4 on your keyboard to make it an absolute reference ($A$2:$C$100). This prevents the range from changing as you copy the formula down. A comma follows: =VLOOKUP(B2, 'Product Prices'!$A$2:$C$100,
6. For the col_index_num, you want the price, which is in the 3rd column of the selected range. So, you'll type 3. A comma follows: =VLOOKUP(B2, 'Product Prices'!$A$2:$C$100, 3,
7. Finally, for range_lookup, type FALSE for an exact match and close the parenthesis.

Your finished formula looks like this:

=VLOOKUP(B2, 'Product Prices'!$A$2:$C$100, 3, FALSE)

Press Enter, and Excel will find the corresponding price. You can then drag the small square in the bottom-right corner of cell C2 all the way down to apply this formula to every order.

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 Modern Upgrade: Using XLOOKUP

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

Why XLOOKUP is Better

• It can look left. Unlike VLOOKUP, which can only search in the first column and return data from columns to its right, XLOOKUP can return data from anywhere, including columns to the left of the lookup column.
• It's more intuitive. You specify a lookup_array and a return_array separately, so you don't need to count columns anymore.
• It has a built-in error handler. You can tell XLOOKUP what to return if it doesn't find a match, eliminating the need for pesky IFERROR functions.

Breaking Down the XLOOKUP Formula

The syntax is much simpler for a basic lookup:

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

XLOOKUP in Action

Let's use our previous example, but now imagine the "Product Prices" sheet is poorly formatted: prices are in column A, and product IDs are in column C. VLOOKUP would fail here, but XLOOKUP handles it with ease.

1. In the "Order Details" sheet, click on cell C2.
2. The formula you'll build is:

=XLOOKUP(B2, 'Product Prices'!$C$2:$C$100, 'Product Prices'!$A$2:$A$100, "Price Not Found")

• B2 is your lookup value (the product ID).
• 'Product Prices'!$C$2:$C$100 is the lookup_array (where Excel should search for the product ID).
• 'Product Prices'!$A$2:$A$100 is the return_array (where the prices are).
• "Price Not Found" is the optional custom text if the product isn't found.

This is not only more powerful but also easier to read and troubleshoot. If your version of Excel supports it, XLOOKUP should be your go-to lookup function.

The Classic Power Duo: INDEX and MATCH

Before XLOOKUP existed, the most powerful way to perform complex lookups was by combining two functions: INDEX and MATCH. While XLOOKUP is simpler, you'll still find INDEX/MATCH used in countless spreadsheets, so it's valuable to understand how it works.

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

How They Work Together

Think of it as a two-step process:

1. MATCH finds the position (the row number) of your lookup value within a specific range.
2. INDEX retrieves the value at that specific position (that row number) from another range.

Combined, they perform a dynamic and efficient lookup that isn't restricted by column order. The final formula structure looks like this:

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

Step-by-Step Example

Let's go back to our "look to the left" scenario where prices are in column A and product IDs are in column C on the "Product Prices" sheet.

1. The MATCH part: First, you need to find the row number of the product. The formula would be: =MATCH(B2, 'Product Prices'!$C$2:$C$100, 0) This looks for the product ID from cell B2 inside the product ID column (C2:C100) and returns its relative row number. The 0 specifies an exact match.
2. The INDEX part: Now you need to retrieve the price. The INDEX function would be: =INDEX('Product Prices'!$A$2:$A$100, ...)

You then nest the MATCH function inside the INDEX function where the row number should be:

=INDEX('Product Prices'!$A$2:$A$100, MATCH(B2, 'Product Prices'!$C$2:$C$100, 0))

This formula tells Excel: "Go find the row number of this product ID, then give me the value from that same row in the price column." It's more complex than XLOOKUP but achieves the same powerful result.

Final Thoughts

By moving away from manual data entry and embracing functions like direct referencing, VLOOKUP, XLOOKUP, or INDEX/MATCH, you're making your spreadsheets more reliable, accurate, and efficient. These methods build a direct link to your source data, ensuring your reports are always up-to-date and freeing you up to focus on what the numbers actually mean.

Manually connecting Excel sheets is a critical skill, but for marketers and business owners managing data across multiple platforms, it’s just one piece of the puzzle. That's why we created Graphed. We wanted to skip the spreadsheet wrangling entirely by connecting directly to tools like Google Analytics, Shopify, and Facebook Ads. Instead of writing formulas, you can just ask questions in plain English, like "Show me a chart of my top 5 ad campaigns by revenue last month," and get a live, automated dashboard in seconds, not hours.