Excel: Insert a data table into an Excel template

Use this action to fill multiple cells in an Excel template with data from a table.

screen readers look here

Since this action adds the entire data table, use actions like Tables: Apply Filters and Tables: Create table for each unique value first to generate targeted, filtered tables to add to Excel spreadsheets.

Use case

Some departments or teams use Excel spreadsheets as templates: the template is reused with different data, so things are summarized or analyzed consistently. This action is great for interfacing with these types of Excel spreadsheets. You can also use this action to:

  • Add multiple pieces of information into a template at one time.
  • Comply with any company requirements to have data in spreadsheet form.
  • Insert different arrangements of data, form 1 column and 10 rows, to 50 columns and 50 rows.

How to configure this action

Because of how Excel handles inserted data, some Excel formatting and features may not be compatible with this action. Here is a list of some features that do or do not work in Excel:

  • Inserted data correctly inherits cell formatting, like bold, underline, text color, cell color, or cell width.
  • This action works on sheets that use charts, images, and page margins.
  • Excel files with hidden sheets and protected sheets are supported.
  • Inserted data can be referenced by formulas within the Excel file. See How to reference Catalytic data from Excel formulas
  • Excel cell formatting (e.g. currency, time, number) are unaffected by inserted data. For example. An Excel cell formatted to currency will not change if the value Apple is inserted into it.
  • If a data table with Excel formulas is inserted into Excel, the formulas will not work or evaluate. For example, a data table cell with the text value =SUM(B1:B2) is inserted into the Excel spreadsheet as if it was text, not a formula.
  • If the Excel template has merged cells, the columns in the data table that correspond to the merged Excel columns will not be inserted. The following illustration shows how this works:
    screen readers look here

How to reference Catalytic data from Excel formulas

You cannot insert data into a sheet and reference that data from the same sheet because of how Excel handles inserted data. The formulas must be on a separate sheet than the sheet where data is imported. For example, insert data into Sheet 1, then reference that from Sheet 2 with formulas like =Sheet1!B2+B1

screen readers look here

Fields for this action

  • Spreadsheet file

    • Name of field containing the Excel or CSV template in {{field-names}} format
  • Sheet

    • The worksheet name or number in the master spreadsheet to insert data into. Enter a number to select by worksheet order. If the sheet does not exist, it will be created.
    • By default, Excel sheet names are “Sheet1”, “Sheet2”, etc. Enter a number to select by worksheet order, the first sheet starts at 1.
  • Cell to begin copying into

    • Data from the inbound sheet will be copied into the master sheet, starting here. For example, A3, K1, or AA1.
      • If blank, defaults to first column and first empty row of the master sheet.
  • Data table

    • Select a table from a list of all tables available on your team. The list only includes tables you have permission to view.
      • You can also reference a table stored in a field. Change the left hand drop-down to Use table via field then select from any field that is part of the process. Learn more.
      • If necessary, you can enter the Table ID directly. Change the left hand drop-down to Use table by ID then enter the ID manually. Learn more.
  • Include header row

    • Select True or False from the drop down to set if the header should be included in the export.
      • Select True to include the header row in the export.
      • Select False or leave blank to ignore the header row and only export data rows.
  • Output field name

    • The name of the field in which the result should be saved.

What will this output?

This action creates a new XLSX file with data table data inserted. The new file is output to a field with the name defined in the New file name during configuration.

This action may generate multiple fields. To help keep output fields organized, the Output field name above will be added to the beginning of each of the output field names, separated by two dashes. Each field will result as:{{output-field-name--output-field}}.

Output fields for this action

  • Status field

    • This action will provide a status of the export.
  • Output field name

    • This action will provide the Excel file with the data table inserted.

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.

Why is my new Excel file strangely formatted?

Because of how Excel handles inserted data, some Excel formatting and features may not be compatible with this action. Here is a list of some features that do not work in Excel:

  • If a data table with Excel formulas is inserted into Excel, the formulas will not work or evaluate. For example, a data table cell with the text value =SUM(B1:B2) is inserted into the Excel spreadsheet as if it was text, not a formula.
  • If the Excel template has merged cells, the columns in the data table that correspond to the merged Excel columns will not be inserted. The following illustration shows how this works:

    screen readers look here

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.