Merge multiple Excel sheets into one

While working with Excel, utilizing the Sheet numbers to operate different tasks is quite common. However, many Excel users need help merging the data of multiple sheets into one without copying and pasting. The old gold method to combine the sheets in Excel is copying the data from various sheets and pasting it to the master worksheet. But this option seems easy only when you want to gather the data from a couple of sheets. But if you want to combine twenty Excel sheets into one master file, in such cases errors with manual copying/pasting are unavoidable and it becomes a tedious process.

To make merging smooth and easy, Microsoft Excel has provided some methods that can quickly help you to combine the data of different sheets into one. This step-by-step tutorial will benefit all Excel users merge the data of multiple sheets into one sheet promptly.

Microsoft Excel provides three different methods to merge your Excel worksheets into one master file without using the manual copying and pasting of data. Following are the different methods:

1. Merge Using Excel Consolidate Method
2. VBA Code
3. Power Query for Excel

Let's get started!

Merge data from multiple worksheets using Excel Consolidate Method

Consolidating your data means collecting data from different sources, organising it and merging it in a single location. Microsoft Excel provides a predefined method that will quickly help you to combine your data in Excel worksheets.

For example: Here, we are given a series of reports of a company where the sales figures for different months are stored in multiple sheets. To do an overall analysis, we need to consolidate the sales number into a master worksheet to have one summary report representing the total sales of all the products.

To consolidate your Excel data (similar data structure, but different number of rows and columns) in a single worksheet, follow the below-given steps:

Step 1: To arrange the data properly.

For the Excel Consolidate method to work accurately, do the following:

• You have created separate worksheets from different data values that you want to combine.
• Make sure that all the Excel sheet follows the same layout, where each column has a header and contains the similar data.
• The cells within the sheets should not contain any blank rows or columns.

Step 2: Run Excel Consolidate

Create a master worksheet where we will merge the data. In the master worksheet, do the following:

• From the Excel ribbon tab, click on the Data. In the Data Tools group, click on the upper-left cell where you want the consolidated data to appear.

Step 3: Validate the consolidate window settings.

Excel will open the Consolidatedialog window. Do the following:

1. From the Function box, we will select one of the summary functions (count, Sum, Product, Max, Min, etc.) from the given dropdown list, using which we will consolidate our data. In our case, we have selected the SUM function since we want the total of Sales.
2. In the following field, i.e.,Referencebox, we will pass the reference of all the cells one by one for different sheets. Click on theCollapse Dialogicon and select the cell range from the 'January worksheet. Once done, click on the Add button. It will quickly add the January range list in theAll referencesfield.
3. Repeat the above step for different sheets you want to consolidate your data.
4. If you want to merge sheets from different workbooks, you can click on theBrowsebottom to locate the workbook.
5. If you want to copy the headers and side labels from the selected cells, select the Top row and Left column checkbox.
6. If you want to create a link to source data click on the create links to source checkbox.
7. Once all the steps are done, click on OK button.

Step 4: Excel will consolidate the selected data

As a result, Excel will consolidate the selected data from different worksheets into one master sheet. As shown below, the merge data will create links to source worksheets.

Helpful and Easy! Following the above steps, you can quickly combine the data from multiple Excel sheets or worksheets into one master worksheet. But every method comes with some limitations, and the same is applied to this one as well.

Limitations of Consolidate method:

1. The Excel Consolidate method only works for thenumeric data valuesas it alwayssummarizesthose numeric data into the selected summary functions (count, Sum, Product, Max, Min, etc.).
2. The data is not properly formatted.

Merge Sheets using Excel VBA

If you are an expert with Excel VBA and are more comfortable using macros and coding. In such cases you can also opt for VBA coding.

Note: For the VBA code to work correctly, ensure all worksheets contain the same column headings and order.

Following are the steps to combine the data from multiple sheets into one master worksheets:

1. Make sure that all the Excel sheet follows the same layout, where each column has a header and contains the similar data. It should not contain any blank rows or columns.
   
2. Activate the Microsoft Visual Basic for Applicationswindow by pressing Alt + F11 shortcut key.
   Or
   You can also open the VBA window by click Developer Tab-> Visual Basic window
   
3. The Visual Basic window will appear. Click on theInsert > Module optionto insert a new Module script.
   
4. In the Module window, copy the and paste below given VBA code:

VBA CODE:

1. Once done, click on the Run tab (or you press the shortcut key F5), and it will select the data from all different sheets and combine them in a new sheet named as "Master").

Refer to the following screenshot.

Merge sheets using Power Query

Power Query is an excellent Excel add-in tool that can be beneficial in many ways. When combining Excel data from multiple sheets using Power Query, it is important to create the given data in an Excel Table. If your data is not present in a table format, this method won't work.

We have taken the pervious Sales report of different months and have converted them into Excel table.

Perform the following steps to merge multiple worksheets containing Tables into a single worksheet using Excel Power Query:

1. Make sure that all the Excel sheet follows the same layout, where each column has a header and contains the similar data. It should not contain any blank rows or columns.
2. From the Excel ribbon menu, click on the Data tab. Click on the 'Get Data' option from the Get & Transform Data group.
   
3. It will open the following list of option. Select the 'From Other Sources' option. From the next window, click on the 'Blank Query' option.
   
4. It will open Power Query editor. In the editor window, you have to type the given formula in the formula bar:=Excel.CurrentWorkbook().

Note: The Power Query formulas are case sensitive; therefore, you need to copy the same formula, or else it won't return the desired output, or it will return an error

1. Press the enter button. It will display all the table names present in the selected worksheet.
   Refer to the below screenshot.
   
2. In the Content header cell, click on the double pointed arrow. It will open the following window. Make sure to selected all the columns that you want to merge.
3. Since we don't want the prefix therefore uncheck the checkbox 'Use original column name as prefix' option. Click on the Ok button.
   
4. As a result, the above steps will combine the data from all the sheets of your workbook in one single table (see the following output).
   

Now, combining data in Excel won't be a stubborn task. You can choose any one of the above methods at your convenience. Go ahead and give it a try now!