The deliverable: one Excel table, one Word template, and by the end of this, a personalised one-page report for every row in that table. Forty rows, forty reports, none of them typed by hand.
You need two things before you start. An Excel table with one row per record and column headers that mean something (ClientName, RegionTotal, RenewalDate, whatever your report actually needs). And a Word document laid out exactly the way you want the finished page to look, with placeholders standing in for the bits that change from person to person. Word calls the whole mechanism mail merge, and yes, the wizard looks like it hasn't been redesigned since 2003. It hasn't. It also still works.
The wizard is dated. The job it does isn't.
Once you know Power Automate exists, the instinct is to skip straight to a flow: read the Excel rows, populate a Word template action, loop, done. That instinct is correct for a recurring, unattended job, a report that has to run itself every Monday without you touching it. It's the wrong first move for the report you're building once, or the one you'll run monthly by hand while you decide whether it's even worth automating.
Mail merge has zero setup cost. You already have both files open. The wizard reads the table, walks it row by row, and hands you an actual reviewable document in less time than it takes to build a flow's first action. Reach for the heavier tool once you know you'll run this often enough to need it, not before.
Step 1: Build the Excel source as one clean table
- Put your data on its own sheet, one row per record, headers in the first row.
- Select it and press Ctrl+T to make it a real Excel Table, not just a range, then give it a name under Table Design.
- A named table survives you adding rows later. A plain range means re-pointing the merge every time the row count changes.
- Close the Excel file before you touch Word. Word locks onto the data at merge time and gets confused if the source is still open (this bites people on Mac especially, where the file also needs to live on local storage rather than a synced OneDrive folder).
Step 2: Point Word at that table
- Open the Word document and go to Mailings > Select Recipients > Use an Existing List.
- Browse to the workbook. Word will ask which sheet or table to use, pick the named table from Step 1, and leave "First row of data contains column headers" checked.
- Mailings > Edit Recipient List is worth a look here. Untick any rows you don't want in this run, or filter to a subset, this quarter's renewals, this region's clients, without touching the source file.
Step 3: Lay the report out with merge fields
- Everywhere the report should say something different per person, use Mailings > Insert Merge Field and pick the matching column.
- Format the surrounding text as if the value were already there. Bold the client name, size the heading, whatever the finished page needs. The field inherits the formatting of the text around it, not the other way round.
- Get the layout as close to finished as possible before merging. Fixing the template after the fact means re-merging from scratch.
Step 4: Preview before you trust it
- Mailings > Preview Results flips the document to show real data instead of field names. Click through at least five or six records, not just the first one.
- This is where you catch the record with the blank field, the name that overflows the layout, or the number that's about to merge as something unrecognisable, more on that in a second.
Step 5: Finish the merge the way this report actually needs
- Mailings > Finish & Merge has three options, and picking the wrong one is the most common way this goes sideways. "Print Documents" sends straight to a printer. "Send Email Messages" needs Outlook and a column with a real email address. "Edit Individual Documents" is the one you want for a saved report: it builds a new Word document containing every record.
- That new document is not forty separate files. It's one document with a section break between each record. If every person needs their own saved file, that's a manual save-and-split job, or a job for an actual automation layer sitting on top of this (Power Automate's Word template action, not a macro), not something the wizard does on its own. Know which one you actually need before Step 5, not after.
Common mistakes
Numbers and dates merge looking nothing like the Excel cell.
A date column often merges as a raw serial number, and a currency column loses its formatting entirely, because mail merge pulls the underlying value, not the display format you see in Excel. The fix that doesn't involve fighting Word's field codes: add a helper column in Excel that stores the value already formatted exactly as text, =TEXT(A2,"d mmmm yyyy"), and merge that column instead. If you'd rather fix it on the Word side, select the field, press Shift+F9 to see its code, and add a picture switch, \@ "d MMMM yyyy" for a date or \# "$#,##0.00" for currency, then press F9 to update it. Both work. The Excel fix is faster to explain to whoever inherits this file after you.
Assuming "Finish & Merge" saves one file per person.
Covered above, worth repeating because it's the single most common expectation mismatch in this whole workflow: the built-in merge gives you one combined document. If the report only has value as a shared PDF or a printed stack, that's fine as-is. If each person needs their own file in their own folder, budget time for that as a separate step, not an assumption baked into "Edit Individual Documents."
Where to go from here
Build a Word template that holds its formatting is worth doing first if your template's headings and spacing don't survive being reopened a week later. Mail merge will faithfully reproduce whatever formatting problems the template already has, across every single row.
Word holds up fine for a structured, repeating report like this one, the shape of the document doesn't change, only the data does. It's the free-form process document that gets edited by six people and never updated that turns into a liability. More on that soon.
Frequently Asked Questions
Does this work with a plain Excel range, or does it need to be a formatted Table?
A plain range works, but a named Table (Ctrl+T) is safer. It survives you adding rows later without re-pointing the data source, and Word reads the header row the same way either way.
Can I get individual PDFs instead of individual Word documents?
Not directly from the wizard. "Edit Individual Documents" gives you one combined Word file, and saving that as PDF gives you one combined PDF. Separate PDFs per person need either a manual split or an automation layer built for that specific job.
Do I need Outlook installed to do any of this?
No. Outlook only comes into it if you choose "Send Email Messages" in Finish & Merge. Saving or printing a report never touches it.
What happens if a row has a blank cell in one of the merge columns?
Word merges an empty string, not an error, so the report just shows blank space where that value should be. If a blank matters, either filter that row out in Excel first, or wrap the field in a Word {IF} field to substitute default text.
Does this work in Word for Mac?
Yes, with two conditions worth knowing before you start: the workbook has to be closed before the merge runs, and it needs to live on local storage rather than a synced OneDrive folder, or Word can lose track of it mid-merge.