Mail merge and quotation templates: why numbers arrive wrong and how to test a batch

What a merge does

A mail merge connects a Word template to a spreadsheet. Each column name becomes a merge field, and for every row Word produces a document with the fields replaced by that row's cell contents. Microsoft says column names in the spreadsheet should match the field names, that all the data to be merged should be on the first sheet and that Preview Results lets you step through records to see how each looks. Finishing the merge offers printing or sending as email messages; for quotations that go to customers, you will want to read each one first.

Numbers and codes arrive differently from how they look

Microsoft documents three traps. Merged numbers come through without currency or percent symbols, so a price shows as 50.00 and a rate as 20 unless you add the symbol before or after the merge field in the Word document. A percentage format in Excel multiplies the stored value by 100 on display, so a percentage column is safer stored as text if you want it to appear as written. Postal codes need text format, or leading zeros are lost.

The same care applies to dates and to long reference numbers. Decide how each should print, and make the spreadsheet hold it in that form.

• Add currency and percent symbols in the template, outside the field.
• Hold postcodes and codes as text.
• Check one record of each kind in Preview Results.

Do the arithmetic in the workbook, and decide the rounding rule

The template should show results, not calculate them. Work out line totals and the grand total in the workbook, where they can be checked, and merge the finished figures. Decide the rounding rule in words: round each line, then add, or add, then round? The two can differ by a penny, as the worked example shows with invented figures.

Excel stores 15 digits of precision and cannot hold some decimals such as 0.1 exactly, so apply the ROUND function at the point your rule says rounding happens, rather than relying on number formatting. Microsoft's ROUND page shows its behaviour with examples such as 2.15 to one decimal place giving 2.2.

Test with a proof set before the batch

Pick about ten records on purpose: a normal one, a zero quantity, a quantity at a price break, an item missing from the price list, a very long description, a name with an accent or an apostrophe, a blank optional field. Calculate their totals by hand under the written rules. Merge them and compare. Look for unfilled fields, wrong symbols and any price that is not on the price list.

Then merge the batch and check that the sum of the document totals equals the sum in the workbook. A proof sheet that lists each brief, each lookup and each calculation makes later checking possible.

Where the paid job fits, and where it does not

One approved template, one price list with unique codes and up to 40 briefs can be turned into a batch of quotation documents with a proof sheet as a one-off job from £495 (an untested proposal). It is accepted by ten hand-calculated test briefs, a scan for unfilled fields and non-list prices, template layout checks and a total reconciliation. Payment follows your sign-off. You keep the template and workbook.

It does not set prices or discounts, decide how tax applies, advise whether a quotation is binding, send anything to customers or connect to other systems. The first enquiry needs your rules in words and the counts, never the price list or customer details.