How to merge sheets in excel?

Many times, data is split into several sheets. These sheets can be a part of a single Excel workbook or they can be stored in different workbooks. You get a requirement to take these sheets of data into a single worksheet.

You can take the data from different sheets to one by merging them together. Another way is to copy and paste the data from the sheets into one. But the copy-paste can take a lot of time if the data is too much. Also, it may remove all the formatting and operations performed on the sheet data. Hence, we will suggest you to use the method to merge sheets.

Merge sheets are the shortest and smarter way than the copy-paste method to take the different sheets of data together. This chapter will elaborate the Excel methods to merge the sheets in Excel.

Merge Sheets in Excel

Merging the sheets to take their data together (in a single sheet) is a smarter way in Excel. Generally, users use the copy/cut and paste method to combine the sheets data. If the data is too vast, it is not a good choice. In that case, you can merge the sheets to combine the data.

Excel enables various methods to merge the sheets/books data, that is –

• Move or copy sheets
• Merge data using consolidation
• Combine Excel data using VBA
• Use Get & Transform Data tools to merge data

Move or copy sheets

This one is the simplest method to move the sheet from different workbooks into a single workbook. You can move the sheet into a new workbook or existing one. It is a method to move the sheet from one workbook to another.

The users can easily learn how to move or copy the sheets from different workbooks into one new workbook in Excel by following the below steps. They can merge the sheets by creating a new Excel workbook or in an Existing workbook. For this, they can follow the below steps:

1. Open all the sheets whose data you want to merge. Either they are in a single Excel file or inside the different files. We have opened two workbooks for this example.

2. Navigate to the targeted sheet in the workbook you want to merge. In the Home tab, click the Format dropdown button residing inside the Cells Then, choose the Move or Copy Sheet option from the list.

3. A small window panel will open in which either select a new book or select an existing opened workbook from the dropdown list. We have selected a new workbook.

4. A new book is selected to merge the sheet from different books. Click OK to close the panel.

Tip: Instead of moving the sheet, you can also create a copy of a sheet by marking the Create a copy checkbox in the above panel.

5. You will see that the sheet has been moved into the new book named Book1.

The sheet has been removed from the book from which you have moved to the new book.

Note: Do not create the newly created workbook yet.

6. Now, navigate to another workbook that we have opened earlier. We will follow the same steps to move the sheet to the newly created workbook.

7. Select the book1 from the dropdown list that we have created in previous steps and also moved a sheet in it.

8. After selecting the workbook, click OK to close this panel and successfully move the selected sheet to the new book.

9. See that the sheet has been moved to book1, which is currently named Sheet1(2). It is because – both the sheets were named Sheet1.

Repeat the steps as many times you want to move and keep the sheets to a newly created book.

Merge data using consolidation

The above method does not work when your data is separated/stored into multiple worksheets and you want to take it into a single sheet. The above method takes the sheets of different books together into a new or existing book.

To make this method work perfectly, the following two conditions should be met while merging the data.

1. All the sheets that you are consolidating should be in the same layout as well as header and data type should also be identical.
2. There should not be any blank row or column. It means the number of columns and number of rows must be the same and their label should also be the same.

So, before consolidating the data, arrange the data in the order we have described. Once you arrange the data according to the specification, create a new worksheet that will contain the entire merged data.

Steps to merge data

Now, execute the following steps with your worksheet to consolidate the data:

We have created a new workbook that meets with the above conditions before processing the below steps.

Open all the books whose data you want to consolidate and the newly created book as well.

Step 1: In the newly created workbook, direct to the Data tab in Excel menu bar and click the Consolidate option inside the Data Tools group.

Step 2: Now, select the Sum (or any other operation) from the function dropdown list. Then, click the Reference field button to access the worksheet to select the data.

Add the cell references of different sheets one by one to merge into one.

Step 3: To do this, go to the sheet and select data like in the below screenshot.

Step 4: The reference of the selected cells will be automatically inserted into the Reference field.

Now, click the Add button to add the currently inserted reference to the All references list.

Step 5: See that the inserted reference is added to the All References list. Click inside the Reference field to add more data.

Step 6: Select the data from another sheet, like below.

Step 7: Another cell reference is inserted inside the Reference field. Click the Add button to add the inserted reference with the existing one (the above selected data).

Repeat the steps and do the same with other datasets to add more datasets to merge with the previously selected data.

Step 8: Another reference is added to the All references list.

Step 9: You can also mark the labels (Top row or Left column checkbox) to use the same row and column headings in the new sheet.

Step 10: Mark the Create links to source data. This will allow the real-time update in data. It means whenever you will update in the other sheet, it will reflect in the merged datasheet.

At last, click the OK button.

