Document Editing Problems

How to Create Account Statements from Excel Data in Word

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to generate monthly account statements in Word using transaction data from Excel, but the number of transaction rows varies per account.

Product: Microsoft Word
Device & OS: not provided
Scenario: Importing variable-length Excel transaction records into a standardized Word account-statement template.
Observed behavior: Standard Mail Merge either duplicates the header page for every transaction row or leaves the header fields blank when utilizing the Next Record rule.

Before you start

Ensure your Excel dataset is organized with clear column headers (e.g., Account ID, Date, Amount) and sorted by the common identifier so that all transactions for a single account are grouped together.

Solution 1Recommended

Use the Word DATABASE Field

By replacing standard merge fields with a DATABASE field, Word can dynamically pull a varying number of transaction rows from an Excel dataset into a single statement document.

The standard Mail Merge feature treats every row in Excel as a separate document. The DATABASE field bypasses this limitation by running an SQL query to retrieve multiple matching records based on a specific Account ID.

Insert the Database Field

In your Word template, click on the 'Insert' tab, go to 'Quick Parts' in the Text group, select 'Field', and choose 'Database' from the list.

Toggle Field Codes

Press 'Alt + F9' to reveal the field codes in your document, which will look like { DATABASE }.

Configure the SQL Query

Edit the field code to include the path to your Excel file and an SQL query using the \d and \s switches. For example: { DATABASE \d "C:\path\to\data.xlsx" \s "SELECT * FROM [Sheet1$] WHERE AccountID = '{ MERGEFIELD AccountID }'" }.

Apply Table Formatting

Add the \l switch followed by a format number (e.g., \l "15") to apply built-in table styling to the dynamically generated transaction rows.

Preview and Execute

Press 'Alt + F9' again to hide field codes, then click 'Update Field' or 'Preview Results' on the Mailings tab to verify that the varying rows populate correctly.

Syntax Precision: Ensure the file path and sheet names in your SQL query exactly match your Excel file. Any typo will prevent the database field from pulling the correct rows.

Solution 2

Use the Merge Tools Add-in

If coding SQL queries is too complex, a third-party add-in can simplify the process by providing a dedicated graphical interface for many-to-one merges.

Free Microsoft Office alternative

Easily Manage Spreadsheets and Documents with WPS Office

Complex mail merges and database fields in Microsoft Word can be frustrating. WPS Office offers a free, highly compatible, and user-friendly alternative to handle your document generation tasks seamlessly.

Fully compatible with Microsoft Word (.docx) and Excel (.xlsx) formats.Intuitive built-in Mail Merge wizard for easier document generation.Free and lightweight, consuming significantly fewer system resources.Unique tabbed interface to manage your spreadsheets and documents in one single window.

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard Mail Merge duplicate headers for each transaction?

Standard Mail Merge operates on a one-to-one basis, meaning it treats every single row in your Excel spreadsheet as a separate document. If an account has five transactions, Word generates five separate pages instead of grouping them onto one statement.

What is a many-to-one mail merge?

A many-to-one mail merge is a technique used when multiple source records (many) need to be combined into a single generated document (one). This is frequently used for generating monthly invoices, account statements, or product catalogs.

Can I use the Directory type merge to group records?

Yes, changing your document type to 'Directory' (or 'Catalog' in older versions) allows you to list multiple items sequentially without page breaks. However, it requires complex IF field coding to trigger page breaks and header reprints when the Account ID changes.

Document & File Issues