How to Use Data from a Different Sheet in Excel

Working with data spread across multiple sheets is a fundamental skill in Excel, but it can quickly become confusing. Whether you're trying to build a summary dashboard or just pull a single number from another tab, knowing how to link your sheets is essential. This guide will walk you through several methods for using data from different sheets, ranging from a simple click-and-link to more powerful and dynamic formulas.

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 1: The Basics of Direct Cell Referencing

The simplest way to use data from another sheet is by creating a direct link to a specific cell. This is perfect for when you have a key total or a piece of information on one sheet that you want to display on another, like a dashboard.

A reference to a cell in another sheet has a specific syntax: SheetName!CellAddress. The sheet name is followed by an exclamation mark, which tells Excel to look on that particular sheet for the cell address you provide.

Let's say you have a sheet named "Q4_Sales" and you want to pull the total sales figure from cell F22 into your "Annual_Summary" sheet.

How to Create a Direct Link

1. Click on the cell in your "Annual_Summary" sheet where you want the Q4 sales total to appear.
2. Type the equals sign (=) to begin your formula.
3. Using your mouse, click on the "Q4_Sales" sheet tab at the bottom of the Excel window.
4. Click on cell F22 (the cell containing the total sales figure).
5. Press Enter.

That's it! Excel will immediately take you back to your "Annual_Summary" sheet, and you'll see the value from "Q4_Sales!F22" displayed. Your formula bar will show the reference:

=Q4_Sales!F22

The great thing about this method is that the link is live. If the value in cell F22 on the "Q4_Sales" sheet changes, it will automatically update on your "Annual_Summary" sheet.

Note for Sheet Names with Spaces: If your sheet name includes spaces (like "Q4 Sales" instead of "Q4_Sales"), Excel will automatically add single quotes around the name: 'Q4 Sales'!F22.

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 Named Ranges for Clarity and Stability

Referencing cells like =Q4_Sales!F22 works perfectly fine, but formulas can get complicated and hard to read, especially when you start referencing multiple ranges. A better approach is to use Named Ranges.

A Named Range allows you to assign a simple, human-readable name (like "TotalRevenue" or "ProductList") to a single cell or a range of cells. This makes your formulas much easier to understand and more resilient to changes in your spreadsheet's structure.

How to Create a Named Range

1. Navigate to the sheet with the data you want to name (e.g., your "Q4_Sales" sheet).
2. Select the cell or range of cells you want to name. For example, let's highlight cells B2:B50, which contain all the sales amounts.
3. Go to the Name Box, which is located just to the left of the formula bar.
4. Type a descriptive name for your range, like "Q4SalesData". Names cannot have spaces but can use underscores or capitalization.
5. Press Enter.

Now, instead of using Q4_Sales!B2:B50 in your formulas, you can simply use "Q4SalesData".

Back on your "Annual_Summary" sheet, if you wanted to calculate the sum of all sales in Q4, you could simply write:

=SUM(Q4SalesData)

This is much cleaner and more intuitive than =SUM(Q4_Sales!B2:B50). Furthermore, if you insert or delete rows within the B2:B50 range on your "Q4_Sales" sheet, the Named Range will automatically adjust, whereas a standard cell reference might break.

Method 3: Looking Up Specific Data with VLOOKUP

Often, you don't just want to link to a static cell, you need to find a specific piece of information from a large table on another sheet. This is where lookup functions come in, and the classic tool for this job is VLOOKUP (Vertical Lookup).

Imagine you have a sheet named "Products" with all your product information - Product ID, Name, and Price. On your "Invoice" sheet, you want to enter a Product ID in one cell and have Excel automatically pull the correct Product Name and Price from the "Products" sheet.

The VLOOKUP syntax looks like this:

=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).
• table_array: The range of data on the other sheet where your information lives. Crucially, the column containing your lookup_value must be the first column in this range.
• col_index_num: The number of the column in your table_array from which you want to return a value (e.g., column 2 for Product Name).
• range_lookup: Enter FALSE for an exact match or TRUE for an approximate match. You will almost always use FALSE.

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

VLOOKUP Example in Practice

The Setup:

