How to Prepare a Spreadsheet for Bulk Document Generation
Bulk document generation relies entirely on the shape of your spreadsheet. If your data is clean and structured, the process takes seconds. If your spreadsheet contains floating titles, merged cells, or complex formulas, the generation will fail or produce empty documents.
This guide shows you how to structure a spreadsheet—whether in Excel, LibreOffice Calc, or Google Sheets—so that it works cleanly with mail merge and browser-based bulk generation tools like ManyDocs. We will take a messy customer list and fix it step by step, ensuring your batch generation works the first time.
The one-header-row rule
Every bulk document tool expects the exact same layout: row 1 contains your column names, and row 2 onwards contains your data.
Many spreadsheets start life as visual reports. They might have a big bold title in row 1, a blank row 2, and headers in row 3. They often use merged cells to group categories. These visual layouts break document generation. When the tool reads your file, it looks at the first row to figure out your placeholder names. If it sees a single title and mostly blank cells, it stops or maps everything incorrectly.
Here is an example of a broken spreadsheet layout that will fail:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Monthly New Client Onboarding List | (merged) | (merged) | (merged) |
| 2 | (blank row) | |||
| 3 | Client Details | (merged) | Contract Info | (merged) |
| 4 | First Name | Last Name | Start Date | Value |
| 5 | Jane | Doe | 01/04/2026 | 5000 |
To fix this, delete rows 1, 2, and 3 entirely. Unmerge any cells. Your column headers must sit firmly in row 1, with no gaps above them.
Here is the correct, cleaned layout for the exact same data:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | FirstName | LastName | StartDate | Value |
| 2 | Jane | Doe | 01/04/2026 | 5000 |
| 3 | John | Smith | 15/04/2026 | 3500 |
Naming your columns
Your column headers must exactly match the placeholders in your template. If your Word document uses ((FirstName)), your spreadsheet column must be named exactly FirstName.
The most common invisible cause of failed replacement is a trailing space. If you type "FirstName " in your spreadsheet header, but "FirstName" in your template, they do not match. The generator will leave the placeholder untouched in the finished document.
Keep your header names short, unambiguous, and free of spaces. "StartDate" is much better than "Date the contract begins". Consistent capitalization also prevents errors. While some tools guess what you meant, matching the exact casing guarantees your data lands in the right place.
One row equals one document
Document generation operates on a strict rule: every row in your spreadsheet produces exactly one finished document.
If your source data exports with one row per line item—for example, a customer buying three different software licenses takes up three rows—generating from that sheet will produce three separate documents for the same customer.
To generate a single document per customer, you must reshape your data so each customer takes up exactly one row. You might need to flatten your data using columns like License1, License2, and License3. ManyDocs replaces placeholders with values; it does not support looping over a variable number of line items to build dynamic tables. If an invoice needs ten lines for one customer and two for another, bulk placeholder replacement is not the right tool for that specific job.
Formulas and pasted values
ManyDocs and similar local generators read the raw values stored in your spreadsheet file. They do not contain a full spreadsheet engine and do not recalculate your formulas during the import.
If column C contains =A2+B2, and you update A2 just before saving, the saved file might still contain the old result in C2 until the spreadsheet software fully recalculates and commits the save. More importantly, complex external links, VLOOKUPs, or array formulas can fail to read correctly outside of Excel.
Before generating documents, freeze your calculations.
- Select your entire data range.
- Copy it.
- Use "Paste as Values" (Ctrl+Shift+V or via the Paste Special menu) over the exact same cells.
This replaces all active formulas with final, static text and numbers, ensuring exactly what you see is what gets exported.
Formatting is ignored
Spreadsheet cell formatting changes how a number looks on your screen, not what is stored in the file. Cell background colors, bold text, and currency formats do not carry over to your finished Word or ODT documents.
If you have a date stored as 45366 but formatted in Excel to display as "14 March 2026", ManyDocs reads the underlying raw number. If you format a cell as currency so the number 1200 displays as "$1,200.00", the generator reads "1200".
When the printed form of a number or date matters, store it as text. You can use Excel's =TEXT() function in a new column to convert a date or currency into a formatted text string.
For example, =TEXT(C2, "dd mmmm yyyy") forces a date into text, and =TEXT(D2, "$#,##0.00") forces currency formatting. Once you create this new text column, copy it and paste it as values. Map your document placeholders to this new text column, not the raw data column.
Empty cells and optional fields
Not every record has complete data. Some customers have a second address line, a middle name, or a specific discount code, while others do not.
When a generator encounters a blank cell, it replaces the placeholder in your document with nothing. If your template is formatted as ((AddressLine1)), ((AddressLine2)), a blank second line leaves a stray comma sitting in your paragraph.
Design your template sentences around optional data. Instead of typing punctuation directly into the Word document, build the complete string in your spreadsheet. Use a formula like =IF(B2="", A2, A2 & ", " & B2) to combine the address lines perfectly. Paste the result as values, and use a single ((FullAddress)) placeholder in your template.
Duplicate rows
If your spreadsheet contains two identical rows, you will generate two identical documents. Sometimes this is a mistake, such as downloading an HR export twice and pasting both sets of rows. Sometimes it is intentional, like printing duplicate certificates for a physical backup archive.
ManyDocs checks your spreadsheet during loading and flags identical rows before generation begins. You can review the warning and decide whether to proceed or clean up your data first. If it is a mistake, use your spreadsheet's built-in "Remove Duplicates" feature to clean the list quickly before uploading it again.
Special characters in file names
When you use a spreadsheet column to name your output files—such as an InvoiceNumber or EmployeeName column—those values must be valid file names for your operating system.
Windows and macOS forbid characters like /, \, :, *, ?, ", <, >, and | in file names. If your customer name column includes a slash, such as "Smith/Jones Account", it will cause errors when the ZIP file is created and downloaded.
Review the column you intend to use for file names. Clean the values using Find and Replace to swap slashes or colons for dashes or spaces.
The pre-flight checklist
Before you generate a batch of two hundred files, run through this quick checklist to ensure your spreadsheet is ready:
- Row 1 contains only headers. No titles, no merged cells, no logos.
- Headers match placeholders exactly. Check for trailing spaces and casing.
- One row equals one document. Ensure data is flattened appropriately.
- Formulas are pasted as values. Lock in your arithmetic.
- Dates are formatted as text. Use the
=TEXT()function to freeze the display format. - Currencies are formatted as text. Freeze your symbols and decimal places.
- No forbidden characters in the naming column. Remove slashes and colons.
- Run a two-row test batch. Isolate the first two rows, generate them, and open the files to check formatting before running the full list.
Frequently asked questions
Does formatting in my spreadsheet carry over to the document?
No. Generators read the raw values stored in the cell, not the visual display formatting. If you want a date or currency to print exactly as it looks in Excel, convert it to text using a formula and paste it as values.
Why did my document generate with a blank space?
If a cell in your spreadsheet is empty, the corresponding placeholder in your template is replaced with nothing. Check your spreadsheet to ensure the row actually contains data, and verify the header name matches the placeholder exactly without trailing spaces.
Can I generate multiple line items per document?
No. The standard rule for bulk generation is one spreadsheet row equals one document. If you need ten line items on an invoice, you must create ten separate columns in that single row (e.g., Item1, Item2) and place ten placeholders in your template.
What happens if I have duplicate rows?
Identical rows will produce identical documents. ManyDocs detects completely identical rows and flags them with a warning before you click generate. You can either ignore the warning if the duplicates are intentional, or fix your spreadsheet.
Can I use a formula to name my output files?
Yes, but you must convert the formula result to static text first. Build your file names in a new column, copy the column, and use "Paste as Values". You can then select this column in the generator to name your output files.