5 Simple Ways to Merge Two Excel Spreadsheets

The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after – consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.

Today we will tackle a problem that many Excel users are struggling with daily – how to merge multiple Excel sheets into one without copying and pasting. The tutorial covers two most common scenarios: consolidating numeric data (sum, count, etc.) and merging sheets (i.e. copying data from multiple worksheets into one).

The quickest way to consolidate data in Excel (located in one workbook or multiple workbooks) is by using the built-in Excel Consolidate feature.

Lets consider the following example. Supposing you have a number of reports from your company regional offices and you want to consolidate those figures into a master worksheet so that you have one summary report with sales totals of all the products.

As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns: The source worksheets to be consolidated into a summary sheet.

As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. However, it does have a few limitations. In particular, it works for numeric values only and it always summarizes those numbers in one way or another (sum, count, average, etc.)

If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go. To combine just a couple of sheets, you may not need anything else but the good old copy/paste. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, you may want to employ one of the following techniques to automate the merge.

Working with multiple Excel spreadsheets can get messy. To stay organized, you’ll eventually need to merge Excel files.

Combining data from different spreadsheets is a common task in Excel. But for beginners knowing how to merge Excel sheets can be confusing.

In this guide, you’ll learn 5 easy ways to combine two or more Excel spreadsheets:

Copy and Paste Sheets
Move and Copy Sheets
Consolidate Data
Use Power Query
VLOOKUP

These methods help you merge Excel workbooks efficiently. Let’s look at each technique for combining data into one master spreadsheet.

1. Copy and Paste Sheets

The most straightforward way to merge Excel files is by copying and pasting

Here are the steps:

Open both Excel workbooks you want to merge.
In the source file, select the sheets you want to copy by clicking the tabs while holding CTRL or Command. The tabs will appear highlighted when selected.
Right click on the highlighted tabs and choose “Copy” or “Move”.
Go to the destination workbook and right click on any tab. Choose “Insert Copied Sheets”.
The copied sheets will now be merged into the destination workbook.

This lets you quickly merge contents from multiple Excel workbooks. The drawback is data doesn’t stay linked. Changes in the source files won’t sync to the merged workbook.

2. Move and Copy Sheets

You can also move or copy entire sheets between workbooks using the “Move or Copy” command.

Follow these steps:

Open both Excel workbooks.
In the source file, CTRL + click to select the sheets you want to move or copy.
Right click on a highlighted tab and choose “Move or Copy”.
In the “Move or Copy” dialog box, choose the destination workbook from the “To book” drop down menu.
Check the “Create a copy” box to copy the sheets instead of moving them.
Click OK, and the sheets will be merged into the destination workbook.

This lets you merge selective sheets from multiple Excel files. Like copying and pasting, the data won’t stay dynamically linked after moving sheets between workbooks.

3. Consolidate Data

Excel’s built-in “Consolidate” tool lets you merge data from multiple sheets into one summary sheet.

Here’s how to use it:

Open the Excel workbook where you want to consolidate data.
Click on the “Data” tab and select “Consolidate”.
In the Consolidate dialog box, choose the function to summarize the data such as Sum, Count, Average, etc.
Click the dropdown arrow beside “Reference” and select the data range in the first source sheet. Click “Add”.
Repeat this to add data ranges from other Excel sheets you want to consolidate.
Click OK, and Excel will create a summary sheet with the consolidated data.

This is useful for combining similar data from multiple sheets into one master sheet for analysis. The summary will update if the source data changes.

4. Use Power Query

Excel’s Power Query tool lets you merge data from multiple sheets into a single sheet, with one click.

Follow these steps:

Ensure all source sheets have the same data structure with identical column headings.
Go to the Data tab and click “Get Data” > “From File” > “From Workbook”.
Select the current Excel workbook and click OK.
In the navigator window, select the sheets you want to combine.
With the desired sheets selected, click “Combine & Load” to merge the data.

Power Query automatically matches up column headings when merging multiple sheets. This is the fastest way to combine Excel data for analysis.

5. VLOOKUP

You can also use the VLOOKUP formula to merge data from separate sheets.

To do this:

Add a new column in the destination sheet to pull in data from another sheet.
Use VLOOKUP to match values from a shared column like an ID number or name.
Set the VLOOKUP range to the data you want to pull from the source sheet.
Fill down the formula to merge the full data range into the destination sheet.

This works well when you need to combine sheets based on a common value like customer ID, product name, etc. It keeps the merged data dynamically linked.

So those are 5 simple ways to merge data from multiple Excel sheets. By using the right technique, you can combine multiple spreadsheets into one master file for reporting and analysis.

Copying or moving sheets is quick for one-time consolidation. Tools like Consolidate and Power Query are better for recurring merges that need to stay updated. And VLOOKUP gives you precision when merging sheets with common values.

