The powerful and flexible EBMS functions within an Excel spreadsheet allows the user to total a large number of records by using a single function. A single spreadsheet may contain many of these functions which may result in an enormous number of records that are accessed to complete the spreadsheet calculation. The following speed issues may result:
Consider some of the following suggestions to reduce potential performance issues:
Use a function once to calculate a specific amount: Place the results of a formula calculation into a spreadsheet cell then reference this cell when the same total is need as part of an equation. Do not nest a function that contains the same parameters as other functions within the same spreadsheet.
Limit using the Excel Add-in to total extremely large EBMS files such as the financial transaction file (GLT or ARINVDET) or other transaction file if summary files are available. For example, use the history tabs, invoice totals, and other EBMS summary files whenever possible.
Limit the number of users that launch the calculation of the Excel Add-in feature. This policy may improve speed and enhance security. Review the next step as an ideal way to reduce the number or users running EBMS functions.
Set the Calculation Options to Manual before distributing the information to other users. Reducing the number of times the spreadsheet is recalculated will reduce the amount of file access. Another option is to export the Excel spreadsheet to a format such as a PDF file before distribution.
Replace with the more robust enterprise level tool to analyze large amounts of data. Koble recommends that a business intelligence tool such as MS PowerBi in conjunction with the EBMS SQL Mirror option be used to remove the MS Excel performance limitations. Review SQL Mirror Overview for more details.