A simple solution to avoid this manual labor is "sharing excel sheets".
Follow the simple process given below to help you collate data from multiple excel files from employees just with click of a button !
First things first:
First you need to prepare the work sheet you wish to share (to collect data)
- Open the work book you want to share
- 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 work book and give it a name ( eg: Cust_Nos.xls) save the file in a separate folder
- Now make as many copies of the work book as you would need to distribute by using Save as command from 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 concerned employee for their Input
The last step is to merge files ( Received with input from employees)
- Save each file from 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!