Welcome to Helptips.org
Sometimes you may have a spreadsheet that needs several different people or departments to enter their own data onto the spreadsheet. You can give each department a shared version of the spreadsheet, and once they have done their work, you can merge all the data back into your master copy.
Sharing a Spreadsheet
Before you can pass the spreadsheet out, you will need to make it shared. If you do not, then the merge will not be possible.
First open the spreadsheet you are going to pass out to the other people.
Because this spreadsheet is not shared, we need to share it. Click Tools, then Share Workbook.
In the new window, check the Editing tab is selected.
Put a tick in the box at the top to allow sharing of the Spreadsheet. Then click OK.
A warning box will pop up to confirm the spreadsheet will be saved. Click OK to continue and save.
You will notice at the top of Excel, next to the file name it will say [Shared]
You can now send copies of this spreadsheet to the relevant people or departments to add their data.
Once the departments’ data has been completed, and they have sent you a copy, you can then merge their data onto your master spreadsheet.
For a merge to work, all the spreadsheets need to be in the same folder. With them all being in the same folder, they will have to have different names. It may be worth adding the department or persons name to the spreadsheet name for easy identification.
Click Tools, then Compare and Merge Workbooks.
Navigate to where your folder is, and then select the spreadsheet you want to merge with your master copy. Click OK once you have made your selection.
The two spreadsheets will then be merged. In my example I have added data to Sheet2 and renamed it to Income.
I can see the new merged data when I click the Income tab.
You will notice the data has been imported, but not the formatting, so you may have to tidy up the look of the spreadsheet. My final page looked like this.
You can merge data from as many spreadsheets as you like, but they must have all originally come from your master spreadsheet and be shared.
© Copyright 2012. All Rights Reserved.