Document Editing Problems

How to Create Grouped Word Documents from Excel Data

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to generate Word documents from an Excel source where each person has multiple purchase records, outputting a grouped table per person without splitting the Excel data into separate worksheets.

Product: Microsoft Word
Device & OS: not provided
Scenario: Performing a mail merge using an Excel database where a single entity (a person) has multiple corresponding rows (purchases) that need to be grouped together on one page.
Observed behavior: Standard mail merges generate a new page for every single row, failing to group a single person's multiple purchase records into a single page or table.

Before you start

Ensure your Excel data is properly formatted with distinct, valid column headers in the very first row, and that the data contains a common grouping field such as 'Person Name' or 'ID'.

Solution 1Recommended

Use a Directory Mail Merge with a DATABASE Field

Utilize Word's native Directory (or Catalogue) mail merge feature combined with a DATABASE field code to group multiple Excel records into a single table per person.

This method leverages native Word features. By setting the merge type to Directory, you can use field codes to query the Excel file and generate a continuous list or table. A macro might be required to process the final output if your Excel source does not contain a separate list of unique names.

Set Document Type

Open a blank Word document, navigate to the Mailings tab, click 'Start Mail Merge', and select 'Directory' (or 'Catalogue' in older versions).

Connect Excel Data

Click 'Select Recipients', choose 'Use an Existing List', and locate your formatted Excel file.

Insert Field Codes

Press Ctrl + F9 to insert empty field code braces, and manually type your DATABASE field code to query the Excel sheet, using the common identifier (like Person Name) to pull the associated purchase rows into a table.

Run the Merge

Click 'Finish & Merge' and select 'Edit Individual Documents' to generate the grouped tables.

Field Code Complexity: Writing { DATABASE } field codes requires familiarity with SQL syntax within Word. Review Microsoft's documentation on grouped mail merges for the exact syntax needed for your specific column names.

Solution 2

Use a Many-to-One Merge Add-in

Install a third-party Mail Merge add-in designed for Many-to-One merges to easily group records and output individual files.

Efficient Document Generation

Easily Manage Mail Merges with WPS Office

WPS Writer offers an intuitive, user-friendly Mail Merge tool that seamlessly integrates with your spreadsheet data. You can easily connect databases and insert fields without struggling through complicated menus.

1. Open Mail Merge: Launch WPS Writer and navigate to the 'References' or 'Mailings' tab, then click on 'Mail Merge'.
2. Import Data Source: Click 'Open Data Source' and select your WPS Spreadsheet or Excel file containing the organized purchase data.
3. Insert Merge Fields: Use the 'Insert Merge Field' button to place your data columns (like Name or Purchase Details) exactly where you need them in the document template.
4. Merge and Finish: Click 'Merge to New Document' to automatically generate your personalized, data-driven documents.

Fully compatible with Microsoft Word (.docx) and Excel (.xlsx) formats.Intuitive Mail Merge interface for letters, emails, and directories.Lightweight software that processes large data sources quickly.Free alternative with a familiar ribbon interface for zero learning curve.

Frequently Asked Questions

Do I need a separate list of unique names in my Excel file?

When using native Word field codes or VBA macros, having a separate table or sheet of unique names simplifies the grouping logic. However, advanced Many-to-One add-ins can group records dynamically based on a single common column without needing a separate list.

Why is my Mail Merge creating a new page for every purchase row?

Standard 'Letters' mail merges treat every row in an Excel spreadsheet as an entirely separate document or page. To group multiple rows onto one page, you must change the document type to 'Directory' (or 'Catalogue') or use a Many-to-One mail merge add-in.

Can I automatically save the grouped tables as separate PDF files?

A native Directory merge typically outputs one long, continuous Word document. To save each person's grouped data as a distinct PDF file, you will need to run a VBA macro after the merge or use a specialized mail merge add-in that supports automated PDF splitting.

Document & File Issues