Excel Performance - Tips for Optimizing Performance Obstacles (2023)

Refers to:Excel | Excel 2013 | Ured 2016 | VBA

Follow these tips to optimize many common performance bottlenecks in Excel.

Optimize references and links

Learn how to improve performance related to reference and link types.

Do not use forward and backward referencing

To increase clarity and avoid errors, design your formulas so that they do not point forward (to the right or below) to other formulas or cells. Forward forwarding usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a reasonable sequence of calculations if there are many formulas whose calculations need to be deferred.

Reduce the use of circular references with repetition

Calculating circular references with iterations is slow because multiple calculations are required, and these calculations are single-threaded. You can often "unwrap" circular references using algebra so that the iterative calculation is no longer necessary. For example, in cash flow and interest calculations, try to calculate cash flow before interest, calculate interest, and then calculate cash flow including interest.

Excel calculates circular references sheet by sheet without considering dependencies. So you tend to get slow calculations if your circular references span more than one worksheet. Try moving circular calculations to a single worksheet or optimize the sequence of worksheet calculations to avoid unnecessary calculations.

Before the iterative calculations begin, Excel must recalculate the workbook to identify all circular references and their dependencies. This process is equivalent to two or three iterations of the calculation.

After circular references and their dependents are identified, each iteration requires Excel to calculate not only all cells in the circular reference, but also all cells that depend on cells in the circular reference chain, along with non-constant cells and their dependents. If you have a complex calculation that depends on cells in a circular reference, it may be faster to isolate it to a separate closed workbook and open it for recalculation after the circular calculation is merged.

It is important to reduce the number of cells in the circular calculation and the calculation time taken by these cells.

Avoid links between workbooks

Avoid links between workbooks when possible; they can be slow, break easily and are not always easy to find and fix.

Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. Some exceptions to this might be when you have many front-end calculations that are recalculated so infrequently that it makes sense to put them in a separate workbook, or when you don't have enough RAM.

Try using simple direct cell references that work on closed workbooks. This way you can avoid recalculationallyour linked workbooks when you recalculateanyworkbook. You can also see the values ​​that Excel has read from a closed workbook, which is often important for debugging and revising the workbook.

If you can't avoid using linked workbooks, try opening them all instead of closing them, and open the workbooks they're linked to before you open the workbooks they're linked to.

Reduce links between worksheets

Using a large number of worksheets can make your workbook easier to use, but it is generally slower to calculate references to other worksheets than references within worksheets.

Minimize the range used

To save memory and reduce file size, Excel tries to store data only about the area of ​​the worksheet that has been used. This is calledrange used. Sometimes various editing and formatting operations extend the used range well beyond what you would currently consider used. This may cause performance issues and file size issues.

You can check the visible range used in the worksheet using Ctrl+End. If this is excessive, consider deleting all rows and columns below and to the right of your actual last used cell, then saving the workbook. Make a backup first. If you have formulas with ranges that extend into or refer to the deleted range, those ranges will shrink or change to#N/A.

Allow additional data

When you frequently add rows or columns of data to your worksheets, you need to find a way to have your Excel formulas automatically point to the new data range, instead of trying to find and change your formulas each time.

You can do this by using a large range in your formulas that extends far beyond your current data limits. However, this can cause the calculation to be inefficient under certain circumstances, and is difficult to maintain because deleting rows and columns can reduce the range without you noticing.

Use structured table references (recommended)

Starting with Excel 2007, you can use structured table references, which automatically expand and contract as the size of the reference table increases or decreases.

This solution has several advantages:

• There are fewer performance drawbacks than the alternatives of full column referencing and dynamic ranges.

• It's easy to have multiple data tables on a single worksheet.

• Formulas embedded in a table also expand and collapse with the data.

Alternatively, use whole column and row references

An alternative approach is to use the entire column reference, for example$A:$A. This reference returns all the rows in column A. So you can add as much data as you want, and the reference will always include it.

This solution has both advantages and disadvantages:

• Many Excel built-in functions (AMOUNT,SUMIF) efficiently calculate the references of the entire column because they automatically recognize the last used row in the column. However, the array calculation works asSUMPROIZVODor cannot process entire column references or calculate all cells in a column.

• User-defined functions do not automatically recognize the last used row in a column and therefore often inefficiently calculate the references of the entire column. However, it is easy to program user-defined functions to recognize the last line used.

• It is difficult to use whole column references when you have multiple tables of data in a single worksheet.

• In Excel 2007 and later, field formulas can handle whole column references, but this forces a calculation for all cells in the column, including empty cells. This can be slow to compute, especially for 1 million rows.

Alternatively, use dynamic ranges

By usingOFFSETorINDEXiCOUNTAfunction in the named range definition, you can make the area to which the named range applies expand and contract dynamically. For example, create a defined name using one of the following formulas:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A: $A)+RED(List1!$A$1) - 1,1)

When you use a dynamic range name in a formula, it automatically expands to include the new entries.

UseINDEXthe dynamic range formula is generally preferable toOFFSETformula becauseOFFSEThas the disadvantage of being an unstable function that will be recalculated at each recalculation.

