How to change an Excel report template

In FBO One, reports are generated in Excel. It is possible to use an Excel template, if it has all the layout and headers you want. Although templates can be very complicated and use complex formulas, simple changes are usually not a problem. The only thing to remember is that the position where FBO One inserts the data is defined elsewhere, so inserting or deleting rows or columns may give strange results. Simple changes like column width, fontsize or type, alignments, header texts etc are not a problem.

Here is how to do it:

Make sure you use te correct version of Excel.

This is the oldest version that will be used to view the report. Also, make sure you use the same 'regional settings' as the actual users of the report, otherwise things like date & time formats may change (and break) unexpectedly. FBO One only can insert data in .xls files (Exel 97-2003 workbooks), not in the Excel 2007 .xlsx format or later. You can use newer versions of Excel creating the report, but make sure to save the final version of the document as .xls.

Check the name of the template

Find in Administration - Reports the name of the template document you want to change. In the example below we are looking for ContactMailingList

Select the template document

In Administration - template documents, find the template document and select it so it has a blue background. In the example below it is XXXX

Download the template & back it up

By using the Download button at the bottom of the page you can download the document to your machine. Of course its a good idea to make a second local copy, just in case the changes you're about to make don't work out as planned.

Open the document in Excel and make changes

Once again: simple changes like column settings and formats should not be a problem, but if you move the position of the data by inserting or deleting rows or columns you have to be carefull all Excel functions still work and change the report settings in Administration to the new position too. After the change, save the sheet als .xls file (Excel 97-2003 workbook)

Clear empty lines & move cursor home

Excel saves the size of the sheet by remembering which was the bottom-rightmost cell that was changed. You can easily find this location by pressing Ctrl-End. If this cell is too far out of the working area, delete the excess rows & columns manually. On save, Excel will then save only the real working area so the template will be smaller and faster.
Finally press Ctrl-Home to move the cursor to cell A1.

Upload the template back (and test it)

To upload the template, use the Update button at the bottom of the Template Documents screen, next to the download button we used before. Do NOT use the edit function, that won't allow you to update the document itself, just the metadata.

Finally, test the changes. If the result is not as expected, edit & upload the document again, or upload the backup copy you created before to return to the original situation.