Improving Excel spreadsheet performance

Excel is an extremely powerful tool containing many useful calculation functions. However, some of these functions consume a lot of CPU power, thus slowing down the performance of a dashboard.

If you are a user who works with a lot of complicated Excel reports, you will most likely experience that some Excel reports take a very long time to recalculate whenever any data is changed within any of the spreadsheets.

How to do it…

Here are some tips with workarounds to improve Excel performance:

  • Work from left to right: By default, Excel will first calculate expressions at the top-left corner of the spreadsheet and then continue to the right and downwards. Because of this, it is best to put expressions that are referencing to values in other cells to the right or to the bottom of those referenced cells. This is called forward referencing. With a small worksheet it won't make much of a difference, but for very large worksheets it will.
  • Avoid using volatile functions: Some examples of volatile functions are RAND(), RANDBETWEEN(), NOW(), TODAY(), OFFSET(), CELL(), and INDIRECT(). The issue with using volatile functions is that they will recalculate every time a change is made in a worksheet. For example, if you change cell A1 from X to Y, the RAND() function in cell A2 will recalculate and display another value. Note that some of these functions such as RANDBETWEEN() don't even work with SAP BusinessObjects Dashboards during runtime.
  • Avoid array formulas: Formulas such as SUMIF(), COUNTIF(), AVERAGEIF(), VLOOKUP, and HLOOKUP are memory hogs especially when the arrays are very large. If possible, replace SUMIF(), COUNTIF(), and AVERAGEIF() with regular formulas. VLOOKUP() and HLOOKUP() can be replaced with the Filtered rows component found in Chapter 3, From a Static to an Interactive Dashboard, or with a combination of MATCH() and INDEX().
  • Avoid giant formulas: Complex formulas that are very large should either be completed on the database side, or in a Web Intelligence (Webi) report (see the following recipe), or a BEx query when possible. This puts the load on the server, which has a lot more horsepower than the client machine.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset