Skip to main content

Custom Table

Build pivot-style financial tables in Word from an Excel range, with categories and value columns that you define, and keep a live link back to the source workbook.

When To Use It​

  • You want a table whose rows and columns don't match a straight copy of the Excel range, for example grouped categories with subtotals.
  • You want to reuse the same table structure (categories, value columns, sorting) across different workbooks or reporting periods using a saved template.
  • You want the table to stay linked to Excel so it can be refreshed later, the same as Table Import.

Step-by-Step​

  1. Open your Word document where you will build the table.
    You can download Word test file.
    And Excel test file.
  2. Open the LedgerQ add-in pane, in Word ribbon.
  3. In the LedgerQ ribbon, Account section, choose Sign in/Out to Sign in with your Microsoft credentials.
  4. In the LedgerQ ribbon, Tools section, choose Custom Table.
  5. Source: choose Local file from your computer, or OneDrive to import from Microsoft cloud storage, then select an .xlsx/.xlsm Excel file.
  6. Sheet & Range: provide the Sheet name, the Start and End cell of the range (for example A1 and L22, including the header row), and an optional Table name.
  7. Values: add one or more value columns. For each, set the Column letter, number of Decimals, an optional header override, and whether to invert the sign of values.
  8. Categories: add one or more categories. For each, set the Column letter, whether it's Indented, and whether it Has total. The first category is the caption column. Use Sort options to control the order of rows per category (Sheet order, Alphabetical, or By column) and the caption row order.
  9. Generate: review the summary of File, Sheet, Range, Table name, Value columns and Categories, position your cursor where the table should be inserted, then click Generate table.
  10. Wait for the table to appear in Word.

Using Templates​

A template stores your value columns, categories and sort options so you can reuse the same table structure elsewhere. It does not store the source file, sheet or range, since those are specific to each workbook.

Save a template:

  1. After generating a table, click Save Template.
  2. Provide a Name and an optional Description, then save. The template is saved as a JSON file.

Load a template:

  1. Complete the Source and Sheet & Range steps as usual.
  2. On the Values step, click Load template instead and select a previously saved template file.
  3. LedgerQ fills in the Values and Categories steps from the template and jumps to Generate, ready for review.

Tables generated from a template show a Template badge in the Generated Tables list.

Refreshing Custom Tables​

If the Excel source data changes:

  1. Locate the table in the Generated Tables list in the add-in task pane.
  2. Click Refresh. If the source was OneDrive, LedgerQ re-downloads it automatically; if it was a local file, you'll be prompted to re-select the workbook.
  3. The table updates in place in Word, keeping its position and column widths.
info

After changing data in the Excel source file, always save the Excel file before clicking Refresh.

Editing and Removing Tables​

  • Click Edit on a table in the Generated Tables list to reload its configuration into the steps above and regenerate it.
  • Click Remove to delete the table from the Word document.
  • Click a table in the list to navigate to it in the document.

Microsoft OneDrive Support​

LedgerQ supports building custom tables from files in Microsoft OneDrive, as well as from a local folder.

Next​

Continue with Annotations to learn how to update financial statements from auditor comments.