• Your "Products" sheet has data in columns A, B, and C:
• On your "Invoice" sheet, cell A2 is where you will type a product ID. You want the name to appear in B2.

In cell B2 on your "Invoice" sheet, you would write this formula:

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

Let's break that down:

• A2: This is the lookup_value - we are telling Excel to look for whatever Product ID is typed into cell A2.
• Products!A:C: This is our table_array. We are telling Excel to search inside the range of columns A through C on the "Products" sheet.
• 2: This is our col_index_num. Once a match is found in column A, we want to return the value from the second column of our table range (which is column B, the Product Name).
• FALSE: We are telling Excel we need an exact match for the Product ID.

Now, when you type "WID-101" into A2, "Standard Widget" will instantly appear in B2. To get the price in C2, you'd use a similar formula, but change the column index number to 3: =VLOOKUP(A2, Products!A:C, 3, FALSE).

Method 4: The Modern and Superior Alternative: XLOOKUP

If you're using a modern version of Excel (Microsoft 365 or Excel 2021), you have access to XLOOKUP, the powerful successor to VLOOKUP. It's easier to use, more flexible, and fixes many of the old frustrations of VLOOKUP.

Why XLOOKUP is Better

• No Column Order Restriction: Your lookup value doesn't have to be in the first column!
• Simple Column Selection: You select the lookup column and the return column separately, rather than counting columns in a large table.
• Built-in "Not Found" Message: You can specify what to show if no match is found, avoiding ugly #N/A errors.
• Safer: Inserting or deleting columns won't break your formula, a common VLOOKUP issue.
• Defaults to Exact Match: It defaults to an exact match, which is safer and what you usually need.

The XLOOKUP syntax is more intuitive:

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

XLOOKUP Example in Practice

Using the same scenario as before ("Products" and "Invoice" sheets), here's how you'd get the product name using XLOOKUP:

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

Let's break this down:

• A2: The Product ID we're searching for.
• Products!A:A: The lookup_array. We're telling Excel to look only in column A on the "Products" sheet.
• Products!B:B: The return_array. We want to get the corresponding value from column B (the Product Name).
• "Product Not Found": This optional argument tells Excel to display this message if the ID in A2 doesn't exist in the product list.

This formula is easier to read, write, and much harder to break than its VLOOKUP counterpart. If possible, XLOOKUP should be your go-to function for pulling specific data from another sheet.

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 5: Combining Multiple Functions like INDEX and MATCH

Before XLOOKUP existed, the most powerful and flexible way to perform lookups was by combining two separate functions: INDEX and MATCH. This method still works in all versions of Excel and is wonderful for complex, two-way lookups.

How an INDEX/MATCH Combo Works:

• First, MATCH finds the position (the row number) of your lookup value within a column.
• Then, INDEX takes that row number and returns the value from the corresponding position in a different column.

Here's how you'd structure the formula for our product lookup example:

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

• MATCH(A2, Products!A:A, 0): This inner part runs first. It finds the "Product ID" from cell A2 inside column A of the "Products" sheet and returns its row number. The 0 specifies an exact match.
• INDEX(Products!B:B, ...): The outer INDEX function then takes that row number and returns the value from that same row in column B of the "Products" sheet (the Product Name).

While XLOOKUP has made INDEX/MATCH less necessary for everyday use, understanding how it works can help you tackle more advanced spreadsheet challenges.

Final Thoughts

Knowing how to connect data across different Excel sheets is a gateway to creating more organized, powerful, and automated workbooks. From a simple cell link to a flexible XLOOKUP formula, these techniques allow you to summarize information and find what you need without endless copy-pasting, transforming your spreadsheets from static data repositories into dynamic reporting tools.

Manually connecting sheets in Excel is one thing, but connecting data from completely different applications - like Google Analytics, Salesforce, and your ad platforms - is another challenge entirely. That's why we built Graphed to help. We simplify the entire process by letting you connect all your marketing and sales data with a few clicks. Instead of writing complex formulas, you can just ask questions in plain English, and Graphed instantly builds the live, cross-platform dashboards you need. This gives you back the time to focus on insights, not on manual data entry.