How to Get Data from Different Sheets in Excel

Trying to create a summary report in Excel by pulling data from other sheets can feel like a chore. You end up constantly flipping between tabs, copying and pasting cells, and worrying that one wrong click will mess up your entire analysis. This article walks you through several methods to automatically get data from different sheets, eliminating the manual work and saving you a ton of time.

The Basics: Direct Cell Referencing

The simplest way to pull data from another sheet is by creating a direct link to a specific cell. This is perfect for when you need a value from a single cell on another sheet for a formula or a quick summary.

The syntax is straightforward: start with the sheet name, follow it with an exclamation mark, and then add the cell reference.

SheetName!CellReference

Let’s say you have two sheets: "Sales Q1" and "Summary". The total revenue for Q1 is in cell F50 on the "Sales Q1" sheet. To display this value on your "Summary" sheet, you would click into any cell and type:

='Sales Q1'!F50

How to Easily Create a Direct Reference

You don't need to manually type this formula. Excel can do it for you.

• In your destination sheet (e.g., "Summary"), click into the cell where you want the data to appear.
• Type the equals sign (=).
• Without typing anything else, click on the tab for the source sheet (e.g., "Sales Q1").
• Click on the specific cell you want to reference (e.g., F50).
• Press Enter.

Excel will automatically write the correct formula for you. This method also works within larger formulas. For example, if you wanted to add the Q1 total to a Q2 total (in cell F60 on a sheet named "Sales Q2"), your formula would look like this:

='Sales Q1'!F50 + 'Sales Q2'!F60

Note: If your sheet name includes spaces (like "Sales Q1"), Excel automatically adds single quotes around the name. If the name has no spaces, the quotes are not necessary.

Make Your Formulas Readable with Named Ranges

As formulas get more complex, references like 'Sales Q1'!F50 can become hard to read and manage. A much cleaner approach is to use Named Ranges. This lets you assign a memorable name (like Q1_Total_Revenue) to a cell or range of cells.

How to Create a Named Range

1. Navigate to the sheet and select the cell or range of cells you want to name. For our example, go to the "Sales Q1" sheet and click on cell F50.
2. Go to the Name Box, which is located in the top-left corner just above column A.
3. Type a descriptive name for your range (e.g., Q1_Total_Revenue) and press Enter. Note: Names cannot contain spaces.

That's it. Now, instead of typing ='Sales Q1'!F50, you can simply type:

=Q1_Total_Revenue

This formula works from any sheet in the workbook, making your summary reports dramatically easier to read and troubleshoot.

Look Up Specific Data with VLOOKUP

Often, you don't just need a static cell value - you need to find data related to a specific item. For example, you might want to pull the price of a product from a master "Products" sheet into your "Sales Log" sheet. This is where VLOOKUP (Vertical Lookup) comes in handy.

VLOOKUP searches for a value in the first column of a data table on another sheet and returns a corresponding value from a specified column in the same row.

Understanding the VLOOKUP Formula

The syntax for VLOOKUP has four elements:

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

• lookup_value: The value you are searching for (e.g., a Product ID).
• table_array: The range of cells that contains the data, located on your other sheet. Your lookup value must be in the first column of this range.
• col_index_num: The column number within your table_array from which you want to pull the data. The first column is 1, the second is 2, and so on.
• [range_lookup]: A value indicating whether you want an exact match (FALSE) or an approximate match (TRUE). For most business reporting, you will almost always use FALSE for an exact match.

VLOOKUP in Action: A Practical Example

Imagine you have a workbook with two sheets:

• Products: A list of your products. Column A has the "Product ID," and Column C has the "Price."
• Sales: Your sales log. Column B has the "Product ID" for each sale, and you want to fill in the "Price" in Column D.

In the "Sales" sheet, you would click on cell D2 and enter this formula:

=VLOOKUP(B2, Products!A:C, 3, FALSE)

Here's what it means:

