How to Pull Data from Multiple Tabs in Excel

Manually copying and pasting data from a dozen different Excel tabs into one master sheet is a classic recipe for wasted time and costly errors. If you're managing monthly reports, regional sales data, or separate project trackers, you've likely felt this pain. This guide will walk you through a few practical methods to pull data from multiple tabs in Excel, ranging from a quick formula for simple summaries to a robust solution for combining entire tables.

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

Why Combine Data from Multiple Excel Tabs?

Before jumping into the "how," let's quickly cover the "why." Consolidating your data isn't just about saving a few clicks, it's about creating a single source of truth that unlocks better analysis. When your data lives in one central place, you can:

• Create a Master View: Get a bird's-eye view of your performance without having to click through endless tabs.
• Simplify Reporting: Build charts, pivot tables, and dashboards from one clean, consolidated table instead of trying to patch together visuals from ten different sources.
• Ensure Consistency: A unified dataset makes it easier to perform calculations like SUMs or AVERAGES, knowing you're not accidentally missing a tab or double-counting information.
• Avoid Manual Errors: The more you copy and paste, the higher the chance of grabbing the wrong range, pasting over correct data, or missing information entirely. Automation eliminates this risk.

Method 1: Quick Summaries with 3D Reference Formulas

If your goal isn't to combine lists of data but to summarize a single value across multiple sheets, a 3D reference is your fastest option. This type of formula is perfect for calculating a total sum, average, count, max, or min from the same cell or range on different tabs.

The key requirement for this method is that all your worksheets must have an identical structure. For example, if you want to sum the value in cell C5 from every sheet, the data in C5 needs to represent the same thing on each tab (e.g., "Total Revenue").

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 Quarterly Sales

Imagine you have an Excel workbook with sales data broken out into four tabs: Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales. Each sheet is laid out identically, with "Total Revenue" in cell F10. You want to calculate the total annual revenue on a new "Summary" tab.

1. Create a new worksheet and name it Summary.
2. In any cell on the Summary sheet (e.g., A2), type =SUM( but don't press Enter.
3. Click on the first worksheet tab in the series, Q1_Sales.
4. Hold down the Shift key on your keyboard.
5. While still holding Shift, click on the last tab in the series, Q4_Sales. You'll see Excel group the tabs together in the formula bar.
6. Now, click on the cell you want to sum - in our case, cell F10.
7. Close the parenthesis ) and press Enter.

Your final formula will look like this:

=SUM('Q1_Sales:Q4_Sales'!F10)

Excel will now add the values from cell F10 across all sheets from Q1_Sales to Q4_Sales. The best part? If you add a new tab (like "Q5_Sales") anywhere between the "Q1_Sales" and "Q4_Sales" tabs, the formula will automatically include it in the calculation!

Use this method for: A quick aggregation of single values when sheets are structured identically. Think totals, averages, and high-level summaries.

Method 2: Pulling Specific Data with VLOOKUP or XLOOKUP

What if you don't just want a total, but instead want to pull specific, related information from various tabs into a master list? This is where lookup formulas shine. You use a common piece of information (like a Product ID, Email Address, or Employee Name) to find its corresponding data on another sheet and bring it back.

Example: Consolidating Product Information

Let's say you have three tabs:

• Master_Products: Contains a list of Product SKUs and Names.
• Inventory: Contains the Product SKU and the current Units in Stock.
• Pricing: Contains the Product SKU and the list Price.

Your goal is to pull the "Units in Stock" and "Price" into the Master_Products sheet.

Step-by-Step with XLOOKUP (the modern, better option):

We'll use XLOOKUP, which is available in newer versions of Excel (Excel 2021, Microsoft 365). It's more flexible and intuitive than the older VLOOKUP.

1. On your Master_Products sheet, create new columns for "Stock" and "Price".
2. In the first cell under "Stock" (let's say it's C2), you'll look up the SKU from cell A2.
3. Type the following formula:

=XLOOKUP(A2,
    Inventory!A:A, 
    Inventory!B:B)

