Need to merge multiple datasets into one spreadsheet? Here’s how.
Sometimes the Excel data you need is split across multiple sheets or even multiple files. It can be significantly more convenient to have all of this information put in the same document.
In a pinch, it’s possible to copy and paste the various cells that are required, placing them all in the same sheet. However, depending on how much data you’re working with, this might end up taking a lot of time and effort.
Instead, consider some of the smarter ways to accomplish the same task. These three methods might just allow you to skip some of the busywork when it comes to merging sheets or files in Excel.
How to Merge Excel Sheets
Excel makes it easy to combine more than one sheet into a new workbook.
- Open the sheets you want to merge.
- Click Home > Format > Move or Copy Sheet.
- Use the dropdown menu to select (new book).
- Click OK.
Merging Sheets Into a Single Workbook
The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method has its limitations, but it’s quick and straightforward.
First, open up the sheets you want to merge into the same workbook. Use the Format dropdown in the Home tab so select Move or Copy Sheet.
You should see these controls:
Use the dropdown to select (new book). This is going to serve as the master spreadsheet where we send all of our individual sheets. You can use the Before sheet: box to stipulate the order that the sheets are in.
Repeat this process with the rest of the sheets you want to merge. Then, save your new master document.
Merging Data Into the Same Sheet
Sometimes, you might want to take more than one dataset and present it as a single sheet. This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time.
Here’s the data that I’m going to use. There are two things that are very important if this process is to work correctly; the sheets that you’re consolidating need to use exactly the same layout, with the same headers and types of data, and there can’t be any blank rows or columns.
When you’ve arranged your data to those specifications, create a new worksheet. It’s possible to run the consolidation procedure in a sheet where there’s already data, but it’s easier not to.
In this new sheet, head to the Data tab and click Consolidate.
Select Sum from the dropdown and then use the button in the References field to access your spreadsheet so you can select the data you need.
Do this for all the data sets that you want to merge. You can even draw from other workbooks by using the Browse button, otherwise known as Select in the Mac version of Excel.
Tick the box titled Create links to source data if you’re going to continue to update the data in other sheets, and want this sheet to reflect that. You can also select which labels are carried across with the tick boxes shown above. I chose to tick both boxes because I wanted both sets of labels.
Finally, click OK.
You should end up with something like the screenshot above. Unfortunately, this process isn’t suitable if you want to merge cells with text in them — it only works with numerical data. In that situation, you’ll need to use VBA.
Combining Workbooks With VBA
If you want to combine sheets from several workbooks in a snap, the best way is to write a simple VBA macro. This will come in especially handy if you perform this task on a regular basis.
First, make sure that all the workbooks you want to combine are in the same folder on your computer. Then, create a new Excel spreadsheet that will bring them all together.
Head to the Developer tab and click Visual Basic.
Click Insert > Module and copy and paste the following code, taken from a guide by ExtendOffice:
Sub GetSheets() Path = "C:\Users\Brad\MakeUseOf\MergingSheets\" Filename = Dir(Path & "*.xls") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
Make sure to change the path to wherever the files are stored on your computer.
Next, save your workbook as an XLSM file so that macros are enabled. Then run the macro and you should find that you have a single workbook that contains all the sheets from all the files in the folder.
Look Before You Leap
Merging sheets and files in Excel is rather complicated and messy. This fact should illuminate one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.
Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time. Whenever you start working with a new workbook, it’s best to consider all possibilities of what the file is going to be needed for further down the line.
Excel is great at making documents that can be referred to and used for an extended period of time, but decisions made early on are likely to cause problems later.
Do you have a tip for merging different datasets? Or are you looking for assistance with the methods in this guide? Either way, why not join the conversation in the comments section below?