The ‘novel’ idea of a formula based mail merge in Google Sheets came to my mind quite recently. I was actually trying to repeat rows or you can say duplicating rows that had some contents.

I have done that successfully. You can read about that experiment here in your spare time – How to Insert Duplicate Rows in Google Sheets.

At the time of working on the above said duplicating rows, I thought of replacing some of the content in each duplicated rows.

I have worked on that idea and the result is this Sheets formula based mail merge. This is an experiment and I hope some of you will like it.

Mail Merge in Google Sheets

Mail merge is all about adding names and addresses from a database to letters/envelopes.

In other words, it’s about generating duplicate copies of a letter or envelop equal to the count of recipients and adding the recipients’ addresses automatically.

This can help us to save lots of our valuable time if we want to send letters with the same content to several peoples/multiple recipients.

In Google Sheets, there is no default mail merge option. It’s not wise to expect one as it’s a Spreadsheet application, not a word processor. But with the help of my formula you can run mail merge in Google Sheets in a limited way.

Let me introduce you to my Sheet (template) and the formulas in use. There are four tabs (the sheet link given at the later part of this article).

Letter Tab – The Letter to Mail Merge in Google Sheets

[User is permitted to edit this tab]

This sheet contains the draft letter.

See some place holders (yellow highlighted) included which are going to be replaced with different recipient addresses while mail merging.

You can change the order of the place holders as well as their position. Also, replace the “Lorem ipsum…” sample text with your original text. There are no restrictions in including more paras.

I have only included five place holders (detail follows) in my formula based mail merge in Google Sheets and allowed the use of the same place holders multiple times in the letter.

Please note that I have typed the whole letter within one cell by inserting new-lines – Start New Lines Within a Cell in Google Sheets – Desktop and Mobile. You should adhere to that setup.

Address Tab – Contains Recipient Names and Their Addresses

[User is permitted to edit this tab]

I have inserted five unique place holders in the letter above. As I have mentioned above, if you want, you can repeat the same place holders multiple times.

The 5 place holders are [recipient name], [recipient address 1], [recipient address 2], [zip code], and [title].

Actually, these are the field labels (row headers) in my address dataset. See the content of the ‘Address’ tab below.

At present, there are two records in this address book. So once I mail merge the letter, there would be two letters with two different recipient addresses.

You can add more records (recipient addresses and their titles) to this ‘address book’. I will explain that later.

Formulas Tab

[User interaction not required in this tab]

This is the third tab in my mail merge Sheet. It contains two formulas one in the cell A1 and the other in cell B1.

=ArrayFormula(vlookup(sequence(counta(Address!A2:A),1)^0*4,{4,Letter!A1},2,0))

The above formula in cell A1 makes duplicate copies of the letter from the “Letter” tab based on the number of records in the “Address” tab.

I am not going for a formula explanation as usual here. Here my goal is to help you use Google Sheets to mail merge letters.

The formula in cell B1 in this ‘Formulas’ tab simply populates the records from the “Address” tab.

=ArrayFormula(Address!A2:E)

Mail Merge Output Tab – Mail Merged Letters

[User interaction not required in this tab]

This is the last tab that contains the mail merged letters. In all the letters the place holders will be replaced by proper addresses from the “Address” tab.

There is a formula in cell A1 in this ‘Mail Merge Output’ tab that does this substitution/replacement of place holders.

The Formula (in Cell A1) to Create Mail Merge in Google Sheets:

=ArrayFormula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Formulas!A1:A,"[recipient name]",Formulas!B1:B),"[recipient address 1]",Formulas!C1:C),"[recipient address 2]",Formulas!D1:D),"[zip code]",Formulas!E1:E),"[title]",Formulas!F1:F))

Sample Sheet

How to Use My Template to Mail Merge Letters in Google Sheets

You just need to follow two steps.

1. Modify the letter in the ‘Letter’ tab. Please only use the place holders from my above given 5 place holders’ list.
2. Then add the recipients’ addresses in the “Address” tab.

If there are five records (recipient names and addresses) in the ‘Address’ tab, the formula would mail merge 5 letters in the ‘Mail Merge Output’ tab.

You can copy the output tab letters to Google Docs, MS Word or any other word processing apps or convert to PDF directly from within Google Sheets. Don’t forget to insert the page breaks between each letter.

Just try it and leave your feedback below. Thanks for the stay. Enjoy!