Efficiency decreases becauseCOUNTAa function within a dynamic range formula must examine many rows. You can reduce this performance degradation by storingCOUNTApart of the formula in a separate cell or defined name, then referencing the cell or name in the dynamic range:

Counts!z1=COUNTA(Sheet1!$A:$A) OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1) IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A :$A,Broji!$Z$1+RED(List1!$A$1) - 1,1)

You can also use functions such asINDIRECTLYto construct dynamic ranges butINDIRECTLYis volatile and always computes single-threaded.

Dynamic ranges have the following advantages and disadvantages:

• Dynamic ranges work well for limiting the number of calculations performed by array formulas.

• Using multiple dynamic ranges within a single column requires special purpose counting functions.

• Using a large number of dynamic ranges can reduce performance.

Improve lookup calculation time

In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact matching on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

However, for older versions of Excel, lookups are still often significant obstacles to calculation. Fortunately, there are many ways to improve the lookup calculation time. If you use the exact match option, the computation time for the function is proportional to the number of cells scanned before a match is found. For large range searches, this time can be significant.

Search time using approximate match optionsVLOOKUP,HLOOKUP, iCOMPARE YOURSELFon sorted data it is fast and not significantly increased by the length of the range you are looking for. The characteristics are the same as for binary search.

Understanding search options

Make sure you understand the match type and range search options in theCOMPARE YOURSELF,VLOOKUP, iHLOOKUP.

The following code example shows the syntax forCOMPARE YOURSELFfunction. For more information seeMatchingmethodWorksheet functionobject.

MATCH(searchValue, searchString, matchType)

• Match Type=1returns the highest match less than or equal to the search value when the search string is sorted in ascending order (approximate match). If the search field is not in ascending order, MATCH will return an incorrect answer. The default option isapproximate match in ascending order.

  (Video) How to Improve Excel Performance - Common Culprits & Solutions

• MatchType=0requires an exact match and assumes that the data is not sorted.

• MatchType=-1returns the lowest match greater than or equal to the lookup value if the lookup string is in descending order (approximate match).

The following code example shows the syntax forVLOOKUPiHLOOKUPfunctions. For more information seeVLOOKUPiHLOOKUPmethodWorksheet functionobject.

VLOOKUP(lookup value, table field, column index number, range lookup) HLOOKUP(lookup value, table field, row index number, range lookup)

• Range lookup=TRUEreturns the highest match less than or equal to the lookup value (approximate match). This is the default option. The array of the table must be sorted in ascending order.

• Range lookup=FALSErequires an exact match and assumes that the data is not sorted.

Avoid searching for unsorted data where possible as it is slow. If your data is sorted but you want an exact match, take a lookUse two searches for sorted data with missing values.

Use INDEX and MATCH or OFFSET instead of VLOOKUP

Try usingINDEXiCOMPARE YOURSELFfunctions instead ofVLOOKUP. AlthoughVLOOKUPit is slightly faster (about 5 percent faster), simpler, and uses less memory than combinationCOMPARE YOURSELFiINDEX, orOFFSET, additional flexibility whichCOMPARE YOURSELFiINDEXThe offer often allows you to save a significant amount of time. For example, you can store the result of correctCOMPARE YOURSELFin a cell and reuse it in severalINDEXstatements.

TheINDEXfunction is fast and is a permanent function, which speeds up recalculation. TheOFFSETthe function is also fast; however, it is an unstable function and sometimes significantly increases the time required to process the calculation chain.

It's easy to convertVLOOKUPdoINDEXiCOMPARE YOURSELF. The following two statements return the same response:

VLOOKUP(A1, Podaci!$A$2:$F$1000,3,False) INDEX(Podaci!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Speed ​​up searches

Since searching for an exact match can be slow, consider the following options to improve performance:

• Use one worksheet. It is faster to keep searches and data on the same sheet.

  See Also

  7 Best Intermittent Fasting Apps for 2023Convert PDF to URL Online | pdfFillerDoFasting Review: How Much Does It Cost in 2023?Android apps crash and close suddenly (fixed)

• When you can,SPECIESfirst the data (SPECIESis fast) and use approximate matching.

• When you must use an exact match search, limit the range of cells that are scanned to a minimum. Use tables and structured references or dynamic range names instead of referencing large numbers of rows or columns. Sometimes you can pre-calculate the lower and upper limits of the search range.

Use two searches for sorted data with missing values

Two approximate matches are significantly faster than one exact match for searching more than a few rows.(The coverage point is about 10-20 lines.)

If you can sort your data, but still can't use approximate matching because you can't be sure that the value you're looking for exists in the search range, you can use this formula:

IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _ VLOOKUP(lookup_val, lookup_array, stupac, True), "ne postoji")

The first part of the formula does an approximate search in the search column itself.

VLOOKUP(lookup_value, lookup_string, 1, true)

You can check if the answer from the lookup column is the same as the lookup value (in which case you have an exact match) using the following formula:

IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

If this formula returns True, you've found an exact match, so you can do the approximate lookup again, but this time return the answer from the column you want.

VLOOKUP(lookup_value, lookup_string, column, True)

