| Feedback | Index() | Address() | |

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.

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.

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().

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))`

- Sort the data (whenever possible) if it is to be used for lookups. Sorting is fast and can be automated with a macro if required.
- Avoid using
*exact*match lookups if possible. The MATCH() function contains a final argument (0 - exact match, 1 - approximate match) which affects the result. A value of zero will require an*exact*match and does**not**require the source data to be sorted in ascending order. - If you are doing lookups using the
*exact*match option the calculation time for the function is proportional to the number of cells scanned before a match is found. For lookups over large ranges this time can be very significant. - The lookup time using the approximate match options of VLOOKUP(), HLOOKUP()
or MATCH() on
**sorted**data is fast and not significantly increased by the length of the range you are looking up. - Keep
*exact*match lookups on the same worksheet as the data they are looking up. It is significantly faster. - Minimise the range of cells you are looking up. The smaller the range the better.
- When doing
*exact*match lookups restrict the range of cells to be scanned to a minimum. Use Dynamic Range Names rather than referring to a very large number of rows or columns. Sometimes you can pre-calculate a lower and upper range limit for the lookup. - Concatenating strings is a calculation-intensive operation.

file: excel_speed.htm | © meadinkent.co.uk 2016 | Page updated jan16 | CMIDX S1 P09 N |