---
title: "Using Excel Templates for Reports"
canonical: "https://docs.rubiconwater.com/space/RptNC/1979875352/Using%20Excel%20Templates%20for%20Reports"
format: markdown
---
> Macro (toc)
Generate report data directly to Excel templates to take advantage of the analytical features of Excel. The Rubicon report generator writes the report data to the first worksheet of the Excel template where it is accessible to your custom code and macros.
Any editable report, and some program reports, can be generated to an Excel template. Follow this process to generate report data to the first worksheet of the spreadsheet:
# Create a Report for an Excel Template
## Create the report
Create the report unless you are using a supplied report.
**Step 1**
Create a data-driven report in the Report Editor and bind the data columns to the report columns. You do not need to format the report table columns. See [Adding Data-Driven Components](https://rubiconwater.atlassian.net/wiki/spaces/RptNC/pages/1979548825) for details
**Step 2**
Turn off publication of additional report details. This is not required, but additional rows may be printed in your output if you do not:
- In the Report Properties screen, clear the **Show Input Parameters** check box.
- For data-driven components, open the component properties screen. On the **Table > Heading** tab, clear the **Display rowcount** check box
**Step 3**
**Save** and **Publish** the report. See [https://rubiconwater.atlassian.net/wiki/spaces/RptNC/pages/1987510996](https://rubiconwater.atlassian.net/wiki/spaces/RptNC/pages/1987510996)for details
## Create an Excel template
**Step 1**
Create an Excel file. You can choose whether to send the report data to the first worksheet in the report, or to send each report table to a separate worksheet.
**Step 2**
Leave the first row of each output worksheet blank. The report table headers will be written to this row when the report is generated.
> ℹ️ **Note**
> ℹ️
> ℹ️ If your report has a report header, ensure that the first cell of the first row is formatted as text. Alternatively, remove the report header in the Report Editor.
**Step 3**
On the second row, format one column for each report data column that will be generated. All columns should be formatted as *Text*, except for Date columns which should be formatted as *Date*.
**Step 4**
Enter sample data in each column in row 2 where the report data will be generated. This sample data will be overwritten by the report data when the report is generated.
> ℹ️ **Note**
> ℹ️
> ℹ️ Any formatting applied to this sample data will be used for the whole report table including the column headers.
> ℹ️
> ℹ️ Confirm that numerical values are formatted as text. A number that is formatted as text has a small green triangle in the top left corner of the Excel cell.
**Step 5**
Save the spreadsheet, in the *xlsx* format, to a location where it will be accessible to the report generator, such as on a shared network drive. The report generator must be able to locate the spreadsheet.
> ℹ️ **Note**
> ℹ️
> ℹ️ If you are *previewing* the report, the report generator will run on your local machine. If you are *running* the report, or scheduling the report to run the report generator will run on the server.
## Generate the report
**Step 1**
Double-click on the report icon in the application menu.
**Step 2**
In the **Generate As** field, choose Excel from template.
**Step 3**
In the **Template File** field, enter the location of the Excel spreadsheet.
**Step 4**
Select the Use Multiple Tabs check box if you have several report tables and you want to send each to a separate worksheet.
You can now **Run** or **Preview** the report. See [Previewing a Report](https://rubiconwater.atlassian.net/wiki/spaces/RptNC/pages/1980039172) and [Scheduling a Report](https://rubiconwater.atlassian.net/wiki/spaces/RptNC/pages/1987511013) for details.
---
# Customising the Excel Report Output
## Formatting
There are limited formatting options available for formatting the output report table. Typically you will manipulate the spreadsheet data using VBA code or Excel macros after the report is generated. However, the following formatting is applied:
- Any text formatting applied in row 2 of the Excel template, such as font, font colour, font size, font style and background colour, are used for all rows of the output table.
- Date formatting is applied to date rows.
- Number formatting applied to table columns in the Report Editor is copied to the report output.
## Customising with Excel macros and VBA
A typical application of this feature is to generate report data to a spreadsheet, then run analyses of the data using Excel analytical and automation tools. This allows you to generate more sophisticated reports and to use Excel features such as charting.
# Troubleshooting Excel Templates
**I get an error message "Cannot format into a Number, please check column"**
Ensure that you have applied text formatting to the Excel cell in the specified column in row 2 of the spreadsheet. If the cell contained data, you may need to delete it and re-enter it after you apply the text formatting.
**I get an error message "The cell at row 2, column in the template cannot be empty, please check."**
Ensure that you have put sample data in each column of row 2 where report data will be generated.
**I get an error message saying that the template does not exist**
Ensure that your template is accessible from wherever you are running the report.
**I applied formatting to the header row of the spreadsheet, but it isn't showing up**
Only the formatting in row 2 of the spreadsheet is used in the generated report
**I applied formatting to the report table in the Report Editor, but it isn't showing up**
Formatting applied to numbers in the report table will be applied in the Excel output. Text formatting in the report table is not used in the Excel report output, however any formatting that you apply to the report table will be used in any other output format.
---