Collect and Compile data in excel

We you often encounter situations where many employees contribute to data and at your end , you need to collate the data in to a single file.
Usual practice is ,excel workbooks are sent to employees to fill data.
When excel sheets come back with data, they are manually collated by cut and paste.
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 !
OK.. may not be one click. May be few clicks!
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
  • 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 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!

No comments:

Post a comment