If the answer from the lookup column does not match the lookup value, you are missing a value and the formula returns "notexist".

Note that if you search for a value smaller than the smallest value in the list, you will get an error. You can solve this error by usingIFERROR, or by adding a small test value to the list.

Use the IFERROR function for unsorted data with missing values

If you must use an exact match lookup on unsorted data, and you can't be sure if a lookup value exists, you often need to handle the #N/A that is returned if no match is found. Starting with Excel 2007, you can useIFERRORa function that is both simple and fast.

IF IFERROR(VLOOKUP(lookup value, table, 2 FALSE), 0)

In earlier versions, the easy but slow way is to useIa function that contains two lookups.

IF(ISNA(VLOOKUP(lookup,table,2,FALSE)),0,_ VLOOKUP(lookup,table,2,FALSE))

You can avoid a double exact search if you use exactCOMPARE YOURSELFonce, store the result in a cell, then test the result before buildingINDEX.

U A1 =MATCH(lookupvalue,lookuparray,0) U B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

If you can't use two cells, use themCOUNTIF. It is generally faster than searching for an exact match.

IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _ VLOOKUP(lookupval, table, 2 FALSE))

Use MATCH and INDEX to search for an exact match across multiple columns

You can often reuse a stored exactCOMPARE YOURSELFmany times. For example, if you are looking for exactly multiple columns of results, you can save time by using oneCOMPARE YOURSELFand manyINDEXstatements, not manyVLOOKUPstatements.

Add an additional column forCOMPARE YOURSELFto store results (stored_row), and for each result column use the following:

INDEX(search_range, stored_row, column_number)

Alternatively, you can useVLOOKUPin the field formula. (Field formulas must be entered using Ctrl+-Shift+Enter. Excel will add { and } to show you that this is a field formula).

{VLOOKUP(lookupvalue,{4,2},FALSE)}

Use INDEX for a set of consecutive rows or columns

You can also return many cells from a single search operation. To search for several consecutive columns, you can useINDEXfunction in an array formula to return multiple columns at once (use 0 as the column number). You can also useINDEXfunction to return multiple rows at once.

{INDEX($A$1:$J$1000,stored_row,0)}

This returns column A to column J from the stored row created by the previous oneCOMPARE YOURSELFstatement.

Use MATCH to return a rectangular block of cells

UseCOMPARE YOURSELFiOFFSETfunctions to return a rectangular block of cells.

Use MATCH and INDEX for two-dimensional searching

You can perform a two-dimensional table search efficiently by using separate table row and column searches usingINDEXfunction with two built-inCOMPARE YOURSELFfunctions, one for row and one for column.

Use a subset range to search multiple indexes

In large worksheets, you will often need to search using multiple indexes, such as searching for the quantity of a product in a country. To do this, you can chain indexes and search using chained search values. However, this is ineffective for two reasons:

• String concatenation is a computationally intensive operation.

• The search will cover a large range.

It is often more efficient to calculate the subset range to search (for example, by finding the first and last rows for a country, and then searching for products within that subset range).

Consider options for a three-dimensional view

To find a table to use with a row and column, you can use the following techniques, focusing on how Excel searches for or selects a table.

If each table you want to search (the third dimension) is stored as a set of named structured tables, range names, or as a table of text strings representing the ranges, you may be able to useCHOOSEorINDIRECTLYfunctions.

• UseCHOOSEand range names can be an effective method.CHOOSEit is not unstable, but it is best suited for a relatively small number of tables. This example uses dynamicTableLookup_Valueto select a range name (NazivTablice1, NazivTablice2, ...) to use for the lookup table.

  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

• The following example usesINDIRECTLYfunction iTableLookup_Valueto dynamically create the list name to use for the lookup table. This method has the advantage that it is simple and can handle a large number of tables. BecauseINDIRECTLYis a non-persistent single-threaded function, the search is computed single-threaded on each computation even if the data has not changed. Using this method is slow.

  INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

• You can also useVLOOKUPto find the name of the sheet or text string used for the table, then useINDIRECTLYfunction to convert the resulting text into a range.

  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

Another technique is to aggregate all your tables into one huge table that has an extra column that identifies the individual tables. You can then use the techniques for searching multiple indexes shown in the previous examples.

Use wildcard search

TheCOMPARE YOURSELF,VLOOKUP, iHLOOKUPfunctions allow you to use wildcards?(any single character) i*(no character or any number of characters) on exact alphabetical matches. Sometimes you can use this method to avoid multiple matches.

Optimize field formulas and SUMPRODUCT

Field formulas iSUMPROIZVODare powerful, but you must handle them carefully. A single array formula can require many calculations.

The key to optimizing the calculation speed of array formulas is to ensure that the number of cells and expressions evaluated in an array formula is as small as possible. Note that an array formula is a bit like a mutable formula: if any cell it refers to has changed, is volatile, or has been recalculated, the array formula calculates all cells in the formula and evaluates all virtual cells. a calculation should be made.

To optimize the calculation speed of the field formula:

