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!