How to Add Data from a Different Sheet in Excel

Wrangling data across multiple Excel sheets can feel like a chore, but it’s a necessary step for building useful summary reports and dashboards. Instead of manually copying and pasting values - which is tedious and prone to error - you can pull data dynamically from one sheet into another. This guide walks you through several methods for referencing data from different sheets in Excel, from simple cell 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

Before You Start: Understanding Sheet References

The key to working across sheets in Excel is knowing how to tell a formula where to look. The basic syntax is simple: you write the sheet name, followed by an exclamation point, and then the cell or range reference.

For example, to reference cell A1 on a sheet named "SalesData," the reference would be:

=SalesData!A1

If your sheet name contains spaces or special characters, you need to enclose it in single quotes. For example, if your sheet is named "Q1 Sales Report," the reference would be:

='Q1 Sales Report'!A1

With that foundation, let's look at the different ways you can use this syntax to build connected reports.

Method 1: The Simple Cell Link

The most straightforward way to add data from a different sheet is to create a direct link to a specific cell. This is perfect when you have an aggregated value (like a grand total) on one sheet that you want to display on another, such as a summary or dashboard sheet.

Think of it as creating a live connection. When the source cell updates, the cell referencing it 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

How to Do It:

1. Navigate to the sheet where you want the data to appear (let's call it "Summary").
2. Select the cell where you want to display the value (e.g., cell B2).
3. Type the equals sign (=) to begin your formula.
4. Without pressing Enter, use your mouse to click on the tab for the source sheet (e.g., "Sales").
5. Click on the specific cell you want to reference (e.g., E25, which might contain your total revenue).
6. Press Enter.

Excel automatically writes the correct formula for you in the "Summary" sheet's B2 cell, which will look something like this:

=Sales!E25

Now, any changes made to cell E25 on the "Sales" sheet will be instantly reflected in cell B2 on your "Summary" sheet. This is the simplest method and works great for one-off values.

Method 2: Finding Data with VLOOKUP

What if you don't just want one static cell, but need to find a specific piece of information within a large table on another sheet? This is a common scenario. For instance, you might have a sheet of orders with just a "Product ID" and a separate "Products" sheet with all the product details. You can use VLOOKUP to pull the product name or price into your orders sheet.

VLOOKUP scours the first column of a table for a value you specify and returns a corresponding value from another column in that same row.

The VLOOKUP Formula Explained:

The formula structure is: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The piece of information you're looking for (e.g., the Product ID in your orders sheet).
• table_array: The range of data to search in, located on your other sheet (e.g., the entire product table on the "Products" sheet). It's crucial to lock this range with dollar signs ($) so it doesn't shift when you drag the formula down.
• col_index_num: The number of the column within your table_array that holds the data you want to return. The first column of your table is 1, the second is 2, and so on.
• range_lookup: Typically, you'll use FALSE here, which tells Excel to look for an exact match.

Example: Looking Up a Product Name

Let's say you have:

• A sheet named "Orders" with Product IDs in column B, starting at B2.
• A sheet named "Products" where column A has the Product IDs and column C has the Product Names. The data spans from A2 to D100.

On the "Orders" sheet, in cell C2, you would enter this formula to grab the product name:

=VLOOKUP(B2, Products!$A$2:$D$100, 3, FALSE)

Let's break that down:

• B2: Look for the value from cell B2 in our "Orders" sheet.
• Products!$A$2:$D$100: Search for it in the table on the "Products" sheet from A2 to D100.
• 3: Return the value from the 3rd column of that table (the Product Names).
• FALSE: Ensure an exact match.

You can then drag this formula down from C2 for all your orders.

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: A More Flexible Alternative with INDEX and MATCH

VLOOKUP is a workhorse, but it has a significant limitation: it can only look up values to the right of the initial lookup column. For more advanced or flexible lookups (including looking to the left), combining the INDEX and MATCH functions is superior.

This may sound complex, but the combination is incredibly powerful once you understand how it works.

• MATCH: Tells you the row number where a specific value is found within a range.
• INDEX: Fetches the value from a specific row and column number within a range.

By nesting MATCH inside INDEX, you tell Excel: "First, find the row for my item, then give me the value from a different column in that exact row."

Example: Using INDEX and MATCH

Using our previous product example, let's imagine the "Products" sheet has Product Names in column A and Product IDs in column C. VLOOKUP wouldn't work because it can't look left. But INDEX/MATCH can handle it easily.

On your "Orders" sheet, your goal is to get the Product Name from column A on the "Products" sheet based on the Product ID in column C.

This is the formula you'd use in cell C2 of your "Orders" sheet:

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

Here’s how it works from the inside out:

1. MATCH(B2, Products!$C:$C, 0): This part searches for the Product ID from cell B2 (on the "Orders" sheet) in all of column C on the "Products" sheet. The 0 specifies an exact match. Let's say it finds it in row 45. The MATCH function returns the number 45.
2. INDEX(Products!$A:$A, 45): Now the formula is simplified. It tells Excel to go to column A on the "Products" sheet and retrieve the value from the 45th row - which is our corresponding Product Name.

Method 4: Aggregating Data with SUMIF or COUNTIF

Sometimes you don't need to pull in an individual piece of data, you need to sum or count values from another sheet based on a specific condition. For this, functions like SUMIF, SUMIFS, COUNTIF, and COUNTIFS are perfect.

Let's say you have a raw data export of all transactions on a sheet named "RawData" and you want to build a "Dashboard" sheet that calculates total sales per region.

The SUMIF Formula Explained:

The structure is: SUMIF(range, criteria, [sum_range])

• range: The range of cells on the other sheet that you want to check against your criteria (e.g., the column containing region names).
• criteria: The condition you want to meet (e.g., the specific region "North").
• sum_range: The range of corresponding cells to add up if the criteria are met (e.g., the column containing sales amounts).

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

Example: Summing Regional Sales

Imagine your "RawData" sheet has:

• Region names in column D.
• Sales values in column H.

On your "Dashboard" sheet, you have the region name "North" in cell A2. In cell B2, you could enter this formula to calculate total sales for that region:

=SUMIF(RawData!$D:$D, A2, RawData!$H:$H)

This formula tells Excel to scan column D on the "RawData" sheet, find all rows that match the value in A2 ("North"), and sum the corresponding values from column H.

Practical Tips for Cross-Sheet Formulas

Whichever method you choose, a few best practices will help you avoid common errors and keep your workbook organized.

• Keep Sheet Names Simple: Avoid spaces and special characters in sheet names if you can. A name like SalesData is easier to reference than Q1-Sales Raw Data (Final). If you must use spaces, remember the single quotes.
• Use Named Ranges: For tables or cells you reference often, define a name (Formulas > Name Manager). For example, if you name your product table ProductTable, your VLOOKUP formula becomes =VLOOKUP(B2, ProductTable, 3, FALSE), which is far more readable.
• Lock Your Ranges: When using lookup functions, always use absolute references (like $A$2:$D$100) for your lookup tables. This prevents the range from shifting incorrectly as you copy the formula to other cells.
• Be Careful When Deleting: If you delete a sheet, cell, or range that is referenced elsewhere, your formulas will return a #REF! error. Always check for formula dependencies before deleting.

Final Thoughts

Pulling data from different sheets is fundamental to making sense of disorganized data in Excel. From simple cell links for dashboard totals to dynamic lookups with VLOOKUP or INDEX/MATCH and conditional sums with SUMIF, these techniques allow you to create structured, interconnected, and automated reports that stay up-to-date without constant manual intervention.

While mastering these formulas is a huge step up from manual copy-pasting, we know that building reports is often bigger than just one spreadsheet. Your sales data might be in Salesforce, your web traffic in Google Analytics, and your revenue in Shopify. We built Graphed to solve this exact problem. It connects all your data sources in one place, so instead of wrangling VLOOKUPS across multiple exports, you can just ask in plain English, "Show me a chart of Shopify revenue broken down by Google Ads campaign," and get an answer in a real-time dashboard instantly.