How to Input Data from Another Sheet in Excel

Manually copying and pasting data between sheets in Excel is not only tedious but also a recipe for errors. Instead of rebuilding reports every week, you can create dynamic connections that pull data from one sheet to another automatically. This article will show you several ways to input data from another worksheet, ranging from simple direct links 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

The Easiest Method: Direct Cell Linking

The simplest way to get data from another sheet is to create a direct link to a specific cell. When the data in the original cell changes, the cell with the formula automatically updates. Think of this as creating a live mirror of one cell in another.

How to Create a Simple Cell Reference

Let’s say you have a Summary sheet and want to pull the "Total Sales" figure from cell F10 on a sheet named Sales Data.

1. Click on the cell in your Summary sheet where you want the total sales figure to appear (e.g., cell B2).
2. Type the equals sign: =
3. Without clicking anywhere else, navigate to the Sales Data sheet by clicking on its tab at the bottom of the worksheet.
4. Click on the cell you want to reference (cell F10).
5. Press Enter.

You’ll be taken back to your original sheet, and you'll see the formula 'Sales Data'!F10 in the formula bar. The value from Sales Data!F10 will now appear in Summary!B2. If the value in F10 on the sales sheet ever changes, your summary sheet will update instantly.

When to Use Direct Cell Linking

This method is great for dashboard-style summary sheets where you need to pull specific, high-level numbers or key performance indicators (KPIs) from various other sheets into one place. It’s quick, straightforward, and works perfectly for one-off values.

• Pros: Incredibly easy and fast, updates in real-time.
• Cons: Brittle. If you insert or delete a row or column in the source sheet in a way that moves the target data, the formula will still point to the old cell location and display the wrong information.

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

Find What You Need with VLOOKUP

Often, you don't just want to link to a fixed cell. You need to find a specific piece of information in a large table on another sheet. For instance, you might want to pull the price for "Product A" from a master price list on a separate Products sheet. This is where VLOOKUP (Vertical Lookup) shines.

VLOOKUP scans down the first column of a table to find a specific value you provide, then moves across that row to grab a corresponding value from another column.

Understanding the VLOOKUP Formula

The syntax for VLOOKUP can seem intimidating at first, but it makes sense once you break it down:

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

• lookup_value: The piece of information you already know. This is what you're searching for (e.g., a product ID, a person's name).
• table_array: The range of cells on the other sheet that contains both the value you're looking for and the information you want to retrieve. The lookup value must be in the first column of this range.
• col_index_num: The column number (within the table_array) from which to pull the result. The first column of your table is 1, the second is 2, and so on.
• [range_lookup]: This is where you tell Excel whether you want an exact match or an approximate match. In 99% of cases, you'll use FALSE for an exact match.

Step-by-Step VLOOKUP Example

Imagine you have a sales invoice on a sheet named Invoice and a master product list on a sheet named Price List. You want to type a Product ID in column A of the invoice and have the price automatically appear in column C.

Your Price List sheet looks like this:

• Column A: Product ID (e.g., "PROD-101")
• Column B: Product Name (e.g., "Wireless Mouse")
• Column C: Price (e.g., "$29.99")

Here’s how to build the formula on the Invoice sheet:

1. On your Invoice sheet, click on cell C2 (where you want the price).
2. Type the formula: =VLOOKUP(A2, 'Price List'!A:C, 3, FALSE)
3. Press Enter. Drag this formula down for other products.

Let’s break that down:

• A2 is the product ID on our Invoice sheet we're searching for.
• 'Price List'!A:C tells Excel to look in columns A through C on the Price List sheet.
• 3 means we want the result from the 3rd column of our table (the Price column).
• FALSE ensures we only get a result if it finds an exact match for the product ID. If not, it returns a #N/A error.

A More Flexible Lookup: INDEX and MATCH

While VLOOKUP is a trusted classic, it has a few famous limitations. Its biggest weakness is that it can only look up values to the right of the lookup column. If your product prices were in column A and the product IDs in column C, VLOOKUP wouldn't work. It's also known to break if you insert or delete columns within the source table.

The combination of the INDEX and MATCH functions is more powerful, flexible, and robust.

• MATCH finds the position of your lookup value in a column (e.g., it tells you "Product A" is in the 5th row).
• INDEX then retrieves the value from another column at that same position.

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

How to Use INDEX and MATCH Together

Using the same example as before, here's how you'd build the formula:

=INDEX('Price List'!C:C, MATCH(A2, 'Price List'!A:A, 0))

Let's dissect this from the inside out:

• MATCH(A2, 'Price List'!A:A, 0): Looks for the product ID from cell A2 (Invoice sheet) inside column A of the Price List sheet. The 0 signifies an exact match. If it finds "PROD-101" in the 12th row, it returns 12.
• INDEX('Price List'!C:C, ...): Takes that row number and fetches the value from the 12th row of column C on Price List, which contains the price.

This combo is considered best practice by many Excel pros because it doesn't break if you rearrange your source data and is more efficient with large datasets.

The Modern Solution: XLOOKUP

If you're using a newer version of Excel (Microsoft 365, Excel 2021, or Excel for the web), you have access to XLOOKUP. This function was designed to replace VLOOKUP and INDEX/MATCH by combining their power into a much simpler, more intuitive formula.

The basic XLOOKUP syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

• lookup_value: What you're looking for (e.g., A2).
• lookup_array: The column/row where your lookup value lives (e.g., 'Price List'!A:A).
• return_array: The column/row containing the result you want (e.g., 'Price List'!C:C).

As you can see, you just point it to the lookup column and the return column. No more counting column numbers!

Applying it to our example, the formula becomes shockingly simple:

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

XLOOKUP also has more advanced optional arguments, like a built-in "if not found" message, making IFERROR wrappers unnecessary. It's the new standard for a reason.

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

Bringing Your Data Together with Named Ranges

As your formulas get longer and refer to different sheets, they can become hard to read. Using 'Price List'!A:A is clear enough, but what if your reference was 'Q4-Financials-Final-v2'!$C$2:$C$568? This is where Named Ranges become incredibly helpful.

A Named Range lets you assign a memorable, plain-English name to a cell or range of cells.

How to Create a Named Range:

1. Go to the source sheet (e.g., Price List).
2. Highlight the range of cells you want to name. For example, select all of column A.
3. Click into the Name Box, which is the small white box to the left of the formula bar.
4. Type a name for your range, like Product_IDs, and press Enter. Make sure there are no spaces in the name.
5. Repeat the process for column C, naming it Product_Prices.

Now, you can rewrite your lookup formulas to be much more readable. The INDEX/MATCH formula from before can be written as:

=INDEX(Product_Prices, MATCH(A2, Product_IDs, 0))

This is far easier to understand and debug. When you build complex models, using named ranges is a non-negotiable best practice.

Final Thoughts

You have several effective methods for pulling data from another sheet in Excel. Direct cell linking is perfect for simple dashboard totals, while VLOOKUP, INDEX/MATCH, and XLOOKUP offer powerful ways to search for specific data within tables. Mastering these techniques will help you create more automated, accurate, and manageable spreadsheets.

As powerful as these formulas are, they still rely on manually structuring and maintaining data within spreadsheets. If you want to connect directly to your business apps - like Google Analytics, Shopify, or Salesforce - and build reports without wrestling with cell references, our platform might be what you're looking for. With Graphed, we turn hours of manual data wrangling into a simple conversation, so you can just ask a question in plain English and instantly get the dashboard you need.