I wanted to combine the contents of multiple worksheets in an Excel file.
The method in this tutorial will concatenate some or all of the worksheets in a single file into a new worksheet.
If you have data spread across multiple Excel files, you can simply repeat the process for each file.
Table of Contents
Format Of The Worksheets You Want To Combine
The data in each worksheet should be in the same format and number of columns.
Each worksheet should have the same header row.
Steps To Concatenate Several Excel Worksheets Into One
- Create a blank Excel workbook (or a new worksheet within the same file as the data).
- Switch to the Data tab in the top ribbon.
- Click on “Get Data” on the far left, choose “From File”, and select “From Workbook”.
If you don’t see the “Get Data” option, then your version of Excel is older than 2016. You won’t be able to follow these steps. If you’re not sure, we have an article on how to check your version of Microsoft Office.
The next dialog box lets you browse to a folder and open one file. You can’t specify multiple files.
However, when you check the box for “Select multiple options”, you can select some or all of the worksheets in the file.
The window on the right will give you a preview of a selected worksheet so you can double-check that it has the contents you need.
Click on the Load button in the bottom right of the window.
This opens the Queries & Connections pane in your spreadsheet. Now for the next steps in this process.
Use Queries & Connections To Append The Worksheets
You now have a Query for each selected worksheet. These will appear in the right pane of your new Excel workbook.
If you don’t see the pane in the picture below, it may not be expanded. You should be able to widen the pane by dragging the edge.
The next step is to append the results of all the queries into a single new worksheet.
Right-click any of the queries, it doesn’t matter which. Choose “Append” from the drop-down menu.
The next dialog box lets you choose which queries to append. As you’ve chosen the worksheets that you want already, you want them all.
To work with more than two worksheets, toggle the option to “Three or more tables”. This will show all the worksheets.
You can hold down the control key to select multiple files in the left window and transfer them all across in one go.
When you’re finished adding the worksheets, click the OK button. This will open up the Power Query Editor.
Warning
The worksheet that you selected to run the append feature will be in the right window when this dialog box opens.
For some reason, Excel lets you pick the same workbook again to bring over from left to right. So be careful you don’t end up with the same sheet twice.
Finish With Power Query Editor
The Power Query Editor opens with the new Query highlighted. It is named Append1.
You don’t need to do anything here except click “Close & Load”.
The Editor window closes and you will have a new worksheet named Append1 in your workbook.
This Append1 worksheet has all the data from the worksheets you chose in the previous steps.
Can You Concatenate Worksheets Into The Same Workbook?
I created a new workbook to pull in the data. But can the process be run using a single workbook?
Yes, it can.
Simply create a new worksheet in the workbook that has all the sheets that you want to concatenate.
Follow the same steps I described and you will end up with a new Append1 worksheet with the combined data.
Can You Combine Multiple Excel Sheets Online?
You can’t use Excel Online to follow these steps because the web version doesn’t support Power Query.
Other Ways To Concatenate Data In Different Excel Worksheets
There are other ways to achieve the same results.
You could save each worksheet as a delimited text file in a folder.
Then you can use various methods to concatenate the files. You could use Powershell, Python, or another scripting language to append each file together.
In this scenario, you’d be better off not having a header in each sheet. Otherwise, you’ll have to remove the unnecessary lines.
I wanted to combine the contents of multiple worksheets in an Excel file.
The method in this tutorial will concatenate some or all of the worksheets in a single file into a new worksheet.
If you have data spread across multiple Excel files, you can simply repeat the process for each file.
Format Of The Worksheets You Want To Combine
The data in each worksheet should be in the same format and number of columns.
Each worksheet should have the same header row.
Steps To Concatenate Several Excel Worksheets Into One
- Create a blank Excel workbook (or a new worksheet within the same file as the data).
- Switch to the Data tab in the top ribbon.
- Click on “Get Data” on the far left, choose “From File”, and select “From Workbook”.
If you don’t see the “Get Data” option, then your version of Excel is older than 2016. You won’t be able to follow these steps. If you’re not sure, we have an article on how to check your version of Microsoft Office.
The next dialog box lets you browse to a folder and open one file. You can’t specify multiple files.
However, when you check the box for “Select multiple options”, you can select some or all of the worksheets in the file.
The window on the right will give you a preview of a selected worksheet so you can double-check that it has the contents you need.
Click on the Load button in the bottom right of the window.
This opens the Queries & Connections pane in your spreadsheet. Now for the next steps in this process.
Use Queries & Connections To Append The Worksheets
You now have a Query for each selected worksheet. These will appear in the right pane of your new Excel workbook.
If you don’t see the pane in the picture below, it may not be expanded. You should be able to widen the pane by dragging the edge.
The next step is to append the results of all the queries into a single new worksheet.
Right-click any of the queries, it doesn’t matter which. Choose “Append” from the drop-down menu.
The next dialog box lets you choose which queries to append. As you’ve chosen the worksheets that you want already, you want them all.
To work with more than two worksheets, toggle the option to “Three or more tables”. This will show all the worksheets.
You can hold down the control key to select multiple files in the left window and transfer them all across in one go.
When you’re finished adding the worksheets, click the OK button. This will open up the Power Query Editor.
Warning
The worksheet that you selected to run the append feature will be in the right window when this dialog box opens.
For some reason, Excel lets you pick the same workbook again to bring over from left to right. So be careful you don’t end up with the same sheet twice.
Finish With Power Query Editor
The Power Query Editor opens with the new Query highlighted. It is named Append1.
You don’t need to do anything here except click “Close & Load”.
The Editor window closes and you will have a new worksheet named Append1 in your workbook.
This Append1 worksheet has all the data from the worksheets you chose in the previous steps.
Can You Concatenate Worksheets Into The Same Workbook?
I created a new workbook to pull in the data. But can the process be run using a single workbook?
Yes, it can.
Simply create a new worksheet in the workbook that has all the sheets that you want to concatenate.
Follow the same steps I described and you will end up with a new Append1 worksheet with the combined data.
Can You Combine Multiple Excel Sheets Online?
You can’t use Excel Online to follow these steps because the web version doesn’t support Power Query.
Other Ways To Concatenate Data In Different Excel Worksheets
There are other ways to achieve the same results.
You could save each worksheet as a delimited text file in a folder.
Then you can use various methods to concatenate the files. You could use Powershell, Python, or another scripting language to append each file together.
In this scenario, you’d be better off not having a header in each sheet. Otherwise, you’ll have to remove the unnecessary lines.
