How to do the Excel equivalent mail merge?

Copper Contributor
Hello, Can we do something in Excel that's similar to a mail merge in Word. I have a spreadsheet that is essentially a form letter in Excel format, contains empty fields which I want to fill with the the records from another spreadsheet which has information i.e., For example, first name, last name, employee id, marks, etc.
Let's say I have 100 records in the second spreadsheet. The goal is to make 100 versions of the first spreadsheet (the form letter), each filled out with the info of a given record (or row) from the second spreadsheet.

Thank you in advance
3 Replies

@Nancy1231820 

 

Are you familiar with the VLOOKUP or XLOOKUP functions? Those could be used to retrieve the data in each row of the source data -- so long as there's some unique identifier (e.g., SSN or Employee ID or customer ID) to each row. You'd just have to lay things out so that there'd be an "off-screen" identifier that you'd change each time as the basis for the looking up of the data elements to fill in the document itself.

 

Then if needed, you could write a macro that simply runs through the database one row at a time and sends the resulting document to the printer. If there are only 100 rows, it might be just as time efficient to simply go row by row manually.

 

A question, though, if I may: you can use the Excel database as the source for a Word mail merge, and have a lot more flexibility in the formatting of the resulting document. Perhaps there's some compelling reason to use Excel to produce the merged document itself, but I'm skeptical. Word is far more suited to work with an Excel database to do that merging. And no need to write a macro. I speak as someone who did this kind of thing back during my working career in HR, producing hundreds of personalized documents from a huge Excel database.......  I would use Word, though, for the merge. So that's why I'm curious what is leading you to want to do the merge in Excel.

Thank you for your quick response. Mathetes!
 
Yes, I am familiar with Vlookup functions in Excel, and we do have a unique identifier for each row like Employer ID & Invoice #.
 
I am Ok with Excel or Word forms. All I need is to split 100 records from the second spreadsheet to 100 versions of the first form letter(I can do the form in word or excel. Anything works for me based on your response), each filled out with the info of a given record (or row) from the second spreadsheet.  
 
Please give an example of how to do it (Excel or Word).
 
Thank you again, and I appreciate your time.

@Nancy1231820 

 

Well, the main idea if you can do it in Word is to use Word's Mail Merge function, specifiying your Excel database as the source. Here are a couple of YouTube instructional videos that explain the process. They'll do better than anything I could put together on the spot.

 

There's a little bit of a learning curve, as is true for any such new technique, but once you've mastered how to place a few fields, I'm sure it'll go smoothly.

https://www.youtube.com/watch?v=mFqCvTOpOL0

 

https://www.youtube.com/watch?v=_Efb_oMgxEs