Trial balance reports are dynamically linked to the trial balance and when refreshed, reflect the changes that occur in the trial balance.

Trial balance reports are displayed in Microsoft® Excel®, but they are not standard Microsoft® Excel® workpapers. Some reports in the binder might be in the older Microsoft® Excel® XLS file format, while other reports might be in the newer Microsoft® Excel® XLSX file format. This article discusses limitations and best practices for both Microsoft® Excel® file type reports.

Contents

• How can there be different Microsoft® Excel® file type reports in the binder?
• How do I know if the report is a XLSX report?
• What areas of the report are eligible for annotations including tickmarks, WP references, notes, comments, and manually entered numeric values?
• What changes can I make in a trial balance report?
• When will annotations not be retained in the report?
• New features are provided with reports created in v7.5 and newer versions.
• Functions that are not available or have limited functionality in newer XLSX reports created in v7.5 and newer versions.

　
How can there be different Microsoft® Excel® file type reports in the binder?

• Older XLS file type. These are non-fund trial balance reports created in a release of Engagement or Workpaper Manager prior to the 7.5 release. These reports can continue to be used in post-7.5 releases with virtually no difference in functionality.
• Newer XLSX file type. These are any and all trial balance reports created in 7.5 or later, or fund trial balance reports created in Engagement or Workpaper Manager 7.0 or later.
  • Note: In addition to using the more advanced XLSX file format, reports created in 7.5 or later refresh in less time than reports created in prior releases. These reports also feature protection on various cells in order to prevent data corruption. To take advantage of the full enhanced reporting capability with the XLSX reports, be sure the most recent 7.5 or newer version of Engagement or Workpaper Manager is installed and then create a new report.

  How do I know if the report is a XLSX report?
To determine if a report is in the older XLS or the newer XLSX file format, do the following:

1. Open the report.
2. Click on the Engagement ribbon.
3. Click Properties.
4. If the Settings check boxes are in the bottom right corner of the Trial Balance Report Properties dialog, the report is in the older XLS file format.
   • If the Settings check boxes are in a separate Settings tab in the dialog, along with multiple tabs for Groups, Funds, and Format Columns, the report is in the newer XLSX file format.

  What areas of the report are eligible for annotations including tickmarks, WP references, notes, comments, and manually entered numeric values?

• Spacer columns in the system-calculated balance area: Unhidden, empty columns, with no header descriptions that are provided after each system-calculated balance column.
• Blank columns and rows outside of the system-calculated balance area.
  • Newer XLSX reports. Annotations can be entered in the first 50 columns immediately to the right of the last system-calculated column and in the first 100 rows immediately below the last system-calculated total of the trial balance report.
  • Older XLS reports. Annotations can be entered in any cell of the columns that are to the right of the last system-calculated column and in any row below the last system-calculated total.
• Refrain from using the following characters at the beginning of any notes or comments:
  • =, +, -. >, <
  • These are Microsoft® Excel® operators and can cause Microsoft® Excel® to try and calculate the contents of the cell instead of reading them as text. 

  What changes can I make in a trial balance report?

• Inserting tickmarks.
  • Best practice. In the v7.5 release, tickmarks should be inserted in the cells of the spacer column and any other eligible column cells. As of the v2017.1.1 release, tickmarks can be entered in any cell.
  • Restrictions.
    • v7.5 or XLSX reports. Tickmarks are not allowed in calculated column cells but can be entered in any other eligible column cells.
    • v2017.1.1 and newer reports. Tickmarks can be entered in both calculated and eligible non-calculated column cells; however, tickmarks in calculated cells may occasionally be removed when the report is refreshed. 
    • Older XLS reports. Tickmarks can be entered in both calculated and eligible non-calculated column cells; however, tickmarks in calculated column cells may occasionally be removed when the report is refreshed.
• Inserting WP references.
  • Best practice. WP references should be inserted in the spacer column and any other eligible column cells.
  • Restrictions.
    • All reports. WP references are not allowed to be inserted in calculated column cells, but they can be inserted in any other eligible column cells.
• Entering text for notes, comments or descriptions and manually entering numeric values.
  • Best practice. Text, manually entered numeric values and any other annotations should be entered in spacer column and any other eligible column cells.
  • Restrictions.
    • v7.5 or XLSX reports. Text and numeric values are not allowed to be entered in calculated column cells but can be entered in the spacer column and any other eligible column cells. Inserting a text box for notes or comments is also not supported. 
    • v2017.1.1 and newer reports. Inserting or using a text box for notes or comments is supported. 
    • Older XLS reports. Text and numeric values can be entered in both calculated and eligible non-calculated column cells; however, text and manually entered numeric values in calculated column cells will be removed when the report is refreshed.
• Entering threshold percentage or dollar amount for variance columns.
  • v7.5, XLSX, and newer reports. Variance thresholds are no longer entered directly in the report. Follow the instructions in the "New features are provided with reports created in v7.5" section below to define variance thresholds.
  • Older XLS reports. The percentage or dollar amount variance threshold must be manually entered in the first blank row cell just below the variance column heading. On the Engagement ribbon, click the Refresh command to dynamically update the values in the variance column based on the threshold entered.
