How to Merge Data From Multiple Sheets in 3 Clicks (Even If You're a Total Beginner)
Let me tell you a story. A few mons ago, I was staring at my computer screen, completely overwhelmed. I had twelve different Excel sheets open—sales data from each month of the year, all with the same columns but scattered across separate tabs. My boss wanted a yearly summary by 5 PM. My first instinct? Copy and paste. Row by row. Sheet by sheet.
Two hours later, I was still at it, my eyes glazed over, and I had just realized I'd accidentally pasted the July data twice and completely missed August. That's when I knew there had to be a better way. I discovered how to merge data from multiple sheets in just a few clicks, and honestly? This is what saved my job.
If you're a beginner who feels lost when looking at Excel, don't worry. This guide is for you. I'll walk you through everything like a friend explaining it over coffee—no complicated jargon, no assuming you already know things. Just clear, step-by-step instructions with simple explanations.
Why Manually Copying and Pasting is a Trap
Before I show you the magic, let me explain why copying and pasting is the worst way to merge sheets.
Think of it like this: imagine you have five different notebooks, each with a list of customers from a different city. To get a complete list, you'd have to open each notebook, write down every name, and then compile them into a new notebook. Sounds tedious, right? Now imagine you discover you made a mistake in one notebook and have to redo everything.
That's exactly what happens when you copy and paste data manually in Excel. It's:
Time-consuming: If you have more than three sheets, you'll waste hours.
Error-prone: It's way too easy to miss a row, paste something in the wrong place, or accidentally duplicate data.
Not dynamic: If your source data changes, your merged sheet doesn't update. You have to start all over again.
Trust me, I learned this the hard way. But there's a much smarter approach.
The 3 Methods to Merge Sheets in Excel
After lots of research and trial and error, I found three reliable ways to combine data from multiple sheets. I'll show you all of them, from easiest to most powerful.
Method 1: The Excel Consolidate Tool (Best for Quick Summaries)
If all your sheets have the exact same structure—same column headers, same order—the Consolidate tool is your fastest option. It lives right in the Excel ribbon and can combine your data in about three clicks.
Here's how it works:
Open your workbook with all the sheets you want to combine.
Create a new blank sheet. Click the
+icon at the bottom next to your sheets and name it "Consolidated" or something you'll remember.Click on cell A1 in your new sheet.
Go to the Data tab in the ribbon at the top.
A new window will pop up. This is where the magic happens.
If you want to learn more about Excel, click here.
Here's how to fill it out:
Function: Choose what you want to do with your data. Most people select Sum to add everything together. If you're working with text or need counts, you can choose Count or Average instead .
Reference: Click inside this box, then go to your first sheet and select the entire range of data you want to combine. Include the headers if you have them. Click Add to include it in the list.
Repeat this for all the sheets you want to combine. All your ranges will appear in the "All references" list.
If you included headers, make sure to check the Top row and/or Left column boxes under "Use labels in" .
Here's a pro tip: check the box that says Create links to source data. This makes your consolidated report dynamic—if a number changes in any source sheet, your summary will update automatically .
Click OK.
Boom! Just like that, your data from all sheets is combined into one . It took me less than a minute.
When to use this: You have simple, identical sheets and need a quick summary. This is perfect for monthly reports, budget summaries, or any situation where sheets have matching structures.
Method 2: Power Query (The Game-Changer)
If you have complex data, different sheet structures, or just want a solution that updates automatically forever, Power Query is your new best friend. This is the tool that literally saved my career.
Power Query is like a smart robot that learns your steps once and then repeats them perfectly every time you need an update . Seriously, once you set this up, merging new data is just one click.
If you want to learn more about Excel, click here.
.png)
Here's the step-by-step guide:
Open a new blank workbook in Excel.
Go to the Data tab, click Get Data > From File > From Excel Workbook .
Browse and select the Excel file that contains the sheets you want to merge, then click Import.
The Navigator window will open showing all the sheets in your file. Don't select a single sheet. Instead, check the "Select multiple items" box at the top, then select all the sheets you want to combine. Click Transform Data .
This opens the Power Query Editor. Now we're cooking.
In the Power Query Editor, you'll see your selected sheets listed as separate queries in the pane on the left .
On the Home tab, click the dropdown under Append Queries and select Append Queries as New . This creates a brand new combined query without touching your original data.
In the Append dialog, select "Three or more tables" (or "Two tables" if that applies). Move all your sheet queries from the "Available tables" list to the "Tables to append" list using the Add >> button. Click OK .
Power Query will now create a new query with all your data neatly stacked together. You can rename it by changing the name in the "Query Settings" pane on the right.
At this point, you can clean up your data: remove unnecessary columns, change data types, or filter out anything you don't need. Right-click column headers to remove them .
Once you're happy with the preview, click Close & Load on the Home tab.
Excel will create a new worksheet with your consolidated data as a formatted table .
The best part? Next month, when you get a new sales report, just add it as a new sheet in your source file, go to your consolidated table, right-click it, and select Refresh. Power Query automatically pulls in the new data in seconds .
Method 3: VLOOKUP and XLOOKUP (For Matching Data)
Sometimes you don't want to stack data. Sometimes you want to enrich one sheet with information from another—like adding product names to a sales list by matching product IDs.
This is where lookup functions shine. The old-school option is VLOOKUP, but if you have Excel 365, the modern XLOOKUP is infinitely better .
Think of this like a phone book. You have a name (the lookup value) and you want to find their phone number (the return value) in the phone book (the table array).
Here's a quick comparison:
VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example scenario: You have a "Sales" sheet with a ProductID column and a "Products" sheet with ProductID and ProductName columns. You want the product names to appear in your sales sheet.
Using XLOOKUP:
On your sales sheet, in the cell where you want the product name, type:
=XLOOKUP( [click the Product ID cell], Products!A:A, Products!B:B)
The first part is the Product ID you're looking for.
The second is the column in the Products sheet that contains the IDs.
The third is the column in the Products sheet that has the names you want to return .
Press Enter, and just like magic, the correct product name appears. Drag the formula down to fill the rest of your rows.
Why XLOOKUP beats VLOOKUP:
You don't have to count columns (no more "column index number" headaches) .
It can look left or right (VLOOKUP only looks to the right) .
It doesn't break if you insert or delete columns in your source data .
My Personal Story: How This Saved My Job
Remember those twelve monthly sales sheets I mentioned earlier? After discovering Power Query, I could merge all of them in under a minute. My boss was impressed when I delivered the annual summary two hours early. But the real test came later.
The following month, I needed to update the report with new data. Instead of spending hours copying and pasting again, I just added the new sheet to the folder, opened my Excel file, and clicked Refresh. Less than ten seconds later, everything was updated—all 12 months of data plus the new month, perfectly combined.
My boss thought I was some kind of Excel wizard. The truth? I just knew how to use the right tools. This is what saved my job.
Choosing the Right Method for Your Situation
Still not sure which method to use? Here's a simple flowchart to help you decide:
Do all your sheets have the exact same structure and you just need a quick summary? Use the Consolidate tool (Method 1).
Do you need to combine data that changes regularly, or do you have more than 5 sheets? Use Power Query (Method 2).
Do you need to pull in related information from another sheet using a common ID? Use XLOOKUP or VLOOKUP (Method 3).
Conclusion: Stop Wasting Time and Start Merging Like a Pro
Manual copying and pasting is a trap. It's slow, error-prone, and—frankly—a waste of your talents. You deserve a better way to work.
Now you know the three best methods to merge data from multiple sheets in Excel. Whether you use the Consolidate tool for quick summaries, Power Query for automated workflows, or XLOOKUP for data enrichment, you can stop wasting time and start getting results.
Personally, I recommend Power Query for most people. It's more powerful than you realize, and once you learn it, you'll never go back to manual merging. It's the tool that turned me from a frustrated Excel user into someone who actually enjoys working with data.
Remember: the goal isn't to work harder—it's to work smarter. These methods help you do exactly that.
If you want to learn more about Excel, click here.
Frequently Asked Questions About Merging Excel Sheets
Q: Can I merge sheets from different workbooks?
A: Yes! Both the Consolidate tool and Power Query can pull data from different workbooks. With Power Query, you can even merge all files in a folder with one click .
Q: Will my merged data update automatically when I change source data?
A: If you use Power Query or create links in the Consolidate tool, yes. Manual copy-paste does not update automatically .
Q: What if my sheets have different column orders?
A: Power Query can handle this easily. You can map columns correctly during the setup process. XLOOKUP also works regardless of column order .
Q: Is Power Query available in my version of Excel?
A: Power Query is available in Excel 2016 and newer versions, including Excel 365. In older versions, it might be called "Get & Transform Data" .
Q: How many sheets can I merge at once?
A: There's no practical limit. Power Query can handle hundreds of sheets without issues .
Q: What's the difference between merging and consolidating?
A: Merging typically means stacking data from multiple sheets into one master list. Consolidating usually means creating summaries (sums, averages, counts) from multiple data ranges .