| Feedback | Index() | Address() |

Performance issues with large spreadsheets

As spreadsheets become larger and more complex with thousands of formulas they can start to slow down. A noticeable lag can develop during which the document recalculates.

There is an option to turn off the automatic recalculation but this is inconvenient and poses a risk if a users reads data which has not been refreshed. Place an eye-catching warning notice on your spreadsheet if it is to be left in a state requiring Manual recalculation.

Ribbon option: File | Options | Formulas | Calculation Options. For normal use choose either Automatic or Manual. (The 'Automatic except for data tables' specifically relates to 'What-If' data analysis tools.) A spreadsheet in 'Manual' recalculate mode can be updated by pressing the <F9> key.

This page lists various design options which may be considered when developing large models.

Ensure that the base data has been carefully designed and possibly pre-processed in other programs such as Access before being put into your spreadsheet. Lookups and linkages between different tables of data can be performed before the data is transferred. Macros in Access and Excel can automate the transfer of data between programs to avoid the need for copying and pasting.

Volatile functions

When you enter data and amend a spreadsheet, Excel is smart enough to distinguish between cells which are affected / unaffected by the change. Only cells which are dependent on the changed data will be recalculated. Some functions however do not make this distinction and will recalculate after every change. OFFSET() and CELL() are two commonly used examples. It may be possible to use alternative functions which avoid the processing overhead.

OFFSET() itself may not be slower than alternative formulas constructed around functions such as INDEX(). It is the repeated recalculation which can cause performance issues.

Summarising a large table using INDEX

The table above summarises thousands of records of temporary staff bookings and shows the reasons the workers were required.

OFFSET() can be used to read monthly data from a column, dependent on the month number in cell AG17.

[AG20] =SUMPRODUCT((OFFSET($F$2,0,$AG$17,37999,1))*($R$2:$R$38000=$AF20)*($M$2:$M$38000=AG$18))

INDEX() will avoid repeated recalculations when unrelated changes are made to other parts of the spreadsheet.

[AG20] =SUMPRODUCT((INDEX($F$2:$H$38000,,$AG$17))*($R$2:$R$38000=$AF20)*($M$2:$M$38000=AG$18))

The formulas read 38,000 rows. In this model that was considered to be the highest possible number of records and the actual number maybe far fewer.

A formula which reads the actual size of the table will be more efficient. A dynamic name range can be defined using a combination of ADDRESS(), COUNT() and INDIRECT().

Creating a dynamic range

Once a dynamic named range (e.g. 'ShiftData') has been created, any unncessary rows can be removed from the calculations. The name can be used in the formulas instead of a large fixed range.

[AG20] =SUMPRODUCT((INDEX(ShiftData,,$AG$17+5))*(INDEX(ShiftData,,18)=$AF20)*(INDEX(ShiftData,,13)=AG$18))

Other Tips

file: excel_speed.htm 2016 Page updated jan16 CMIDX S1 P09 N