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:
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:
Step 2: Run Excel Consolidate
Create a master worksheet where we will merge the data. In the master worksheet, do the following:
Step 3: Validate the consolidate window settings.
Excel will open the Consolidatedialog window. Do the following:
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:
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:
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:
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
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!