• Using the Microsoft® Excel® Font features such as highlighting, bolding and italic, or other formatting features.
  • Best practice. Formatting changes can be made in any system-calculated cell and in any other eligible cells.
• Using the Microsoft® Excel® Zoom feature.
  • Best practice. Change the Zoom setting after refreshing the report. 
  • Restrictions.
    • v7.5 or XLSX reports. Changing the Zoom setting is not retained when the report is refreshed. 
    • v2017.1.1 and newer reports. Changing the Zoom setting is retained when the report is refreshed.
• Using the Microsoft® Excel® Expand or Collapse rows feature for detail reports.
  • Best practice. Expand or collapse the journal entry detail rows after refreshing the report.
  • Restrictions.
    • v7.5 or XLSX reports. Expanding or collapsing the journal entry detail rows is not retained when the report is refreshed.
    • 2017.1.1 and newer reports. Expanding or collapsing the journal entry detail rows is retained when the report is refreshed.
• Adjusting column widths and row heights.
  • v7.5 or XLSX reports. The width of columns and height of rows cannot be adjusted.
  • v2017.1.1 and newer reports. The width of columns and height of rows can be adjusted.
• Using the Microsoft® Excel® Hide/Unhide or Insert/Delete feature for rows and columns.
  • Best practice. To hide/unhide or insert/delete system-calculated rows and columns, refer to the options in the Settings and Format Columns features of the Create Trial Balance Report or Trial Balance Report Properties dialogs and adjust the content of the report.
  • Restrictions.
    • v7.5 and newer XLSX reports. Using the hide/unhide or insert/delete feature is not allowed so that the report does not become corrupt when it is refreshed.
    • Older XLS reports. Do not hide or unhide rows or columns because those settings may not be retained or the report may become corrupt when it is refreshed. Do not insert or delete rows because the report may become corrupt when it is refreshed. Columns can be inserted or deleted, but never delete a column unless it was manually added.

When will annotations not be retained in the report?

There are some situations where annotations (for example, tickmarks, WP references, text, formatting, etc.) made on rows of the report will currently not be retained for the new XLSX reports and may sometimes not be retained for older XLS reports. For fund trial balance reports, the annotations on the fund and account detail rows of the report associated with the affected group or subgroup will also not be retained. The following changes will cause annotations to be removed.

• Changing the account class of a group or subgroup that is in the report.
• Changing the abbreviation of an account class that is assigned to a group or subgroup in the report.
• Moving a subgroup that is in the report to a different group.
• Creating a group from a subgroup that is in the report.

Best practice. Ensure that all account grouping work is completed within the account grouping list prior to annotating any related trial balance reports. If it is necessary to make changes to the account grouping list and you want to be able to recreate the annotations, select the Publish Workpaper command with the report selected in the binder window prior to making the changes. After making the account grouping changes, use Copy & Paste to transfer the annotations from the published workpaper to the updated trial balance report.

New features are provided with reports created in v7.5 and newer versions.

• When creating or updating an analytical report with $ or % variances, variance thresholds must be entered in the Variance properties section of the Format Columns tab of the Create Trial Balance Report or Trial Balance Report Properties dialog.
  • Benefit. This allows for the variance threshold to be set up at the time the report is created instead of entering it within the report itself after it is created and refreshed.
• The abbreviation for variance columns can be customized in the Format Columns tab of the Create Trial Balance Report or Trial Balance Report Properties dialogs by clicking in the Abbrev column and editing the text.
  • Benefit. The label of the column can be used to meaningfully define the purpose of the variance.
• In the Settings tab of the Create Trial Balance Report or Trial Balance Report Properties dialogs, the option to Print as black and white is changed to Do not print background colors. When this option is selected, no background color prints in any cell, including column header cells.
  • Benefit. This allows for less use of printer toner and ink when printing reports. If you want to include background colors in column headers, but you want to print the report as black and white, do not select the "Do not print background colors" option, and use the Microsoft® Excel® Print dialog settings.

Functions that are not available or have limited functionality in XLSX reports created in v7.5 and newer versions.

There are a few Microsoft® Excel® functions that are not available at this time with newer XLSX reports.

• Using the Microsoft® Excel® Spelling feature is not currently allowed.
  • Work Around. The report can be published to verify the spelling in the report using the Microsoft® Excel® Spelling feature.
• Using the Microsoft® Excel® AutoSum feature is allowed, but you cannot click the AutoSum command directly.
  • Work Around. On the Home or Formulas ribbon, select More Functions… from the AutoSum lookup menu, or on the Formulas ribbon, select Insert Function.
• Using Merge Cells or Merge and Center is not currently allowed.
  • Work Around. The report can be published and then the numbers replaced with TB links to the Trial Balance. Then the new Excels file can be edited however is required.