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!

9 comments:

  1. Excellent Sirji.
    Can you suggest me links/books to read on Pharma SFE?

    ReplyDelete
  2. Nice tip. This will save lot of manual efforts.

    ReplyDelete
  3. http://www.zsassociates.com/
    and their free downloads is a wonderful place to start with.

    ReplyDelete
  4. Your absolutely cool Sir, keep up the great work to enlighten us geeks :)

    ReplyDelete
  5. Hey Satya can you help me how do I make a good calendarised presentation for an entire years activities..

    ReplyDelete
  6. @Judy.Thank You. Very happy to receive your comment:-) WRT Calender. Let me think about it and get back to you.

    ReplyDelete
  7. The example process given above,will definitely help in collate data from multiple excel files from employees just with click of a button.Thanks for share this incredible post regarding collect data in excel.
    data collection instruments

    ReplyDelete
  8. The moral values taught in the elementary education of the students are the base for social life of students. The entire life is spent on the bases of the social norms learned pay 4 essay online blog and then applied. The social life is the main source of interaction with others.

    ReplyDelete