Columns are not aligning when I export my report to Excel.
Description

If you have problems with blank lines in the text file where there are page breaks, try the following options:

  • Change the unprintable area setting on your printer to zero
  • Change the margins in your positive pay report to zero (File, Page Setup, Margins)
  • Verify that the Use form feeds check box is cleared (File, Page Setup, Print Options)
  • Set up a printer object that uses the Generic/Text Only Windows printer driver. Print to file with that printer object
  • Import the text file into Excel, sort by date, and then save it. This moves blank lines to the bottom of the spreadsheet
Cause

Reports may not align properly when you export them to Excel due to their formatting.

Resolution

[BCB:5:Third-party support:ECB]



When designing reports and financial statements for export to Excel, follow these guidelines:

  • Reports and financial statements must be in a columnar format with adequate space for each column
  • Reports and financial statements should not contain headers and footers
  • If you want Excel to recognize numbers, include decimals and represent negative numbers with a negative sign to the left of the number
  • Reports and financial statements cannot print dollar signs and asterisks next to numbers
  • Key changes: remove headers, define print options and set single line spacing

 

Formatting reports for export to Excel:

  1. Open the report design in Report Designer by going to File, Open Design.
  2. From the File menu, select Save Design As and in the pop up window under File Name - add "to xls" at the end before the ".rpt". For Example "Job List to xls.rpt".
  3. Click Save.
  4. Remove Margins:
    1. From the File menu, select Page Setup, Margins.
    2. Clear the Use system default check box, and then click OK.
    3. From the File menu select Page Setup, Page Headings.
    4. Clear the Use page heading check box, and then click OK.
    5. Using the mouse, point to in the page header.
    6. Locate the dashed line across the header with two black triangular handles on either side of it.
    7. Click on one of the black triangular handles and drag the line to the top of the page.
    8. From the View menu, click Header to clear the check mark and remove the Header from the report design.
  5. Remove Column Headers:
    1. From the Format menu, confirm that Use Column Headings is not selected. Optional: If any column headers remain, highlight and click delete. Column headers can cause columns to merge or add additional columns.
      Note: If setting up design to print labels, select the fields you want to appear on the labels, each separated by a semicolon (;). All the fields should appear on one line and there should be no column headings.
  6. Set number format:
    1. From the File menu, select Page Setup, Print Options.
    2. In the Print Negative Numbers As area, select -999, and then click OK.
  7. Set line spacing:
    1. From the Design menu, select Line Spacing, Single, and then click OK.
  8. Remove Totals:
    1. From the Design menu, select Totals and clear any total check boxes, and then click OK.
  9. Save the modified design.
  10. Add the new report to the application report menu. See How do I save, add, and print a report design?

Notes:

  • If columns merge, you may need to add space between those two fields in the report design
  • In some cases, the report will need to be modified so all fields are on one line on the design
  • Print Controls may also need to be modified
  • Column headers can cause columns to merge or add additional columns. Removing the column headers from the report removes this issue
  • Total lines will not recalculate but are stagnant numbers. Removing the total lines will then allow for easy addition of total lines in Excel
Steps to duplicate
Related Solutions

Who do I contact about creating or changing custom reports, direct deposit files, inquiries, and invoice forms?