With these tips, you can stop hopping between Excel tabs and combine your spreadsheets efficiently.

When Should You Merge Excel Spreadsheets?

Merging Excel files helps consolidate data from multiple sources into one place. This makes analysis and reporting easier with all info in one spreadsheet.

But when exactly should you combine Excel workbooks? Here are three common scenarios:

1. Compile Team Member Data

In shared projects, team members often track data in separate workbooks. Combining these sheets gives a full data set for analysis.

For example, a sales team leader can merge weekly results from reps into one sheet to view total company performance. You avoid confusion from flipping between multiple tracker files.

2. Import Data From External Sources

Many tasks require pulling data from outside systems like databases and web platforms. You can import this external data into Excel for further manipulation and analysis.

For instance, combine website analytics data from multiple months into one workbook. This gives a consolidated view for identifying trends over time.

3. Aggregate Different Data Types

Often you need to bring together different types of related data. For example, merging sales transaction records with customer contact info.

These are scenarios where combining data from separate sheets is beneficial. The key is planning ahead when aggregating multiples sources of data for reporting.

Tips for Merging Excel Spreadsheets

When combining multiple Excel sheets, follow these best practices:

Check for consistent structure – Before merging, confirm all sheets have the same layout and columns.
Copy key metadata – Include worksheet name, timestamps, and other metadata along with the data.
Use unique IDs – Merge sheets accurately by matching rows using a unique ID column.
Select only required columns – No need to combine all data. Pick only the fields needed for reporting.
Link values don’t copy – Be aware that linked values like formulas won’t copy over when merging.
Watch for duplicates – Check for and remove any duplicate rows when consolidating data.
Check for errors – Review merged data for #REF errors indicating missing reference sheets.

Following these tips will help you combine Excel data cleanly without errors or inconsistencies.

Merge Excel Sheets Using VBA Macros

If you frequently need to combine Excel data, you can automate merging sheets using VBA macros.

Here are the key steps to write a macro for combining sheets in Excel:

Open the Visual Basic Editor in Excel (Alt+F11)
In the Project Explorer pane, double click on the workbook to insert a module.
Add code to loop through all sheets and copy data to a new summary sheet.
Use a condition to exclude the summary sheet from the merge.
Specify the target cell to copy data in the summary sheet.
Copy the data from each looped sheet to the target range.
Finally, activate the merged summary sheet.
Run the macro by pressing F5, and Excel will automatically combine all selected sheets.

This VBA method lets you merge Excel files with one click. The macro can be tweaked to only combine certain sheets.

How to Merge Sheets in Google Sheets

Google Sheets allows similar techniques to merge data from multiple sheets:

Import sheets – You can import entire sheets from separate Google Sheets files into one workbook using File > Import.
IMPORTRANGE – This powerful function lets you import data from specific sheets and ranges in other workbooks.
Query – Google Sheets Query function merges data by joining columns from selected sheets based on criteria.
AppScript – Write scripts to automate importing and merging sheets from multiple spreadsheets.

So Google Sheets provides comparable built-in tools and custom scripting options to combine data from multiple sources. The IMPORTRANGE function gives added flexibility for advanced merging tasks.

Common Questions about Merging Excel Sheets (FAQ)

Here are answers to some frequently asked questions about combining data from multiple Excel sheets:

How do I merge Excel sheets based on a column?

Use VLOOKUP, Index/Match, or Power Query to merge Excel sheets matching on a common column like ID, name, date, etc. This avoids duplicating rows when merging.

Other ways to combine sheets in Excel

Apart from the Copy Sheets wizard, the Ultimate Suite for Excel provides a few more merging tools to handle more specific scenarios.

When you are dealing with the sheets created by different users, the order of columns is often different. How do you handle this? Will you be copying the sheets manually or moving columns in each sheet? Neither! Commit the job to our Combine Sheets wizard: Combine Sheets for Excel.

And the data will be combined perfectly by column headers: The data from different sheets are combined by column headers.

If you have really large sheets with tons of different columns, you may want to merge only the most important ones to a summary table. Run the Combine Worksheets wizard and select the relevant columns. Yep, its that easy! Merge specific columns from multiple sheets.

As the result, only the data from the columns that you selected get into the summary sheet: Only selected columns from multiple sheets are merged.

These examples have demonstrated only a couple of our merge tools, but there is much more to it! After experimenting a bit, you will see how useful all the features are. The fully functional evaluation version of the Ultimate Suite is available for download at the end of this post.

Combine data from multiple worksheets with Power Query

Power Query is a very powerful technology to combine and refine data in Excel. At that, its rather complex and requires a long learning curve. The following tutorial explains the common uses in detail: Combine data from multiple data sources (Power Query).