Step 11: You will see that all the selected data through the consolidate panel has been merged together within a single sheet.

Here, total sum of both sheets for each cell is placed in the merged sheet cells.

In this way, users can combine the data of multiple sheets with the specific operation too. Consolidation on data can be performed in two ways: One using by category and another by using position.

When not to use consolidate method to merge data

With some pros, there are some cons as well, which is –

This method is not suitable in that way it merges the two or more sheets data when you want to merge the cells along with the text. This method best works with numerical data instead of text.

Consolidating sheets is not the right choice to merge text data from different sheets. If you want to merge the text data, you can go for the VBA method to merge the text data.

Combine Excel data with VBA

Merging the text data from multiple sheets using the VBA method is a good choice. Using the VBA code, you can consolidate the text data. The Excel users have to write a macro for it and then use that macro to merge the data. Creating a macro is good when you need to merge the different sheets of data regularly.

Before merging the multiple sheets data together, check that all sheets/ workbooks exist in the same folder in your system. After that, you can bring all the data together you want to merge.

Following are the steps to merge the Excel text data using the VBA code.

Step 1: In the Developer tab, click the Visual Basics. A VBA code editor will open where the user can write the VBA code.

Step 2: Click the Insert tab in the menu bar and select Module. A new module will be created.

Step 3: Now, you can write the VBA code to merge the data.

VBA Code Script:

In this VBA code script, replace the Path with your file path wherever your file is stored in your system.

Step 4: Save the file with .XLSM extension so that the macros will be enabled.

At last, run the created macro and find that you have a single workbook containing all the sheets from all files in a folder.

When to use the VBA method

If you are a powerful user of Excel and feel comfortable with coding and using macros. Then, you can go for this method. Otherwise, this method is tricky and would not be easy to use and understand how it works. You must be very careful with syntax in the code so that it does not be the reason of any error.

Take the caution before merging sheets

The Excel user should take caution while merging the multiple sheets data. Merging sheets is a messy and complex operation that may lead to confusion. However, it is a very useful operation of Excel that an Excel user may need while working with vast data.

When you start working with merging the Excel data, it is good to consider all the possibilities before start and how long it will go ahead.

Get & Transform Data Tools to merge sheets

Excel offers a very powerful feature to query on data, i.e., PowerQuery. It has a set of tools that are extremely useful when you need to query your excel data for some special purpose.

In earlier versions of Excel, this feature was recognized as PowerQuery, but it was later renamed as Get & Transform Data in Excel 2016.

As the name of the feature (Get & Transform Data), it has some tools that help the users to pull the data from different workbooks and take them together into one workbook. This feature is available inside the Data tab of Excel at the very first place.

Remember that – this feature is only available for window users as it is exclusive for them. Hence, the user will not find it in Mac version of Excel or web Excel.

How to use this method?

We will take an example to let you know how this method takes place to combine the data of different sheets/workbooks. You have to complete some steps, these are –

• Before starting, check the data
• Combine sheets

Before starting, check the data

Before you start combining the data from different sheets, check the data in sheets (that you are going to merge) having the same data structure and the same columns. It is the first and most important process of merging the Excel data.

It is not easy to combine the two sheets having a four-column spreadsheet and a five-column spreadsheet, respectively. Excel would not let know where to place the data. Whenever you download the data from any website, it can be in different data structures.

So, the users must check the data before should be in same structure.

Merge the Excel files

Suppose we have a folder containing monthly report data files. We will combine these files to consolidate their data together. Merging the data using data consolidation method is not feasible when you have dozens or hundreds of files. Opening the file one by one and then consolidates its data will take too much time.

Hence, this method will save time when you need to merge multiple files data. Remember one thing that the data should be in the same format.

Place all the files in a folder to get all the file together.

Step 1: Open a new worksheet and navigate to the Data tab, where you will see the New Query option.

Step 2: Click the New Query and select From File from the list and then choose From Folder, i.e.,

New Query > From File > From Folder

Step 3: A popup will open to search the folder containing the files you want to merge. So, navigate to the targeted folder and click Open.

It will take some time to load the files of the selected folder. Wait till then.

Step 5: A new window popup will show where confirm the list of files to be merged together.

Along with the list of files, you will see the metadata (information) about the files in a table list view.

Step 6: Click the Combine button and choose Combine & Load.

A query for combining file will evaluate and a new panel will open.

Step 7: Now, select Sheet1 under the parameter1 list and click OK.

Step 8: The query will process and all the files inside the selected folder will be merged inside a single file. See that total 108 rows has been loaded from the selected folder.

Step 9: The selected file’s data has been merged and you will also see the Source.Name column contains the file names.

These files have been combined together successfully, as all files follow the same data structure. You can now save the merged data file and perform operations combinedly on the data.