It might be possible that you have many different worksheets in a workbook because you are working on them for a long time. A time comes when you need to combine these Excel sheets’ data into one sheet.
Manually, it would be a time-consuming as well as tiring thing to do, however, you can use different approaches to execute this function. At times, you may find Excel a complex tool because it offers many different features to use. Here in this post, you will the tricks about Excel merging spreadsheets easily into one sheet. Of course, copying and pasting data from multiple sheets would not be an easy thing that’s why you have to work on it another way.
Excel Merging Spreadsheets into One File
Other than copy and paste option, you can use Move or Copy Sheet feature to merge multiple sheets into one file. Your many Excel files could have different sheets that needed to merge into one. Though each method comes with its limitations, however, it is quite simple and easy.
Follow the steps given below:
- Open the sheets that you need to combine into the same workbook.
- Choose the Home tab from the top ribbon.
- Click on the Format option from the Cells menu.
- Choose the Move or Copy Sheet option.
You will see the Move or Copy window appears that lets you choose the master spreadsheet from the To book drop-down menu.
Choose the new book option to make a new file and then select an existing file.
Click on the Before Sheet option that helps in identifying the order of the sheets.
Click OK.
You can repeat the process for each sheet that you need to merge.
For a quick process, you can choose different sheets at once.
For this, press and hold CTRL right as you click the tables given at the end.
Excel Merging Spreadsheets with Get & Transform Data Tools
PowerQuery is an amazing series of tools that launch in Excel 2016. It was remade as Get & Transform Data. With this toolset, you can simply drag data all at once from other workbooks to merge them into one workbook.
Bear in mind that the PowerQuery feature is solely used for Windows and Mac users who will not be able to avail of this feature.
Before getting started, you just need to check the data because this is the most critical phase of merging Excel spreadsheets. You have to use a similar setup for the data structure using the same columns. Excel itself does not know where you have to place the data that’s why you will not be able to merge a four-column spreadsheet and a five-column spreadsheet.
Most likely, you will have to merge sheets while downloading data from systems. In this case, it is confirmed that the system is configured to download data in the same columns every time. You must ensure that the format is the same every time you download a file from Google Analytics.
Eventually, it ensures that you can merge different sheets all at once.
Now, you have learned how to combine Excel sheets.
Excel Merging Spreadsheets with a Different Order of Columns
You might have noticed a different column order used in sheets that multiple other users have made. So, how would you deal with this situation? Let’s understand how would you do it.
- You can execute the function with Combine Sheets magic.
- You will see column headers do the combined data smoothly.
- Excel Merging Spreadsheets with VBA
When you are a diehard fan of VBA tricks, you can merge Excel sheets using the VBA approach. VBA macro would be a great option if you are a regular user of this hack.
- First, all your files must be in the same folder that you need to combine. Go to the Developer tab and choose the Visual Basic option from the Code section.
- Click on the Insert and then Module option.
For this, you have to copy and paste the following code:
Sub GetSheets()
Path = “C:\FILE PATH\”
Filename = Dir(Path & “*.xlsx”)
Do While Filename <> “”
Workbooks.Open Filena me:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy Aft er:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
In this procedure, you need to change the pathway where the files are stored on the system. Now, click on the run button or else press the F5 key from the keyboard to run the macro code.
You will see the files are merged instantly into one sheet. Close the Visual Basic window to get back and you will see the results.
Click Ok and the changes will save.
Final Thoughts
Excel merging Spreadsheets needs you to follow some pre-set procedures that you can use. It is up to you which method you will follow to combine multiple sheets into one sheet. Each method works effectively and you can practice any of the above-mentioned methods.
