Sometimes the Microsoft Excel data that you require is divided across multiple sheets and even multiple files. It is much more convenient to have all this information in one document.
In a pinch, you can just copy and paste the cells that you need and place them all on one sheet. This can take some time and effort depending on the amount of data you have.
Consider alternative ways of accomplishing the same task. These techniques might allow you to save time when merging files or sheets in Excel.
How to merge Excel sheets
Excel makes it easy to combine multiple sheets into one workbook. Create a new book by merging sheets:
- You can open the sheets that you wish to merge.
- Click Home > > Copy or Move.
- Select (new Book) from the dropdown menu.
- Click OK.
How to combine sheets in Excel into one file
The Move and Copy Sheet commands are the best ways to merge Excel sheets. Although it has some limitations, this method is quick and easy to merge Excel sheets.
Open the sheets that you wish to merge in a new workbook. You can now:
- Go to Home
- Click Format
- Select Copy Sheet or Move
Controls for moving sheets should be visible, as well as the order of those sheets.
Select (new books) from the dropdown. This will be your master spreadsheet, where you can send each sheet. To specify the order in which the sheets should be placed, you can use the Prior sheet box.
Continue this process for all other sheets that you wish to merge. Save your new master document.
Merge Excel Data Into One Sheet
Sometimes you may want to present multiple datasets on one sheet. This is easy in Excel, provided you take the time to make sure your data is properly formatted.
This process must meet two conditions. The first is that the sheets you are consolidating must have the exact same layout with identical headers. There can’t be blank columns or rows.
Once you have arranged your data according to these specifications, create a new sheet. You can run the consolidation process in an existing sheet if there is data. However, it is easier to not do so.
Navigate to the tab, and then click Consolidate. Select Sum in the dropdown, then click the button in the Refer field to open your spreadsheet and select the data that you require.
Click Add to add the next data set. Next, select the data the same way. This is repeated for all datasets you wish to merge. You can also draw from other workbooks using the Browse button. This button is Select for the Mac version.
If you plan to continue updating the data in other sheets, check the box Create Links to Source Data. As shown above, you can choose which labels will be carried over with the Use labels within checkboxes.
Click OK.
This process won’t work if you need to merge Excel sheets with text. This only works for numerical data. VBA is required to merge excel sheets in situations involving text.
How to combine Excel sheets with VBA
A simple VBA macro is the best way to quickly merge Excel sheets from multiple workbooks. If you do this job often, it will be especially useful.
First, ensure that all files you wish to combine are located in the same folder. Next, create an Excel spreadsheet to bring them all together.
Go to the Developer tab, and then select Visual Basic. Click Insert > Module.
Extension explains how to merge excel sheets vis VBA macro. Copy and paste this code:
Change the path to the location of the files on your computer.
Save your workbook as an XLSM to enable macros. Run the macro and you will see a single file that contains all of the files within the folder.
Be careful when merging Excel data
Excel’s merging of files and sheets can be difficult and confusing. This highlights one of the most important lessons in Microsoft Excel: It’s always a good idea to plan ahead.
It is not uncommon to have to merge data after the fact, especially when you work with large spreadsheets that are in use for a while. It is important to think about all possible uses of the file before you begin working on a new workbook.
Excel excel is great for creating documents you can refer back to over and over again. However, making decisions early can prevent or create problems later. As they say, prevention is worth a pound.
