How to Pull Data from Multiple Worksheets in Excel

Wrangling data from different tabs in a single Excel workbook can quickly turn into a time-consuming headache. Instead of manually copying and pasting rows, you can use Excel’s built-in tools to pull data from multiple worksheets automatically. This article will walk you through a few different methods, from simple formulas perfect for quick summaries to a powerful tool designed to merge large datasets.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: The Quick Consolidation with 3D References

If all you need is a quick sum, average, or count from the same cell across multiple sheets, a 3D reference is your fastest option. This technique works by creating a reference that spans several worksheets, as long as they all share the same layout.

Imagine you have a separate worksheet for each month’s sales data (e.g., "Jan," "Feb," "Mar"). Each sheet has an identical structure, with the monthly sales total in cell E10. On a "Q1 Summary" sheet, you can instantly add these totals together.

How to Use a 3D Reference:

1. On your summary sheet, click the cell where you want the combined total to appear.
2. Start typing your formula, such as =SUM(.
3. Click on the tab of the first worksheet you want to include (e.g., "Jan").
4. Hold down the Shift key on your keyboard.
5. While holding Shift, click on the tab of the last worksheet in the range you want to include (e.g., "Mar"). You'll notice Excel groups the sheets in the formula bar.
6. Now, click the cell you want to aggregate, for instance, cell E10. The formula bar will now look something like this: =SUM('Jan:Mar'!E10).
7. Press Enter. Excel will instantly add the value from cell E10 on the Jan, Feb, and Mar sheets, plus any sheets that fall between them.

Best for: Quick aggregations like SUM, AVERAGE, COUNT, MAX, and MIN when all source sheets are identical in structure.

Keep in mind: This method is fantastic for its simplicity but lacks flexibility. It only works if the cell you’re referencing is in the exact same spot on every single sheet. If your data structures vary, you'll need a more dynamic approach.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Pulling Specific Data with VLOOKUP or XLOOKUP

What if you need to pull specific information related to an identifier, like looking up an employee’s salary or a product's price from a different sheet? This is a perfect job for a lookup function. While VLOOKUP is the classic choice, the newer XLOOKUP (available in Excel for Microsoft 365 and Excel 2021) is more powerful and flexible.

Let’s say you have two sheets: "Sales_Q1" and "Sales_Q2." You have a third "Analysis" sheet where you want to pull the sales amount for a specific Order ID that could be in either the Q1 or Q2 sheet.

How to Chain VLOOKUP Across Multiple Sheets:

The trick here is to nest VLOOKUP functions inside an IFERROR function. IFERROR tells Excel what to do if the formula results in an error (like when it can't find the Order ID on the first sheet). When that happens, you just tell it to try another VLOOKUP on the next sheet.

1. On your "Analysis" sheet, have your Order ID's in column A. In column B, you'll enter the formula.
2. Start by writing a VLOOKUP for the first sheet. Let's say you're looking for the value in A2 on the "Sales_Q1" sheet, which has Order IDs in column A and sales Amounts in column D (the 4th column): VLOOKUP(A2, Sales_Q1!A:D, 4, FALSE).
3. Now, wrap this formula inside IFERROR. If the first lookup fails, the IFERROR will run a second lookup on "Sales_Q2":

=IFERROR(VLOOKUP(A2, Sales_Q1!A:D, 4, FALSE), VLOOKUP(A2, Sales_Q2!A:D, 4, FALSE))

You can keep nesting these for additional sheets, but it can get quite long and difficult to manage.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Better Way: Chaining with XLOOKUP

XLOOKUP simplifies this by having a built-in [if_not_found] argument, making the formula cleaner.

=XLOOKUP(A2, Sales_Q1!A:A, Sales_Q1!D:D, XLOOKUP(A2, Sales_Q2!A:A, Sales_Q2!D:D, "Not Found"))

Here, the formula searches for the Order ID in column A of the Q1 sheet and returns the value from column D. If it doesn't find it there, it moves on to perform the second XLOOKUP for the Q2 sheet.

Best for: Extracting specific data points from different sheets based on a common lookup value (like an ID, email, or a product name).

Keep in mind: Chaining lookups can slow down your spreadsheet if you have many sheets or a huge amount of data. For merging entire datasets, this isn't the most efficient tool.

Method 3: The Ultimate Solution for Merging – Power Query

If your goal is to truly consolidate all rows from multiple sheets into one big master table, Power Query is without a doubt the best tool for the job. It’s built into modern versions of Excel (Excel 2016 and later, found under the "Data" tab) and called "Get & Transform Data." Think of it as an automation engine for all your data combining and cleaning tasks.

Let's stick with our sales data example. Imagine you have a separate worksheet for each sales rep (e.g., "Sarah," "Ben," "Chloe"), and you want to combine them all into a single sales report that you can easily refresh.

How to Combine Sheets with Power Query:

1. First, make sure your data on each sheet is formatted as an Excel Table. You can do this by clicking anywhere inside your data range and pressing Ctrl + T. Give each table a logical name (e.g., "SarahsSales", "BensSales"). This makes everything much easier to manage.
2. Go to the Data tab on the Ribbon.
3. Click Get Data → From Other Sources → Blank Query. This will open the Power Query Editor.
4. In the Power Query Editor formula bar, type the following formula and press Enter. This formula accesses the current Excel workbook's contents:

= Excel.CurrentWorkbook()

1. A table will appear showing you all the Tables and named ranges in your workbook. You can see the tables you just created ("SarahsSales," "BensSales," etc.).
2. Filter this list to only include the tables you want to combine. Click the filter arrow on the "Name" column and check the boxes for the sales tables.
3. Look for the column named "Content," which has the word Table in each cell. Next to the column header, you will see an icon with two arrows pointing in opposite directions (the expand icon). Click this icon.
4. A dialog box will appear. Make sure all columns are checked and uncheck "Use original column name as prefix." Click OK.
5. Just like that, Power Query appends all the data from your selected tables into one master list. You can perform additional data cleaning here, like changing data types or removing extra columns.
6. Once you're satisfied, click Close & Load in the top-left corner. Excel will load the combined data into a brand new worksheet in your workbook.

The best part? When you add new sales data to any of the original worksheets, simply right-click the Power Query results table and select Refresh. Your master list will update automatically.

Best for: Merging and consolidating large, structured datasets from multiple sheets or even separate workbooks. It's the most scalable, repeatable, and robust solution available in Excel.

Keep in mind: Power Query has a slight learning curve compared to simple formulas, but the time it saves on repetitive data consolidation tasks is more than worth the initial effort.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Quick Tips for Success

Whichever method you choose, following these best practices will make your life easier and your data more reliable.

• Keep Your Structures Consistent: For any consolidation task to work smoothly, your column headers should be identical across all sheets. "Date" on one sheet and "Sale Date" on another will cause problems.
• Use Tables: Formatting your sheet data as official Excel Tables (Ctrl + T) is a game-changer. They auto-expand as you add data and work beautifully with Power Query.
• Name Your Sheets Logically: Instead of "Sheet1", "Sheet2", and "Sheet3", use descriptive names like "Sales_Jan", "Sales_Feb", etc. This makes building formulas and managing Power Query steps far more intuitive.

Final Thoughts

From simple 3D SUM formulas for a quick overview to the robust appending power of Power Query, Excel provides several ways to pull data together from multiple worksheets. Choosing the right method depends entirely on whether you're performing a simple calculation or consolidating entire datasets for in-depth analysis.

Manually wrangling data from different tabs and services is one of the biggest time-sinks we see. The weekly ritual of downloading CSVs, cleaning them up, and combining them into a usable report is exactly the kind of friction we built Graphed to eliminate. We enable users to connect all their data sources - from spreadsheets and databases to platforms like Google Analytics and Shopify - and then use simple, plain-English commands to build the dashboards and reports they need, all with real-time, automatically updating data. If you’re tired of the copy-and-paste routine, give Graphed a try and let us automate the busywork for you.