How to Combine Data from Multiple Sheets in Excel (With Examples)

Combine data from multiple sheets is an important skill to learn for intermediate to advanced users. In fact, anyone who works with reports, sales data, monthly trackers, or multi-department inputs frequently has to combine data from several sheets in Excel. Knowing how to combine data from multiple sheets into one can save you hours of manual labor, whether you’re running a small business, leading a team, or simply keeping track of your personal finances.

This post will guide you through simple ways to merge data from several Excel sheets, ranging from simple methods for beginners to somewhat complex formulas. To help you fully understand the idea, we’ll also provide practice questions, screenshots, and examples.

📌 Why Combine Data from Multiple Sheets in Excel?

Assume that we have an Excel sheet with following tabs –

• Sheet1 contains January sales

• We have February sales in Sheet2

• Last sheet – Sheet3 contains March sales

Sheet looks like this –

You now need a separate summary sheet that displays the total sales for each month. Excel provides us with strong tools to automate this process rather than requiring us to copy and paste every time.

🔹 Method 1: Copy-Paste (Manual Method) to combine data from multiple sheets in Excel

If we want a quick one time type of solution, we can follow this method –

Steps:

1. Copy the data from Jan Sales tab.

2. Create a new tab named ‘Summary’ and paste it.

3. Similarly, copy the sales data from Feb Sales and Mar Sales and paste it next to Jan sales data in Summary tab.

Pros:

• It is an easy and quick solution for small datasets.

• Good for absolute beginners as no formulas needed here.

Cons:

• This solution is not dynamic. If the data in Jan, Feb, Mar tabs get updated, our summary will not change.

🔹 Method 2: Use the Consolidate Tool in Excel

We can use this method when the data has same layout in all the sheets we want to consolidate. Here, we have our data in same layout so let’s follow these steps –

Steps:

1. Create a new sheet called “Summary”.

2. Then, click on the top-left cell (A1).

3. Go to the Data tab → Consolidate.

4. In the pop-up, choose Function: Sum.

5. Under Reference, click the upward arrow and select the data from Jan Sales tab, then click Add. Don’t forget to add the first row too for headers.

6. Now, do the same for Feb Sales and March Sales.

7. Check the following boxes – “Top row” and “Left column” if you want labels.

8. Click OK.

You’ll see combined data like this:

Pros:

• No need for manual copy and paste.

Cons:

• This method too does not update dynamically.

• Can not be used if data layouts are inconsistent.

🔹 Method 3: Use the 3D Reference Formula to combine data from multiple sheets in Excel

Method 3 can be used even if the data is not in same layout and the result will automatically update when there is a change in original data.

Example:

In Summary tab, we can write this formula against product A – =’Jan Sales’!B2+’Feb Sales’!B2+’Mar Sales’!B2

This formula sums up B2 across all sheets. We can then drag it for B and C or copy paste.

Pros:

• Simple and dynamic. We do not even have to write the formula, we can click on the cells after pressing “=”.

Cons:

• Not ideal for large tables.

🔹 Method 4: Using Power Query (Best for Dynamic Combining)

The most reliable, scalable, and automated method to combine data from multiple sheets in Excel is Power Query. If you are new to power query, check out our article here – Power Query in Excel

Step-by-step Example:

Let’s say each sheet (Jan, Feb, Mar) contains a table with this data:

Steps:

1. First, format each data table as a Table (Ctrl+T).

2. Then, go to the Data tab → Click Get Data → From Workbook.

3. Now select your workbook.

4. In the Navigator pane, select all the tables/sheets you want to combine.

5. Click Transform Data.

6. In Power Query Editor:

   • Select all tables → Click Append Queries.

7. Click Close & Load to add combined data to a new sheet.

Pros:

• This method is fully automated.

• Power query can handle thousands of rows across sheets.

• Super easy to refresh when data updates.

Cons:

• Beginners may find it difficult.

• Requires enabling Power Query (Excel 2016+ or Excel 365).

🔹 Method 5: Combine Using VLOOKUP or XLOOKUP Across Sheets

This is also a great way of making dynamic summaries.

Example:

In the summary sheet, if you want to look up Price of Product A from Jan Sales tab, use:

Or with XLOOKUP (more powerful):

Copy and paste this into other cells to complete the table. If you are new to VLOOKUP, check out our detailed guide here – VLOOKUP for beginners

✅ Practice Questions on how to combine data from multiple sheets in Excel

Now that you have a great understanding of combining data from multiple sheets in Excel, let’s try the following to test our knowledge –

Q1:

An Excel sheet has 3 tabs – Sheet1, Sheet2, and Sheet3. There is a single column in all 3 called “Revenue”. Create a new summary tab and then combine all revenues into a new sheet using the Consolidate tool.

Q2:

We have 2 columns in every sheet – Product and Sales. Can you deploy Power Query to first load all sheets and then create a summary telling us the total sales per product?

Q3:

Write a 3D formula to sum cell D3 across Sheet1 to Sheet12 (monthly sheets).

Drop your queries in comments if you are stuck somewhere.

📝 Final Thoughts on how to combine data from multiple sheets in Excel

After you become familiar with the available tools and techniques, combining data from multiple sheets in Excel becomes second nature. 3D formulas or manual copy-paste may be sufficient for short tasks. However, Power Query is your best friend when it comes to scalable, automated, and dynamic solutions.

📥 Pro Tip: It is important to clean and organize the data first before starting to combine. It’ll make combining data much easier using formulas or automation tools.

If you found this article on “Combine data from multiple sheets in Excel” helpful, don’t forget to check out our other Excel tutorials on codeandexcel.com for more beginner to advanced content.

💬 Got questions or stuck somewhere? Drop your query in the comments or email us. We’re happy to help!

Check out our other Excel articles –

1. 25 Excel Keyboard shortcuts to make you faster
2. How to Use the Text Function in Excel for Formatting
3. Beginner’s Guide to Data Validation in Excel (with Examples & Practice Questions)
4. How to combine text in Excel