• Extract expressions and range references from field formulas into separate auxiliary columns and rows. This makes much better use of Excel's smart recalculation process.

  (Video) How to Resolve Excel Running Slow? [6 Solutions]

• Do not reference entire rows or more rows and columns than you need. Field formulas are forced to calculate all cell references in the formula even if the cells are empty or unused. With 1 million rows available starting with Excel 2007, a field formula that references an entire column is extremely slow to calculate.

• Starting with Excel 2007, use structured references where you can minimize the number of cells that are evaluated by a field formula.

• In versions earlier than Excel 2007, use dynamic range names where possible. Although they are fickle, it is worth it because they minimize the size of the range.

• Be careful with array formulas that refer to both row and column: this forces a rectangular range calculation.

• UseSUMPROIZVODif it is possible; it is slightly faster than the equivalent field formula.

Consider options for using SUM for array formulas with multiple conditions

You should always useSUMIFS,COUNTIFS, iAVERAGEIFSfunctions instead of array formulas where you can because they are much faster to calculate. Excel 2016 introduces fastMAXIFSiMINIFSfunctions.

In versions older than Excel 2007, field formulas are often used to calculate sums with multiple conditions. This is relatively easy to do, especially if you useConditional Sum Wizardin Excel, but it is often slow. There are usually much faster ways to get the same result. If you only have a few SUMs with multiple conditions, you may be able to useDSUMwhich is much faster than the equivalent field formula.

If you must use array formulas, some good methods to speed them up are as follows:

• Use dynamic range names or structured table references to reduce the number of cells.

• Split multiple conditions into a column of auxiliary formulas that returnHe saysorfalselyfor each row, then refer to the auxiliary column in aSUMIFor array formula. This may not reduce the number of calculations for a single array formula; however, most of the time it allows the smart recalculation process to recalculate only the formulas in the auxiliary column that need to be recalculated.

• Consider merging all the conditions into one condition and then using itSUMIF.

• If the data is sortable, count row groups and limit field formulas to looking at subset groups.

Prefer SUMIFS, COUNTIFS, and other IFS family functions with multiple conditions

These functions evaluate each condition in turn from left to right. Therefore, it is more efficient to set the most restrictive condition first, so that subsequent conditions only need to observe the smallest number of rows.

Consider options for using SUMPRODUCT for field formulas with multiple conditions

Starting with Excel 2007, you should always useSUMIFS,COUNTIFS, iAVERAGEIFSfunctions, and in Excel 2016MAXIFSiMINIFSfunctions, insteadSUMPROIZVODformulas where possible.

There are several advantages to using earlier versionsSUMPROIZVODinsteadAMOUNTfield formulas:

• SUMPROIZVODit does not have to be entered as a string using Ctrl+Shift+Enter.

• SUMPROIZVODis usually slightly faster (5 to 10 percent).

UseSUMPROIZVODfor array formulas with multiple conditions as follows:

SUMPRODUCT(--(Condition1),--(Condition2),Range toSum)

In this example,Condition1iCondition 2are conditional expressions such as$A$1:$A$10000<=$Z4. Because conditional expressions returnHe saysorfalselyinstead of numbers, they must force numbers withinSUMPROIZVODfunction. You can do this using two minus signs (--), or by adding 0 (+0), or by multiplying by 1 (x1). Use--is somewhat faster than+0orx1.

Note that the size and shape of ranges or arrays used in conditional expressions and summation ranges must be the same and cannot contain entire columns.

You can also directly multiply members withinSUMPROIZVODinstead of separating them with commas:

SUMPRODUCT((Condition1)*(Condition2)*RangeSum)

This is usually slightly slower than the comma syntax and gives an error if the range to sum contains a text value. However, it is somewhat more flexible because the summation range can have, for example, multiple columns when the conditions have only one column.

Use SUMPRODUCT to multiply and add ranges and fields

In cases such as weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax forSUMPROIZVODcan be 20 to 25 percent faster than the input stringAMOUNT.

{=SUM($D$2:$D$10301*$E$2:$E$10301)} =SUMPROIZVOD($D$2:$D$10301*$E$2:$E$10301) =SUMPROIZVOD($D$2:$D$10301 ).,$E$2:$E$10301)

All three of these formulas produce the same result, but the third formula, which uses the comma syntax forSUMPROIZVOD, takes only about 77 percent of the calculation time required for the other two formulas.

Be aware of the potential obstacles to calculating arrays and functions

The calculation engine in Excel is optimized to take advantage of field formulas and range-related functions. However, some unusual arrangements of these formulas and functions can sometimes, but not always, cause significantly increased calculation times.

If you find a calculation obstacle involving field formulas and range functions, you should look for the following:

• Partially overlapping references.

• Field formulas and range functions that reference part of a block of cells that are calculated in another field formula or range function. This situation can often occur in time series analysis.

• One set of formulas references by row, and another set of formulas that references the first set by column.

• A large set of single-row array formulas covering a block of columns, withAMOUNTfunctions at the foot of each column.

Use functions effectively

Functions greatly extend the power of Excel, but how you use them can often affect calculation times.

Avoid single-threaded functions

Most native Excel functions work well with multi-threaded calculation. However, where possible, avoid using the following single-threaded functions:

