If you are using Excel for expense tracking, sales data comparison and other finance related stuff, you will probably come into a situation when two or multiple Excel spreadsheets should be rejoined and analyzed. Whether you have multiple offices or several people working on the same thing and using different spreadsheets, you will eventually need to learn how to merge cells in Excel. The cells you need to combine can be in the same workbook but different worksheets or in the totally different workbooks. Either way, you can merge spreadsheets and compare results you need.
How to Merge Cells in Excel Placed in Different Workbooks
If you just want to have worksheets from different Excel files merged into one document, you can achieve that in 3 easy steps. Here is how to do it:
- Open Excel documents you want to merge.
- Right mouse click on the worksheet you want to merge with another one and click on the Move or Copy.
- On the drop-down menu Move selected sheets to book, select the appropriate document and click OK.
Note: The data you have moved to another Excel spreadsheet will be permanently deleted from the worksheet where was initially created. If you still want to keep a copy of that data, make sure you tick the Create a copy box.
By following these steps, you can merge data from one Excel spreadsheet to another but the data from merged spreadsheets will still be in different sheets. If you want to merge data from two different sheets into one for better data comparison, just keep reading.
How to merge cells in Excel that are in the same workbook?
The thing with joining two worksheets into one is that you cannot just add data from one worksheet to another, like you did in the first case. What you need to do is to create a master worksheet that will be empty. There will be placed the data from two or more worksheets if you follow these guidelines:
- Select the upper-left cell of the area in the master worksheet, where you want that consolidated data to show up.
- Go back to one of your worksheets and select the data you want to merge. In the tab Data, click on Consolidate (Data tools tab).
- The window will come up like shown on the image below. From the Function drop-down list, choose the summary function that you want Excel to use to merge worksheets. The default function is SUM but you can also use other functions, according to your needs.
- Click the Browse button to find your workbook and the file path will be shown in Reference
- Go to another worksheet and select the data you want to merge. The Reference box will show up and you just need to go back to the first worksheet when the Consolidate dialog will reappear. Click Add You can repeat that action for all ranges you want to add to your merged worksheet.
- Press OK, and Excel will merge the worksheets for you.
Note: Make sure that data has the same layout and format and that there is no blank space within the rows.