This formula tells Excel: "Take the SKU in cell A2, find it in column A of the Inventory sheet, and return the corresponding value from column B."

1. To get the price, do the same thing in the "Price" column (D2), pointing to the Pricing sheet instead:

=XLOOKUP(A2,
    Pricing!A:A, 
    Pricing!B:B)

1. Drag both formulas down, and Excel will populate the stock and price for every product in your master list.

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

What about VLOOKUP?

If you're on an older version of Excel, you'd use VLOOKUP. The formula for pulling inventory would be:

=VLOOKUP(A2, Inventory!A:B, 2, FALSE)

The main difference is that VLOOKUP requires you to specify the entire table array (Inventory!A:B) and then count the columns to tell it which one to return data from (2 for the second column). It lacks the flexibility of XLOOKUP but achieves the same result in this scenario.

Use this method for: Enriching a master list by pulling in related data points from other tabs based on a unique identifier.

Method 3: The Ultimate Solution for Combining Tables with Power Query

If you need to combine entire tables of data - for instance, taking monthly transaction lists from separate tabs and stacking them into one year-long mega-table - then Power Query is the answer. It is, by far, the most powerful, reliable, and scalable way to combine data in Excel. Power Query (also known as Get & Transform) is a tool built into modern versions of Excel.

This method automates the entire process. Once you set it up, you can add new rows to any of your source tabs, click "Refresh," and your master table will update instantly.

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: Combining Regional Sales Data

Imagine tabs for North_Data, South_Data, East_Data, and West_Data. Each tab contains a list of sales with a consistent column structure (e.g., Date, Product, Amount).

Here’s how to combine them with Power Query:

1. Format Source Data as Excel Tables This is a critical first step. For each of your tabs (North, South, etc.), click anywhere inside your data and press Ctrl + T. This turns your data range into a proper Excel Table. Give each table a descriptive name (e.g., "NorthData", "SouthData") from the "Table Design" tab that appears.
2. Create a Connection to Each Table We're going to create a link to each table without loading it back into a sheet just yet.

• Select a cell in your first table (e.g., NorthData).
• Go to the Data tab on the Ribbon and click From Table/Range. This will open the Power Query Editor.
• You don't need to change anything here yet. Click the dropdown arrow on Close & Load and select Close & Load To...
• In the pop-up window, select Only Create Connection and click OK.

You’ll see a "Queries & Connections" pane appear, showing a connection to your first table. Repeat this process for every other regional sales table (SouthData, EastData, WestData).

1. Append the Queries into One Master Table Now for the fun part. With all your connections ready, you’ll combine them.

• Go to the Data tab, click Get Data > Combine Queries > Append.
• An "Append" window will open. Select Three or more tables.
• You will see your table connections listed on the left. Select each one and click Add >> to move them into the "Tables to append" list on the right.
• Click OK.

1. Load Your Combined Table The Power Query editor opens again, this time showing a preview of all your data stacked on top of each other. You can even see a "Source.Name" column that tells you which table each row came from, which is incredibly useful! Now, just click the main Close & Load button. Power Query will load the combined data into a brand new worksheet. This table is your dynamic, consolidated master list.

From this point on, you never have to repeat these steps. Any time you add new rows to the source tabs, just right-click your final master table and hit Refresh to pull in all the changes.

Final Thoughts

Choosing the right method for pulling data depends entirely on your goal. For quick, high-level summaries across identically structured sheets, a 3D formula is efficient. For fetching specific, related values to build out a dataset, VLOOKUP or XLOOKUP is perfect. But for the serious task of combining entire tables into a dynamic, refreshable master list, Power Query is the undisputed champion.

Automating data consolidation in Excel is a huge time-saver, but reporting can still become a bottleneck when your information is scattered across completely different SaaS applications and advertising platforms. We built Graphed because we believe getting insights shouldn't require manual work. By integrating directly with your tools like Google Analytics, Shopify, and Salesforce, we centralize your data for you, allowing you to build real-time dashboards and reports simply by asking questions in plain English, a task that takes just a few seconds.