Collect and Compile data in excel

We often encounter situations where many employees contribute to data and at your end, you need to collate the data into a single file.
The usual practice is , excel workbooks are sent to employees to fill in data.
When excel sheets come back with data, they are manually collated by cut and paste.
A simple solution to avoid this manual labour is "sharing excel sheets".
Follow the simple process given below to help you collate data from multiple excel files from employees just with a click of a button!
OK.. may not be one click. May be few clicks!
First things first: 
First, you need to prepare the worksheet you wish to share (to collect data)
  • Open the workbook you want to share
  • Go to Tools >> Share Workbook
  • Click Editing Tab -Select Allow changes by more than one user at the same time
  • Click and Open Advanced Tab and select setting as required and click “OK”.
  • Now save the workbook and give it a name ( eg: Cust_Nos.xls) save the file in a separate folder
  • Now make as many copies of the workbook as you would need to distribute by using Save as a command from the file menu.
  • Give a specific name for each Copy like Cust_Nos_John.xls;Cust_Nos_Mary.xls  etc to identify
  • Now send each specific file to a concerned employee for their Input 
The last step is to merge files ( Received with input from employees)
  • Save each file from the employee in the same folder where your base file is stored
  • Open the base file
  • Go to Tools >> Compare and Merge Workbooks


  •  Browse to the file from employee  - select the first file Cust_Nos_John.xls
  • You will observe that the data given by John in his file appears in your base file!
  • Repeat the same by Opening all files from each employee.
Watch this video for a demo

Boom! You have merged data from Many files with ease and no errors!