• B2: Look up the Product ID found in cell B2 of the current sheet.
• Products!A:C: Search for that Product ID in the range of columns A through C on the Products sheet.
• 3: Once you find a match, give me the value from the 3rd column of that range (which is column C, the Price).
• FALSE: Only give me an exact match for the Product ID.

You can then drag this formula down Column D to automatically fetch the price for every sale.

Upgrade Your Lookups with XLOOKUP

While VLOOKUP is a workhorse, it has limitations. It can only look up values from left to right, and adding or removing columns in your source data can break the formula. For modern versions of Excel (Excel 2021, Microsoft 365), XLOOKUP is a far more powerful and flexible alternative.

XLOOKUP overcomes all of VLOOKUP's weaknesses. It can look up in any direction, it's more resilient to changes, and its syntax is much more intuitive.

Understanding the XLOOKUP Formula

The basic XLOOKUP syntax is simpler:

=XLOOKUP(lookup_value, lookup_array, return_array)

• lookup_value: The value you are searching for (same as VLOOKUP).
• lookup_array: The specific column or row where you want to search for your lookup value.
• return_array: The specific column or row from which you want to return a value.

XLOOKUP in Action: The Modern Approach

Using the same scenario as before (finding product prices), here’s how you’d use XLOOKUP. Remember, in our "Products" sheet, "Product ID" is in Column A and "Price" is in Column C. In our "Sales" sheet, we're looking up the ID from cell B2.

In the "Sales" sheet, you would click on cell D2 and enter:

=XLOOKUP(B2, Products!A:A, Products!C:C)

Breaking it down:

• B2: Look for the Product ID in this cell.
• Products!A:A: Search for it in Column A of the Products sheet.
• Products!C:C: When you find a match, return the corresponding value from Column C of the Products sheet.

This is easier to write and read. Because you specify the exact lookup and return columns, you can insert or delete other columns on the "Products" sheet without breaking this formula.

Consolidate Entire Sheets with Power Query

What if your goal is grander? Suppose you have separate sheets for sales from January, February, and March, and you want to combine them all into a single master sales report. Doing this with formulas can be cumbersome. This is a job for Power Query (also known as "Get & Transform Data" in newer Excel versions).

Power Query is a data transformation engine that lets you extract, transform, and combine data from multiple sources, including different sheets in the same workbook.

A Quick Guide to Consolidating Sheets with Power Query

Let's pretend you need to combine sheets named Jan_Sales, Feb_Sales, and Mar_Sales.

1. On the ribbon, go to the Data tab.
2. In the "Get & Transform Data" group, click Get Data » From File » From Excel Workbook.
3. Select the current file you are in and click Import.
4. The Navigator window appears. On the left, click Select multiple items.
5. Check the boxes for each sheet you want to combine (e.g., Jan_Sales, Feb_Sales, Mar_Sales).
6. At the bottom, click the dropdown next to "Load" and select Transform Data.
7. This opens the Power Query Editor. Go to the Home tab and click Append Queries » Append Queries as New.
8. In the Append window, select the sheets you want to stack on top of each other and click OK. Power Query will now generate a new table that combines all the rows from your selected sheets.
9. Once you're happy with the combined data, click Close & Load in the top-left corner.

Excel will create a new sheet containing a master table with all your data consolidated. The best part? This process is repeatable. When you add new data to your monthly sheets, you can simply go to your new master table, right-click, and select "Refresh" to update it with the latest information.

Final Thoughts

Knowing how to connect data across different Excel sheets is a fundamental skill that transforms your workflow. Whether you're using simple cell links, organizing with Named Ranges, fetching data with XLOOKUP, or consolidating entire datasets with Power Query, these methods allow you to build dynamic, automated reports and dashboards.

While these tools are huge time-savers compared to manual work, building and maintaining multi-sheet Excel reports can still feel like juggling chainsaws. That’s why we built Graphed. Instead of wrestling with spreadsheet formulas, you connect your data sources - like Shopify, Google Analytics, or even Google Sheets - once, then just ask questions in plain English. Graphed automatically builds the live dashboards and reports for you, getting a complete view of your business in seconds, not hours.