• VBA and Automation user-defined functions (UDFs), but XLL-based UDFs can be multithreaded
• FONETSKI
• CELL when the "format" or "address" argument is used.
• INDIRECTLY
• GET THE DATA
• CUBE MEMBER
• CUBE VALUE
• CUBEMBERPROPERTY
• CUBESET
• CUBERANKEDMMER
• CUBE MEMBER
• CUBESETCOUNT
• ADDRESS where the fifth parameter (thelist_name) is given
• Any database function (DSUM, DAVERAGE, and so on) that relates to a pivot table
• ERROR.TYPE
• HYPERVEZY

Use tables for functions that handle ranges

For functions likeAMOUNT,SUMIF, iSUMIFSthat handle ranges, the calculation time is proportional to the number of used cells that you are adding or counting. Unused cells are not examined, so whole column references are relatively efficient, but it's better to make sure you don't include more used cells than you need. Use tables or calculate subset ranges or dynamic ranges.

Reduce erratic functions

Volatile functions can slow down recalculation because they increase the number of formulas that must be recalculated with each calculation.

You can often reduce the number of volatile functions by usingINDEXinsteadOFFSET, iCHOOSEinsteadINDIRECTLY. However,OFFSETis a fast function and can often be used in creative ways that give a quick calculation.

Use C or C++ user-defined functions

User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally run faster than user-defined functions that are developed using VBA or Automation (XLA or Automation add-ins). For more information seeDevelopment of Excel 2010 XLL.

The performance of VBA user-defined functions depends on how you program and call them.

Use faster VBA user-defined functions

It is usually faster to use Excel formula calculations and worksheet functions than to use VBA user-defined functions. This is because there is a small overhead for each call to the user-defined function and a significant overhead in transferring information from Excel to the user-defined function. But well-designed and called user-defined functions can be much faster than complex array formulas.

Make sure you put all references to worksheet cells in the input parameters of the user-defined function instead of in the body of the user-defined function, so you can avoid addingApplication. Volatileunnecessarily.

If you must have many formulas that use user-defined functions, make sure you are in manual calculation mode and that the calculation is run from VBA. VBA user-defined functions calculate much more slowly if the calculationit iscalled from VBA (for example, in automatic mode or when you press F9 in manual mode). This is especially true when the Visual Basic Editor (Alt+F11) is open or is open in the current Excel session.

You can capture F9 and redirect it to a VBA calculation subroutine as follows. Add this subroutine toThis workbookmodule.

Private Sub Workbook_Open() Application.OnKey "{F9}", "Recalc" End Sub

Add this routine to the standard module.

Sub Recalc() Application.Calculate MsgBox "hello" End Sub

User-defined functions in Automation Add-ins (Excel 2002 and later versions) do not incur additional costs for the Visual Basic Editor because they do not use the integrated editor. Other performance characteristics of Visual Basic 6 user-defined functions in automation add-ins are similar to VBA functions.

If your user-defined function processes each cell in the range, declare the input as a range, assign it to the variant containing the array, and repeat. If you want to efficiently handle entire column references, you must make a subset of the input range, dividing it at the intersection with the used range, as in this example.

Public Function DemoUDF(theInputRange As Range) Dim vArr As Variant Dim vCell As Variant Dim oRange As Range Set oRange=Union(theInputRange, theRange.Parent.UsedRange) vArr=oRange For Each vCell In vArr If IsNumeric(vCell) Then DemoUDF=DemoUDF +vCell Next vCell termination function

If your user-defined function uses worksheet functions or Excel object model methods to process ranges, it is generally more efficient to keep the range as an object variable than to pass all data from Excel to the user-defined function.

Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _ col_num As Variant, sorted As Variant, _ NotFound As Variant) Dim vAnsa As Variant vAnsa = Application.VLookup(lookup_value, lookup_array, _ col_num, sorted) If Not IsError(vAnsa) Then uLOOKUP = vAnsa Else uLOOKUP = NotFound End If End Function

If your user-defined function is called early in the calculation chain, it can be passed as an uncalculated argument. Within a user-defined function, you can detect uncalculated cells using the following test for empty cells containing a formula:

As is ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Time exists for each call to a user-defined function and for each transfer of data from Excel to VBA. Sometimes a single user-defined multi-cell array formula function can help you minimize this overhead by combining multiple function calls into a single function with a multi-cell input range that returns an array of responses.

Reduce the range of cells referenced by SUM and SUMIF

ExcelAMOUNTiSUMIFfunctions are often used over a large number of cells. The calculation time for these functions is proportional to the number of cells involved, so try to minimize the range of cells that the functions are called on.

Use wildcards SUMIF, COUNTIF, SUMIFS, COUNTIFS and other IFS functions

Use wildcards?(any single character) i*(no character or any number of characters) in the criteria for alphabetic ranges as partSUMIF,COUNTIF,SUMIFS,COUNTIFS, and othersIFSfunctions.

Select a method for period to date and cumulative SUMs

There are two methods of calculating Period-to-Date or Cumulative Totals. Assume the numbers you want cumulativelyAMOUNTare in column A, and you want column B to contain the cumulative total; you can do one of the following:

