Altos Web Solutions, inc. – 5725 Bravo Ave, Reno, NV 89506 USA

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 June 5, 2024

Leave a Reply

Your email address will not be published. Required fields are marked *