How to Insert Data from Another Sheet in Excel

Pulling data from one Excel sheet into another is how you turn a simple spreadsheet into a dynamic dashboard or a powerful summary report. Instead of manually copying and pasting information - which is tedious and prone to errors - you can create live links that update automatically. This article will walk you through several methods for inserting data from another sheet, from basic linking to advanced lookups.

The Basics: How Excel References Other Sheets

Before diving into specific methods, it's helpful to understand Excel's core syntax for referencing a cell on a different worksheet. The formulaic structure is simple:

SheetName!CellReference

The exclamation mark (!) is the key component, it acts as a separator, telling Excel to look on a different sheet. For example, to reference cell B5 on a sheet named "SalesData", you would use:

=SalesData!B5

If your sheet name contains spaces or special characters, you need to enclose it in single quotes:

='Q1 Sales Report'!B5

While you can type this out, the easiest way is to let Excel do the work for you. Start a formula with =, then simply click on the other sheet's tab and select the cell you want. Excel will automatically write the correct reference for you.

Method 1: Direct Cell Referencing (Fast and Simple)

This is the most common and straightforward way to link cells between sheets. It's perfect for when you need to mirror a value from one place to another, like pulling a total from a detailed calculation sheet into a high-level summary dashboard.

Let’s say you have a workbook with separate sheets for each month's sales ("Jan_Sales," "Feb_Sales") and a summary sheet called "Annual Overview." You want to pull the grand total from cell F50 on the "Jan_Sales" sheet into cell B2 of your "Annual Overview" sheet.

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

1. Click on the cell where you want the data to appear (in our example, cell B2 on the "Annual Overview" sheet).
2. Type the equals sign (=) to begin your formula.
3. Click on the tab for the source sheet ("Jan_Sales"). The sheet will become active, but your formula bar will still show you're editing cell B2 on the original sheet.
4. Click on the cell containing the data you want to display (cell F50).
5. Press the Enter key. Excel will automatically return you to the "Annual Overview" sheet, and cell B2 will now display the value from "Jan_Sales!F50".

The formula in cell B2 will be =Jan_Sales!F50. If the value in Jan_Sales!F50 ever changes, the value in your summary sheet will update instantly. You can now drag the fill handle down or across to link to other cells relatively, just like any other formula in Excel.

Method 2: Using ‘Paste Link’ for Quick Connections

The Paste Link feature allows you to accomplish the same direct linking as Method 1, but through a copy-and-paste action. It’s slightly faster if you are already navigating the source sheet and decide you want to link a cell somewhere else.

Using the same example, here’s how to use Paste Link:

1. Navigate to your source sheet, "Jan_Sales."
2. Click on the cell or range of cells you want to link (e.g., F50).
3. Copy the cell(s) by pressing Ctrl+C (or Cmd+C on Mac).
4. Go to your destination sheet, "Annual Overview."
5. Right-click the destination cell (B2) where you want to paste the link.
6. In the context menu under Paste Options, look for the icon with two links of a chain. Alternatively, go to Paste Special and click the "Paste Link" button at the bottom left.

Excel will paste the link, not the value. The formula it creates will be an absolute reference, like =Jan_Sales!$F$50. The dollar signs lock the reference, which means if you drag the formula to other cells, it will continue to point to F50 instead of adjusting relatively. This is useful for fixed dashboard values but less so for creating dynamic tables.

Method 3: Finding and Inserting Data with VLOOKUP

Direct linking is great, but what if you need to find a specific piece of data in a large table and pull it into your sheet? This is where lookup formulas shine. VLOOKUP (Vertical Lookup) is a classic Excel function designed for exactly this purpose.

Imagine you have a master list of products on a sheet called "Product_List" with columns for Product ID, Product Name, and Price. On your "Invoice" sheet, you want to type a Product ID into a cell and have Excel automatically fill in the Product Name and Price for you.

Here’s the anatomy of the VLOOKUP function:

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

• lookup_value: The piece of information you're searching for (e.g., the Product ID on your invoice).
• table_array: The range of data on the other sheet where you're searching. Important: VLOOKUP requires the lookup value to be in the very first column of this range.
• col_index_num: The column number in your table_array from which you want to return a value. The first column is 1, the second is 2, and so on.
• range_lookup: A true/false value. Use FALSE for an exact match, which is what you'll want 99% of the time.