• You can create a formula in column B like=SUM($A$1:$A2)and drag it down as much as you need. The starting cell of SUM is anchored to A1, but because the ending cell has a relative row reference, it is automatically incremented by each row.

• You can create a formula like=$A1in cell B1 i=$B1+$A2in cell B2 and drag it down as far as you need. This calculates the cumulative cell by adding the number of this row to the previous cumulativeAMOUNT.

For 1000 rows, the first method makes Excel do about 500,000 calculations, but the second method only makes Excel do about 2000 calculations.

Calculate the subset sums

When you have multiple sorted indexes for a table (for example, a site within a range), you can often save significant computation time by dynamically calculating the address of a subset of the row (or column) range to use inAMOUNTorSUMIFfunction.

To calculate the address of a subset of rows or columns:

1. Count the number of rows for each subset block.

2. Add the cumulative for each block to determine its starting row.

3. UseOFFSETwith start row and count to return the subset range inAMOUNTorSUMIFwhich covers only a subset of the row blocks.

Use SUBTOTAL for filtered listings

UseSUBTOTALfunction forAMOUNTfiltered lists. TheSUBTOTALfunction is useful because, unlikeAMOUNT, ignores the following:

• Hidden rows created by filtering the list. Starting with Excel 2003, you can also doSUBTOTALignore all hidden rows, not just filtered rows.

• the restSUBTOTALfunctions.

Use the AGGREGATE function

The AGGREGATE function is a powerful and efficient way to calculate 19 different data collection methods (such asAMOUNT,MEDIAN,PERCENTAGEiA BIG).AGGREGATEhas options to ignore hidden or filtered rows, error values, and nested onesSUBTOTALiAGGREGATEfunctions.

Avoid using DFunctions

DFunctionsDSUM,DCOUNT,DAVERAGE, and so on are significantly faster than the equivalent field formulas. A disadvantage of DFunctions is that the criteria must be in a separate range, which makes them impractical to use and maintain in many circumstances. Starting with Excel 2007, you should useSUMIFS,COUNTIFS, iAVERAGEIFSfunctions instead of DFunctions.

Create faster VBA macros

Use the following tips to create faster VBA macros.

Turn off everything but essential while the code is running

To improve performance for VBA macros, explicitly turn off functionality that is not needed while your code is executing. Often, just one recalculation or one plotting after running the code is all that is needed and can improve performance. After your code executes, restore the functionality to its original state.

The following functions can usually be turned off while a VBA macro is running:

• Application.ScreenUpdatingTurn off screen updates. IfApplication.ScreenUpdatingit is set tofalsely, Excel does not redraw the screen. While your code is running, the screen is updated quickly, and it's usually not necessary for the user to see each update. Updating the screen once, after code execution, improves performance.

• Application.DisplayStatusBarTurn off the status bar. IfApplication.DisplayStatusBarit is set tofalsely, Excel does not display the status bar. The status bar setting is separate from the screen update setting so that you can still display the status of the current operation even while the screen is not updating. However, if you don't need to display the status of each operation, turning off the status bar while your code is running also improves performance.

• Application. CalculationGo to manual calculation. IfApplication. Calculationit is set toxlCalculation manual, Excel calculates the workbook only when the user explicitly initiates the calculation. In automatic calculation mode, Excel determines when to calculate. For example, every time the value of a cell associated with a formula changes, Excel recalculates the formula. If you switch the calculation mode to manual, you can wait for all cells associated with the formula to update before recalculating the workbook. By recalculating the workbook only when necessary while your code is running, you can improve performance.

• Application.EnableEventsTurn off events. IfApplication.EnableEventsit is set tofalsely, Excel does not fire events. If there are add-ins that listen to Excel events, those add-ins consume computer resources while logging events. If you don't need the plugin to log events that occur during code execution, turning off events improves performance.

• ActiveSheet.DisplayPageBreaksTurn off page breaks. IfActiveSheet.DisplayPageBreaksit is set tofalsely, Excel does not display page breaks. There is no need to recalculate page breaks while your code is running, and calculating page breaks after code execution improves performance.

The following example shows functionality that you can turn off while a VBA macro is executing.

' Save the current state of Excel settings. screenUpdateState = Application.ScreenUpdating statusBarState = Application.DisplayStatusBar calcState = Application.Calculation eventsState = Application.EnableEvents ' Note: This is a sheet-level setting. displayPageBreakState = ActiveSheet.DisplayPageBreaks ' Turn off Excel functionality to improve performance. Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' Note: This is a leaf-level setting. ActiveSheet.DisplayPageBreaks = False ' Insert your code here. ' Restore Excel settings to their original state. Application.ScreenUpdating = screenUpdateState Application.DisplayStatusBar = statusBarState Application.Calculation = calcState Application.EnableEvents = eventsState ' Note: this is a sheet-level setting ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Read and write large blocks of data in a single operation

Optimize your code by explicitly reducing the number of data transfers between Excel and your code. Instead of looping through cells one by one to get or set a value, get or set values ​​in an entire range of cells in a single row, using a variant that contains a two-dimensional array to store values ​​as needed. The following code examples compare these two methods.

