Excel Worksheet Optimization

Here a summary of the tips to take care of

• Optimize references and links:
  • Do not use forward referencing and backward referencing;
  • Minimize use of circular references with iteration
  • Avoid links between workbooks;
  • Minimize links between worksheets;
• Minimize the used range;
• Allow for extra data:
  • Use structured table references (recommended)
  • Alternatively, use whole column and row references;
  • Alternatively, use dynamic ranges;
• Improve lookup calculation time;
  • Understand lookup options;
• Use INDEX and MATCH or OFFSET instead of VLOOKUP:
  • Speed up lookups;
  • Use two lookups for sorted data with missing values;
  • Use IFERROR function for unsorted data with missing values;
  • Use MATCH and INDEX for exact match lookups on multiple columns;
  • Use INDEX for a set of contiguous rows or columns;
  • Use MATCH to return a rectangular block of cells;
  • Use MATCH and INDEX for two-dimensional lookup;
  • Use a subset range for multiple-index-lookup;
  • Consider options for three-dimensional lookup;
  • Use wildcard lookup;
• Optimize array formulas and SUMPRODUCT:
  • Consider options for using SUM for multiple-condition array formulas;
  • Prioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions;
  • Consider options for using SUMPRODUCT for multiple-condition array formulas
  • Use SUMPRODUCT to multiply and add ranges and arrays;
  • Be aware of potential array and function calculation obstructions;
• Use functions efficiently:
  • Avoid single-threaded functions;
  • Use tables for functions that handle ranges;
  • Reduce volatile functions;
  • Use C or C++ user-defined functions;
  • Use faster VBA user-defined functions;
  • Minimize range of cells that SUM and SUMIF reference;
  • Use wildcard SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions;
  • Choose method for period-to-date and cumulative SUMs;
  • Calculate subset sums;
  • Use SUBTOTAL for filtered lists;
  • Use the AGGREGATE function;
  • Avoid using DFunctions;
• Create faster VBA macros
  • Turn off everything but the essentials while code is running;
  • Read and write large blocks of data in a single operation;
  • Use .Value2 rather than .Value or .Text when reading data from an Excel range;
  • Avoid selecting and activating objects;
  • Use these additional VBA performance optimizations;
• Consider performance and size of Excel file formats;
• Open, close, and save workbooks;
• Make additional performance optimizations.

Source: learn.microsoft.com

1. Use tables to hold the data;
2. Use named ranges, named formulas;
3. Use Dynamic Arr;ays & Spill Ranges;
4. Use Pivot Tables;
5. Sort your data;
6. Use Manual Calculation Mode;
7. Use Non-volatile formulas;
8. Keep formulas in a separate sheet;
9. Write better formulas;
10. Desperate times need desperate measures.

source: chandoo.org

Last Updated on April 27, 2024