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.
Here are some tips with workarounds to improve Excel performance:
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.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()
.