The following code example shows unoptimized code that loops through the cells one by one to get and set the values ​​of cells A1:C10000. These cells do not contain formulas.

Dim DataRange as Range Dim Irow as Long Dim Icol as Integer Dim MyVar as Double Set DataRange=Range("A1:C10000") For Irow=1 to 10000 For icol=1 to 3 ' Read the values ​​from the Excel grid 30,000 times. MyVar=DataRange(Irow,Icol) If MyVar > 0 Then ' Change the value. MyVar=MyVar*Myvar ' Write the values ​​back to the Excel grid 30,000 times. DataRange(Irow,Icol)=MyVar End If Next Icol Next Irow

The following code example shows optimized code that uses an array to get and set the values ​​of cells A1:C10000 at the same time. These cells do not contain formulas.

Dim DataRange As Variant Dim Irow As Long Dim Icol As Integer Dim MyVar As Double ' Read all the values ​​at once from the Excel grid and put them into the field. DataRange = Range("A1:C10000").Value2 For Irow = 1 To 10000 For Icol = 1 To 3 MyVar = DataRange(Irow, Icol) If MyVar > 0 Then ' Change the values ​​in the array. MyVar=MyVar*Myvar DataRange(Irow, Icol) = MyVar End If Next Icol Next Irow ' Write all the values ​​back to the range at once. Range("A1:C10000").Value2 = DataRange

Use .Value2 instead of .Value or .Text when reading data from an Excel range

• .Textreturns the formatted cell value. This is slow, may return ### if the user zooms, and may lose precision.
• .Valuereturns a VBA currency or a VBA date variable if the range is formatted as a date or currency. This is slow, can lose precision, and can cause errors when calling worksheet functions.
• .Value2is fast and does not change the data retrieved from Excel.

Avoid selecting and activating objects

Selecting and activating objects is a more intensive process than directly referencing objects. By referencing an object such as aSpanor aFormdirectly, you can improve performance. The following code examples compare the two methods.

The following code example shows unoptimized code that selects each shape in the active sheet and changes the text to "Hello".

For i = 0 To ActiveSheet.Shapes.Count ActiveSheet.Shapes(i).Select Selection.Text = "Hello" Next i

The following code example shows optimized code that references each shape directly and changes the text to "Hello".

For i = 0 To ActiveSheet.Shapes.Count ActiveSheet.Shapes(i).TextEffect.Text = "Hello" Next i

Use these additional VBA performance optimizations

The following is a list of additional performance optimizations that you can use in your VBA code:

• Return the results by assigning the string directly to aSpan.

• Declare variables with explicit types to avoid the burden of determining the data type, preferably multiple times in a loop, during code execution.

• For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of usingWorksheet functionobject. For more information seeUse faster VBA user-defined functions.

• UseRange.SpecialCellsmethod to reduce the number of cells your code interacts with.

• Consider the performance gains if you implemented your functionality using the C API in the XLL SDK. For more information seeExcel 2010 XLL SDK Documentation.

Consider the performance and size of Excel file formats

Starting with Excel 2007, Excel contains a larger variety of file formats compared to earlier versions. Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.

• XLS format

  The XLS format is the same format as the previous versions. When using this format, you are limited to 256 columns and 65,536 rows. When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check. The file size is almost the same as in earlier versions (some additional information may be stored), and the performance is slightly slower than earlier versions. Any multi-threaded optimization that Excel does with respect to the order of cell calculations is not saved in XLS format. Therefore, the calculation of the workbook may be slower after saving the workbook in XLS format, closing and reopening the workbook.

• XLSB format

  XLSB is a binary format starting in Excel 2007. It is structured as a compressed folder containing many binary files. It is much more compact than the XLS format, but the amount of compression depends on the content of the workbook. For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. Starting with Excel 2007, opening and saving performance is only slightly slower than XLS format.

  (Video) Maximize Profit by Optimizing Production Using Excel Solver

• XLSX format

  XLSX is an XML format starting with Excel 2007 and is the default format starting with Excel 2007. The XLSX format is a compressed folder that contains many XML files (if you change the filename extension to .zip, you can open the compressed folder and examine its contents). Typically, the XLSX format creates larger files than the XLSB format (1.5 times larger on average), but they are still significantly smaller than XLS files. You should expect the opening and saving times to be slightly longer than for XLSB files.

Opening, closing and saving workbooks

You may find that opening, closing, and saving workbooks is much slower than calculating them. Sometimes it's just because you have a large workbook, but there could be other reasons.

If one or more of your workbooks is opening and closing more slowly than is reasonable, it may be caused by one of the following problems.

• Temporary files

  Temporary files can accumulate in the \Windows\Temp directory (in Windows 95, Windows 98, and Windows ME) or in the \Documents and Settings\User Name\Local Settings\Temp directory (in Windows 2000 and Windows XP). Excel creates these files for the workbook and for controls that use open workbooks. Software installers also create temporary files. If Excel stops responding for any reason, you may need to delete those files.

  Too many temporary files can cause problems, so you should clean them periodically. However, if you have installed software that requires you to restart your computer and have not yet done so, you should restart it before deleting temporary files.

