Export Financial Statements to Excel
Print drivers in Sage 300 CRE export reports to Excel
by Kyle Ziegler, Sage Certified Senior Consultant
A handy feature of Sage 300 CRE is the ability to “print” (aka export) reports to Microsoft Excel. This feature is built right into the print drivers automatically installed with Sage 300 CRE on your server and workstations. However, printing reports – and especially financial statements – to Excel can be an ugly business when columns overlap or merge and totals end up in the wrong positions in the spreadsheet. The good news is that many reports can be modified to export more cleanly to an .xls file type, even when it comes to financial statements.
Modify your Financial Statements so that the columns export nicely into Excel:
- Open the financial statement in the Financial Statement Designer application.
- From the File menu, select Save as, enter a unique name for the design (such as “TBalAccr_Excel”) and click Save.
- Remove page breaks from the Financial Statement design.
- Modify the Header of the design to create a blank header.
- Modify the Footer of the design to create a blank footer.
- Optionally, modify the Body of the design to add 3 blank rows at the top.
- Insert the company name field or prefix group description field in the top blank line and center the instruction on the page.
- Enter the name of the statement (e.g. “Trial Balance”) in the second blank line and center on page.
- Enter desired text and/or the period ending date field in the third blank line and center on page.
- Format all numeric columns as follows:
- Under Print Symbols, select No symbols.
- Increase the Width a small amount (e.g. from 1.50 to 2.00). You may also need to increase the Start value to increase the space between columns.
- You may need to change the column Heading text to be on one line instead of multiple lines. Consider abbreviating the text to fit on one line.
- From the Design menu, select Design Options, and change the options as follows:
- On the General tab, unmark Repeat column headings.
- On the Amount tab, change the Print abnormal sign as to -# and unmark Print commas.
- Click OK to save the design options.
- From the File menu, select Save to save all changes to the new financial statement design.
Test the new “export to Excel” version of your financial statement design by beginning the print process, but mark the “print to file” checkbox in the Printer Setup window. When prompted, enter a destination for the file, enter a name for the file, and change the “Save as type” selection to Microsoft Office Excel Workbook (.xls). Exporting financial statements to Excel can cause lines to be deleted, so be sure to check this after testing your export.
If you would like help modifying your financial statement designs or other Sage 300 CRE reports to export to Excel, please contact ETHOSystems.