|
Optimizing Excel Calculation Bottlenecks
Most Excel spreadsheets contain a number of calculation bottlenecks. Some of the most common bottlenecks are:
Exact Match Lookup using MATCH, VLOOKUP, and HLOOKUP: Excel has to scan through each row of the data table until it finds a match. This can be very slow for large tables.
Array Formulas and SUMPRODUCT: Using Array formulas and SUMPRODUCT can do amazing things, but forces Excel to do many calculations, which often results in slow calculations.
Excel calculating more than you need: you can use FastExcel to more precisely control which parts of your workbooks should be calculated.
SUM, SUBTOTAL, SUMIF, and COUNTIF: These formulas can make Excel scan a large number of cells.
Single-threaded Functions: Using single-threaded worksheet functions such as INDIRECT can slow down calculation by a large factor.
User-Defined Functions: There are significant overheads involved in calling VBA UDFs and in transferring data from Excel to the UDF. With care these overheads can be minimized.
Volatile Functions: Using volatile functions such as OFFSET or INDIRECT means that Excel cannot get the best out its smart recalculation engine so that each recalculation takes longer.
Conditional Formats: A large number of conditional formats can significantly slow workbook calculation.
Large Ranges: Using larger ranges than necessary can be expensive for calculation time.
Duplicated calculations: it is very easy to build a spreadsheet where many of the calculations are being repeated many times.
Workbook Links: Links to other workbooks are slow and fragile.
What-If Data Tables: Data Tables use single-threaded iterative calculation methods which are slow.
|
