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!


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

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

    and their free downloads is a wonderful place to start with.

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

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

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

  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

  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.

  9. Data analysis in business paraphernalia has different facets and methodologies. Different aspects of life which includes businesses, politics, science, etc have different interpretation of the data, but the data collection is a basic thing for successful execution. quality data collection

  10. Invaluable blog post - Coincidentally , if people needs to merge two PDF files , my company discovered a service here Alto-Merge.

  11. Data analysis in business paraphernalia has different facets and methodologies. Different aspects of life which includes businesses
    Write My Paper

  12. It is a great website.. The Design looks very good.. Keep working like that.

  13. Thanks for posting such a useful information
    Business templates | Excel templates

  14. Anonymous14 July, 2017

    This was really interesting info in this blog that to very happy for the nice technology in this office training malaysia

  15. The post is written in very a good manner and it contains many useful information for me. Microsoft Excel Training Courses Malaysia

  16. here is another tool for this

  17. Good website! I truly love how it is easy on my eyes it is. I am wondering how I might be notified whenever a new post has been made. I have subscribed to your RSS which may do the trick? Have a great day! for grammarly trial click here

  18. including reasonable comments here... global artists network

  19. Please receive me know if youre seeking out a article creator on your web page. you have a few surely large posts and i temper i'd be a pleasurable asset. in case you ever throb to confess a number of the burden off, id virtually be on hearth going on kind of to jot down a few fabric to your weblog in quarrel for a colleague observe to mine. Please ship me an e mail if eager. thanks! Advanced Excel Training Mumbai

  20. It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing. grammar checking

  21. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. User Profile Chinnagounder Thiruvenkaam