VLOOKUP Example in Practice

Let's find the Product Name.

1. On the "Invoice" sheet, your Product ID (e.g., "P101") is in cell A2. You want to pull the matching name into cell B2.
2. In cell B2, you’ll write this formula:

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

Let’s break it down:

• A2 is the value we're looking for ("P101").
• Product_List!A:C tells Excel to look in columns A through C on the "Product_List" sheet. Column A must contain the Product IDs.
• 2 tells Excel to return the value from the second column of that table (the Product Name).
• FALSE ensures we only get a result if it finds an exact match for "P101".

To get the price in cell C2, you’d use a nearly identical formula, just changing the column index number to 3 to pull from the third column.

Method 4: Upping Your Game with INDEX and MATCH

While VLOOKUP is powerful, it has limitations. It can only search in the first column of a table, and it can break if you add or remove columns from your source data. For these reasons, many Excel pros prefer a more flexible duo: INDEX and MATCH.

• MATCH finds the position (the row number) of an item in a list.
• INDEX retrieves a value from a list at a specific position.

Combining them, you use MATCH to find the row of your lookup value, and then feed that row number into INDEX to retrieve a value from the correct column in that row. This combo is faster, less resource-intensive on big datasets, and far more adaptable.

Here’s the structure:

=INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type]))

INDEX/MATCH Example

Using the same product list, let’s find the price for the Product ID in A2. This time, our data is structured differently: Product Name is in column A, Price is in column B, and Product ID is in column C. VLOOKUP would fail here because it can't look to the left.

Your formula would look like this:

=INDEX(Product_List!B:B, MATCH(A2, Product_List!C:C, 0))

Here's how it works from the inside out:

• MATCH(A2, Product_List!C:C, 0): This searches for the value from cell A2 (the Product ID) within column C on the "Product_List" sheet. The 0 specifies an exact match. Let’s say it finds a match in row 15. The MATCH function will return the number 15.
• INDEX(Product_List!B:B, ...): This takes that result (15) and looks in column B of the "Product_List" sheet. It goes down to the 15th row and returns the value from that cell - the correct price.

This is far more robust because the lookup and return columns are independent. You can insert or delete columns without breaking your formula.

Method 5: The Modern Solution with XLOOKUP

Available in Microsoft 365 and Excel 2021, XLOOKUP is the modern, simplified replacement for both VLOOKUP and INDEX/MATCH. It combines the ease of VLOOKUP with the power of INDEX/MATCH, and adds a few extra useful features.

The basic syntax is incredibly intuitive:

=XLOOKUP(lookup_value, lookup_array, return_array)

• lookup_value: What you’re looking for.
• lookup_array: The column/row where you're looking.
• return_array: The column/row from which you want the answer.

XLOOKUP defaults to an exact match, so there’s no need to add FALSE or 0. It can also handle arrays, search from the right or left, and has a built-in "if not found" argument.

XLOOKUP Example

Finding the product name is now as simple as:

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

This formula tells Excel to:

1. Look for the value in A2.
2. Search for it in all of column C on the "Product_List" sheet.
3. Once found, return the corresponding value from column A on that sheet.
4. If nothing is found, display "Product Not Found" instead of the ugly #N/A error.

If you have access to it, XLOOKUP should be your go-to method for any kind of dynamic data lookup between sheets.

Final Thoughts

Learning how to insert data from another Excel sheet is a foundational skill that transforms the software from a static grid into an interconnected analysis tool. From simple direct cell linking to powerful dynamic lookups with XLOOKUP, these methods automate your reports, reduce manual errors, and keep your data clean and synchronized.

While mastering these functions is great for managing data within Excel, the next challenge is often connecting data from entirely different platforms. Hours are lost exporting CSVs from Google Analytics, Shopify, Facebook Ads, and Salesforce just to consolidate them into a spreadsheet. With Graphed we automate that entire data-wrangling process. By connecting your sources with a few clicks, you can instantly build dashboards and ask questions in plain English - like "Compare Facebook ad spend vs. Shopify revenue by campaign" - letting you skip the formulas and get straight to the insights.