--- title: "Build a document template" slug: "xl-docs-build-a-template" updated: 2026-10-09T18:08:35Z published: 2026-10-09T18:08:35Z canonical: "help.quickbase.com/xl-docs-build-a-template" --- > ## Documentation Index > Fetch the complete documentation index at: https://help.quickbase.com/llms.txt > Use this file to discover all available pages before exploring further. # Build a document template An XL Docs document starts from a template: an Excel workbook that you design, stored on a record in your app's **Document Templates** table. When someone selects the XL Docs button on a record, XL Docs opens a copy of the workbook, writes that record's field values into the cells you mapped, adds any lists of related records, and saves the result as an Excel, PDF, or CSV file. This article walks through building a template end to end: the workbook, the Document Templates record, mapping fields to cells (the **clist**), choosing the sheet, choosing PDF pages, adding images and rich text, and building a CSV export. Lists of related records, such as line items, are covered in [Data tables (subtables)](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables). > [!NOTE] > Note > > The XL Docs Configurator is the easier way to do everything in this article. You pick a table, type a cell position next to each field, and save; the Configurator writes the clist and the other settings for you. See [Configure a template](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-configurator-configure-a-template). This article explains what the Configurator writes, so you can read, check, or fine-tune a template by hand. ## How it fits together - **Master table**: the table whose records you generate documents for, such as Projects, Orders, or Invoices. The XL Docs button lives here. - **Template workbook**: the Excel file with your layout, attached to a Document Templates record. - **Document Templates record**: the template file plus the **clist**, which maps master-record fields to cells, and settings such as **Excel Sheet #** and **PDF Pages**. Every field is described in [Document Templates table reference](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-templates-table). - **Document Subtable Excel records** (optional): one per list of related records to include. See [Data tables (subtables)](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables). - **The XL Docs button**: a formula field on the master table that tells XL Docs which template to use, which record to process, and where to put the result. See [Formula URL parameters](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-url-parameters) and [Output and storage](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-output-and-storage). Building a template by hand uses field IDs and table IDs from your app. If you don't know where to find them, see [Find your Client ID, table IDs, and field IDs](https://help.quickbase.com/quickbase-extensions/docs/en/find-client-id-table-ids-field-ids). ## Step 1: Create the template workbook Design the workbook in Excel so it looks the way the finished document should look. Then save it in one of these formats: - `.xlsx`: Excel workbook. - `.xlsm`: Excel macro-enabled workbook. You can use an .xlsm template, but not all macros are supported, so some macros may not work. - `.xls`: Excel 97-2003 workbook. No other file type is accepted, including Word documents. XL Docs reports *This version of Excel Generator only supports .xls, .xlsx, or .xlsm template files.* if the attached file is anything else. By default the generated document has the same file type as the template; you can also produce a PDF or CSV (see [Output and storage](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-output-and-storage)). What you can put in the workbook: - **Static content**: logos, images, headings, labels, borders, colors, and fonts. Anything you don't map stays exactly as you designed it. - **Placeholder text** such as *[Name]* or *[Company Name]* to help you lay out the sheet. It's optional; mapped cells are overwritten with the field values. - **Formulas, formatting, charts, and conditional formatting**. XL Docs writes values into the cells in place, so these are kept and recalculate with the new values (see [Formulas and Excel features](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#formulas)). - **Several sheets (tabs)**. You choose which sheet each set of data goes on. Note the cell address (such as `B12`) of every place where data should go. You'll use these addresses in the clist. ## Step 2: Create the Document Templates record 1. Go to the **Document Templates** table in your app. 2. Add a new record and enter a **Name**, for example *Employee Expense Template*. 3. In **Excel Template File**, select **Choose File** and upload your workbook. 4. Save the record. The **XL Docs Configurator** button appears on the saved record. 5. Either select **XL Docs Configurator** to map the fields (recommended), or fill in **clist**, **Master table name**, **Master table dbid**, **Excel Sheet #**, and **PDF Pages** yourself as described below. **Master table dbid** is the master table's table ID (see [Find your Client ID, table IDs, and field IDs](https://help.quickbase.com/quickbase-extensions/docs/en/find-client-id-table-ids-field-ids)). To change the layout later, edit the workbook and upload it to the same record again. The mapping stays as long as the cell addresses don't change. ## Step 3: Map fields to cells (clist) The **clist** (column list) field on the Document Templates record says which field of the master record goes into which cell. The same syntax is used in the clist of a Document Subtable Excel record, where it gives the starting row of a list of records. ### Format A clist is a list of entries separated by periods (`.`). Each entry joins a cell and a field ID with a colon: ``` Cell[-N|D|I|H|F]:FID[:Height|Width] ``` - **Cell**: the Excel cell, such as `A1`, `B5`, or `C10`. - **-N, -D, -I, -H, -F** (optional): a type suffix directly after the cell, described below. - **FID**: the field ID of the Quickbase field to put in that cell. To look up a field ID, see [Find your Client ID, table IDs, and field IDs](https://help.quickbase.com/quickbase-extensions/docs/en/find-client-id-table-ids-field-ids). - **Height|Width** (optional, images only): the image size in pixels. For example, this clist puts four fields into cells B12 through B15: ``` B12:53.B13:17.B14:49.B15:54 ``` XL Docs reads the clist without regard to case. Don't put spaces in the clist. To put the same field in two cells, add two entries, for example `B6:6.E10:6`. (In the Configurator you can type `B6,E10` in **Cell Position** and it writes both entries.) ### Type suffixes | **Suffix** | **Configurator Type** | **What XL Docs writes** | **Example** | | --- | --- | --- | --- | | (none) | Text | The field's value as text. | `B6:6` | | `-N` | Numeric | A number, so it right-justifies and follows the cell's number format (currency, decimals, and so on) and works in formulas. A leading `$` is removed. If the cell is formatted as a percentage, the value is divided by 100 so that, for example, 25 shows as 25%. A value that isn't a number is written as text. Use it for numeric, currency, percent, and rating fields. | `C22-N:6.D22-N:7` | | `-D` | Date | A date value, so Excel can format and calculate with it. Use it when the template cell isn't already formatted as a date. Put `-D` after the cell, not in front of the field ID. | `B2-D:6`, `A12-D:6` | | `-I` | Image | An image placed at the cell, optionally resized. See [Images](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#images). | `E22-I:21:20\|20` | | `-H` | HTML Text | Rich text or an HTML table converted into Excel formatting. See [Rich text and HTML](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#html). | `A5-H:12` | | `-F` | (none; type it in the clist) | An Excel formula, if the field's value contains `(` and `)`, for example a formula text field that returns `=SUM(B2:B10)`. Otherwise the value is written as text. | `F30-F:88` | > [!NOTE] > Note > > Some older configurations write a date as `A12-D:D6` or `A12:D6`. XL Docs ignores a `D` in front of the field ID, so `A12-D:D6` works the same as `A12-D:6`, but `A12:D6` is a plain value with no date handling. Use `A12-D:6`. ### Variables and column-letter references An entry whose cell part has letters but no row number isn't written to a cell. Instead, it holds the field's value as a variable that data-table queries can use. Examples: - `RK:3` makes the master record's Record ID# (field 3) available as `[RK]` ("related key"). - `AA:7.BB:10` make fields 7 and 10 available as `[AA]` and `[BB]`. - `A:12` makes field 12 available as `[A]`. The Configurator adds column-letter entries like this automatically when a data-table query compares against a master field ("the value in the field (parent)"). A Document Subtable Excel record can then use the variable in its query, for example `{'12'.EX.'[RK]'}` to find the records whose field 12 (say, *Related Order*) equals the master record's Record ID#. Add variables at the end of the clist. See [Data tables: filter the records](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables#filter). ## Step 4: Choose the sheet (Excel Sheet #) **Excel Sheet #** is the sheet the template clist writes to. It's positional: `1` is the first tab from the left, `2` the second, regardless of the tab names. For example, if the first tab is named *Expense statement*, it's still sheet 1. If the field is blank, XL Docs uses the first sheet. To put master-record data on more than one sheet, add a Document Subtable Excel record for each extra sheet (see [Which records do I need?](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-templates-table#which-records-do-i-need)). ## Step 5: Choose the PDF pages (optional) When the XL Docs button creates a PDF (`docfmt=pdf`), every sheet of the generated workbook becomes part of the PDF unless you set **PDF Pages**. Enter the sheet numbers to include, counted from the left starting at 1: - `1,3,5`: sheets 1, 3, and 5. - `1-3`: sheets 1 through 3. - `1-5,8,10-12`: sheets 1 through 5, then 8, then 10 through 12, as consecutive pages of the PDF. XL Docs sorts the list and ignores duplicates. Listing more sheets than the workbook has stops generation with *Something's wrong with the pages you selected. You entered more than the sheets you have in your template.* In the PDF, each sheet's columns are fitted to the page width, and charts are included as images. ## Images To place an image, use the `-I` suffix. The field's value can be: - a **file attachment** field in any Quickbase table (for example a logo, photo, or signature), including a lookup of one; - a **URL** to an image on a web server, such as a formula URL field that returns a QR code image; - a formula field that returns an `<img>` tag. For the last option, create a Formula - Text field with **Allow some HTML tags** checked, and return the image tag without `height` or `width` attributes. For example, this shows a green circle from the Quickbase image library: ``` "
" ``` **Image size.** Add the size after the field ID as `:Height|Width` in pixels, height first. Leave one side blank to keep the image's proportions: - `E22-I:21:20|20`: field 21 at E22, 20 pixels high and 20 wide. - `M12-I:275:200|200`: field 275 at M12, 200 by 200. - `B2-I:15:200|`: field 15 at B2, 200 pixels high, width in proportion. - `B2-I:63`: field 63 at B2, original size. In the Configurator, set the field's **Type** to **Image** on the Fields tab. The field then appears on the **Images** tab, where you enter **Width (px or Auto)** and **Height (px or Auto)**. Leave a side blank or *Auto* for default sizing. Images in data tables work the same way: each record's image is placed in its own row. If an image in the template clist can't be loaded, the cell shows *Image Error*. For an optional image such as a signature, see [Tips and examples](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-tips-and-examples). ## Rich text and HTML A field that holds HTML, such as a Rich Text field or a Formula - Rich Text field, can be written with its formatting by using the `-H` suffix in the clist of the Document Templates or Document Subtable Excel record: ``` {ColumnLetterRowNumber}-H:FieldID ``` For example, to show field 12 in cell A5 with its formatting, use `A5-H:12`. The HTML is converted into formatted text in the Excel or PDF output. Supported tags: - `<p></p>`: paragraph. - `<font></font>`: font, color, and size of text. - `<h1>` through `<h6>`: headings. - `<a href></a>`: hyperlink. - `<i></i>`: italic. - `<u></u>`: underline. - `<b></b>`: bold. - `<strong></strong>`: important (bold) text. - `<sub></sub>`: subscript. - `<sup></sup>`: superscript. - `<br>`: line break. - `<strike></strike>`: strikethrough. Supported styling: the `color`, `font-family`, `text-decoration`, and `font-size` style properties, and the `size`, `face`, and `color` attributes of `<font>`. XL Docs removes `<div>` and `<em>` tags before converting, so use `<p>` and `<i>` instead. For example, in a Formula - Rich Text field: ``` "some text goes here" ``` **HTML tables.** If the value is an HTML `<table>`, XL Docs writes it as a grid of cells starting at the mapped cell, one table row per sheet row. In a data table, each record's table goes in its own rows, and rows are inserted if a table is longer than **Max Line items**. If the HTML can't be converted, the raw text is written instead. In the Configurator, choose the Type **HTML Text**. ## Formulas and Excel features XL Docs fills a copy of your workbook cell by cell, so the rest of the workbook works as designed: - **Formulas** in the template are kept and calculate from the values XL Docs writes. For example, a TOTAL cell with `=SUM(D12:D21)` totals the line items. Map numbers with `-N` so formulas treat them as numbers. - **Formatting, merged cells, conditional formatting, and charts** are kept. - **Pivot tables** on the sheets XL Docs writes to are set to refresh when the file is opened. - **Macros**: save the template as `.xlsm` to keep them; the output is `.xlsm` too. Not all macros are supported, so some macros may not work. Test the generated file before you rely on a macro. - **Subtotal and average rows** that XL Docs adds to data tables are real `=SUM()` and average formulas (see [Data tables: subtotals](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables#subtotals)). CSV output holds values only; formulas don't carry over into a CSV file. ## Create a CSV export file A popular use of XL Docs is to create an export file in comma-separated value (CSV) format to exchange with partners or other software systems. Export formats often need more than detail rows: for example one or more header rows, the detail records, and a footer row with totals, all generated on the fly. You build this the same way as any template, then set the button's `docfmt` to `csv`. ### Example: weekly time sheet export This export file has two header rows, one detail row per time sheet, and a footer: | **Row** | **Column A** | **Column B** | **Column C** | **Columns D–J** | | --- | --- | --- | --- | --- | | 1 (header 1) | TOTAL NUMBER TIME SHEETS: 26 | | | | | 2 (header 2) | EMPLOYEE NAME | PROJECT NAME | TASK NAME | The seven dates of the week, 1/10/2024 through 1/16/2024 | | 3–28 (details) | Evelyn Morales | Test Project ABC | Preparations for Project | Hours for each day | | 29 (footer) | TOTAL HOURS: 483 | | | | The data comes from a Projects table with child tables: Projects → Project Employees → Weekly Time Sheets → Time Cards. Time cards are summed by day onto each weekly time sheet, and summary fields on Projects provide the header and footer totals. 1. **Add the document template.** Add a Document Templates record whose template file is a blank Excel workbook. Set the master table to **Projects**. No master fields need cell positions (in the example the clist holds only the variable `C:3`). 2. **Add one data table per kind of row**, all on sheet 1, using **Tab sub report order** 01, 02, 03, and 04 so they stack in that order: | **Tab sub report order** | **Table** | **Description** | **clist** | **Returns** | | --- | --- | --- | --- | --- | | 01 | Projects | Header: total time sheets | `A1:255` (a summary/formula field that returns the text and count) | The one project record | | 02 | Weekly Time Sheets | Column headers | `A2:257.B2:258.C2:259.D2-D:104.E2-D:126.F2-D:127.G2-D:128.H2-D:129.I2-D:130`… (static header-text fields and the week's dates) | One record | | 03 | Weekly Time Sheets | Time sheet entries | `A3:25.B3:24.C3:16.D3-N:37.E3-N:38.F3-N:39.G3-N:40.H3-N:41.I3-N:42`… (employee, project, task, hours per day) | Every time sheet for the project | | 04 | Projects | Footer | `A4:256` (a summary/formula field for TOTAL HOURS) | The one project record | Even though Projects is the master table, the header and footer rows are queried as data tables of the Projects table (filtered to the current project) so that they're placed as rows in the stack. 3. **Start each data table on the next row.** The data tables start in rows 1, 2, 3, and 4. Data table 03 returns a varying number of rows, but you still point 04 at row 4: because of the Tab sub report order, XL Docs places the footer right after the last detail row (row 29 in the example). See [Stack data tables](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables#tab-sub-report-order). 4. **Create the button** on the Projects table with `docfmt=csv`. The CSV is written with commas between values and includes a UTF-8 byte order mark (BOM) unless you add `noutf8bom=y`, which some importing systems need. Use one sheet for a CSV export. This button (a Formula - Text field with **Allow some HTML tags** turned on) saves the CSV into a file attachment field on the project, using the progress-window version of XL Docs (`ExcelGen_LP.aspx`) with `stream=n`: ``` var Text url = "https://.../ExcelGen_LP.aspx?"  // your XL Docs server URL, created by the installation wizard & "clientid=Q#" & "&appid=" & AppID() & "&dbid=" & Dbid() & "&tpdbid=" & [_DBID_DOCUMENT_TEMPLATES] & "&tpid=1"  // Record ID# of your template & "&fn=" & URLEncode("Weekly Time Sheets extract") & "&msid=" & [Record ID#] & "&faid=264"  // your file attachment field ID & "&docfmt=csv" & "&stream=n" & "&apptoken=[Your App Token]"; "Create Time Sheets CSV" ``` To download the CSV in the browser instead, use the stream-to-browser version of XL Docs (`ExcelGen_UA.aspx`) with `stream=y`; XL Docs returns a real CSV file. All parameters are described in [Formula URL parameters](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-url-parameters). ## Worked example: invoice address block An invoice template has a *TO* block in cells B12 through B15 with placeholders. The template record's clist is: ``` B12:53.B13:17.B14:49.B15:54 ``` | **Cell** | **Field ID** | **Placeholder in the template** | **Generated value** | | --- | --- | --- | --- | | B12 | 53 | [Name] | Jackie Johanson | | B13 | 17 | [Company Name] | Main Street Inc. | | B14 | 49 | [Street Address] | 100 Myrtle Avenue | | B15 | 54 | [City, ST ZIP Code] | New York, 11201 | The logo, the word *INVOICE*, and all the bold labels are static content in the template file itself. ## Worked example: expense statement An *Employee Expense Template* uses the Projects table as its master table and sheet 1 (named *Expense statement*) of *Employee Expenses v4.xlsx*. Its clist is: ``` B6:6.B7:7.B8:8.K6:104.K7:105.M12-I:275:200|200.A38-H:276.RK:3.AA:7.BB:10 ``` | **Entry** | **Field** | **Configurator Type** | **Result** | | --- | --- | --- | --- | | `B6:6` | 6 Project Name | Text | Project name in B6 | | `B7:7` | 7 Project Number | Text | Project number in B7 | | `B8:8` | 8 Project Location | Text | Location in B8 | | `K6:104` | 104 Est Start Date | Text (the cell is formatted as a date in the template; use `K6-D:104` if it isn't) | Period "From" in K6 | | `K7:105` | 105 Est End Date | Text | Period "To" in K7 | | `M12-I:275:200\|200` | 275 QR Code (formula URL to a QR code image) | Image, Width 200, Height 200 | 200 × 200 QR code at M12 | | `A38-H:276` | 276 Html sample | HTML Text | Formatted rich text at A38 | | `RK:3`, `AA:7`, `BB:10` | 3 Record ID#, 7, 10 | (variables) | Not written to cells; used as `[RK]`, `[AA]`, `[BB]` in data-table queries | The expense lines in rows 12 and below come from a data table on the Expense Sheets table; that part is shown in [Data tables (subtables)](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables#worked-example). ## Next steps - Add lists of related records: [Data tables (subtables)](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-data-tables). - Create the button and choose the output: [Formula URL parameters](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-url-parameters) and [Output and storage](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-output-and-storage). - Field-by-field details: [Document Templates table reference](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-templates-table). - If something doesn't come out as expected: [XL Docs messages and troubleshooting](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-messages).