An easy way to open the temporary directory is from WindowsBeginningmenu: ClickBeginning, and then clickJogging. Type in the text box%temp%, and then clickAlright.

• Track changes in a shared workbook

  Tracking changes to a shared workbook causes your workbook's file size to grow rapidly.

• Fragmented swap file

  Make sure your Windows swap file is on a disk with plenty of space and defrag the disk periodically.

• Workbook with password protected structure

  A workbook whose structure is protected by a password (Alwaysmenu >Protection>Protect the workbook> enter an optional password) opens and closes much more slowly than one protected without an optional password.

• Problems with the scope used

  Using ranges that are too large can cause slow opening and increased file size, especially if they are caused by hidden rows or columns that have a non-standard height or width. For more information on problems with the range used, seeMinimize the range used.

• A large number of controls on worksheets

  A large number of controls (check boxes, hyperlinks, and so on) on worksheets can slow down the opening of the workbook due to the number of temporary files used. This can also cause problems when opening or saving a workbook on the WAN (or even LAN). If you have this problem, you should consider redesigning your workbook.

• A large number of links to other workbooks

  If possible, open the workbooks you are linking to before you open the workbook that contains the links. It is often faster to open a workbook than to read links from a closed workbook.

• Virus scanner settings

  Some virus scanner settings can cause problems or slowness when opening, closing, or saving, especially on the server. If you think this might be the problem, try temporarily turning off the virus scanner.

• Slow calculation causes slow opening and saving

  In some circumstances, Excel recalculates your workbook when you open or save it. If the calculation time for your workbook is long and causing a problem, make sure the calculation is set tomanualand consider switching offcalculate before savingoption (Always>Possibilities>Calculation).

• Toolbar files (.xlb)

  Check the toolbar file size. A typical toolbar file is between 10 KB and 20 KB. You can find your XLB files by searching for*.xlbusing Windows search. Each user has a unique XLB file. Adding, changing, or customizing toolbars increases the size of your toolbar.xlb file. Deleting the file removes all your toolbar customizations (it's safer to rename it to "toolbar.OLD"). A new XLB file is created the next time you open Excel.

Make additional performance optimizations

You can improve performance in the following areas.

• Pivot tables

  Pivot tables provide an efficient way to summarize large amounts of data.

  • Totals as final results. If you need to produce totals and subtotals as part of your workbook's final results, try using pivot tables.

  • Sums as intermediate results. PivotTables are a great way to create summary reports, but try to avoid creating formulas that use PivotTable results as subtotals and subtotals in your calculation chain unless you can ensure the following conditions:

  • The pivot table is correctly refreshed during the calculation.

  • The pivot table has not been changed so the information is still visible.

  If you still want to use pivot tables as intermediate results, useGET THE DATAfunction.

• Conditional formats and data validation

  Conditional formats and data validation are great, but using too many of them can slow down the calculation significantly. If the cell is displayed, each conditional format formula is evaluated on each calculation and when the display of the cell containing the conditional format is refreshed. Excel's object model has aRadni list.EnableFormatConditionsCalculationso you can enable or disable the calculation of conditional formats.

• Defined names

  Defined names are one of the most powerful features in Excel, but they require additional calculation time. Using names that refer to other worksheets adds an additional level of complexity to the calculation process. Also, you should try to avoid nested names (names that refer to other names).

  Because names are calculated every time a formula that refers to them is calculated, you should avoid putting formulas or functions that require calculation inside defined names. In these cases, it can be much faster to put your formula or function that requires calculations somewhere in a spare cell and refer to that cell instead, either directly or by name.

• Formulas that are used only occasionally

  Many workbooks contain a significant number of formulas and lookups that deal with getting the input data into a suitable form for calculations or are used as defensive measures against changes in the size or shape of the data. When you have formula blocks that are used only occasionally, you can copy and paste special values ​​to temporarily remove the formulas, or you can put them in a separate workbook that is rarely opened. Since worksheet errors are often caused by not noticing that formulas have been converted to values, the separate workbook method may be better.

• Use enough memory

  The 32-bit version of Excel can use up to 2 GB of RAM, or up to 4 GB of RAM for the 32-bit versions of Excel 2013 and 2016 with the large address feature. However, the computer running Excel also requires memory resources. Therefore, if you only have 2 GB of RAM on your computer, Excel cannot use all 2 GB because some of the memory is allocated to the operating system and other running programs. To optimize the performance of Excel on a 32-bit computer, we recommend that the computer has at least 3 GB of RAM.

  The 64-bit version of Excel does not have a limit of 2 GB or up to 4 GB. For more information, see the section "Large data sets and the 64-bit version of Excel" inExcel performance: performance improvements and limitations.

Conclusion

This article covered ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common pitfalls and improve performance.

See also

• Excel performance: Improve calculation performance
• Excel performance: performance improvements and limitations
• Portal for Excel developers

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please take a lookOffice VBA support and feedbackfor guidance on ways to get support and give feedback.