Like you do in math, you can change the order of Excel calculations by enclosing the part to be calculated first in parentheses. The result of this calculation is And the result of this calculation is Another example is finding a root in Excel. To get the square root of, say, 16, you can use either this formula:. Because if we don't, Excel will raise 16 to the power of 1 first an exponent operation is performed before division , and then divide the result by 2. Since any number raised to the power of 1 is the number itself, we would end up dividing 16 by 2.
As you can see in the screenshot below, the same calculation with and without parentheses produces different results:. Lookups on sorted data can be tens or hundreds of times more efficient than lookups on unsorted data. VBA user-defined functions are usually slower than the built-in functions in Excel although carefully written VBA functions can be fast. Calculation time is proportional to the number of used cells unused cells are ignored.
The better use you make of smart recalculation and multithreaded calculation in Excel, the less processing has to be done every time that Excel recalculates, so:. Avoid iterative use of data tables and circular references: both of these always calculate single-threaded. Some of the changes that you make might surprise you, either by not giving the answer that you thought they would, or by calculating more slowly than you expected.
Therefore, you should time and test each change, as follows:. For example, you need to calculate the period-to-date sums of a column that contains 2, numbers. Assume that column A contains the numbers, and that column B and column C should contain the period-to-date totals. How many cell references are added up by SUM in total? B1 refers to one cell, and B refers to 2, cells. The average is 1, references per cell, so the total number of references is 2 million.
Selecting the 2, formulas and using the RangeTimer macro shows you that the 2, formulas in column B calculate in 80 milliseconds. Now how many cell references are added up in total? Each formula, except the first formula, uses two cell references.
This is a factor of fewer cell references. RangeTimer indicates that the 2, formulas in column C calculate in 3. If you have a calculation-intensive formula where you want the result to be shown as zero if there is an error this frequently occurs with exact match lookups , you can write this in several ways.
If you have a list of 11, rows of data in column A, which frequently changes, and you need a formula that dynamically calculates the number of unique items in the list, ignoring blanks, following are several possible solutions. This formula takes User-defined functions. The following code example shows a VBA user-defined function that uses the fact that the index to a collection must be unique.
For an explanation of some techniques that are used, see the section about user-defined functions in the "Using functions efficiently" section in Excel performance: Tips for optimizing performance obstructions.
This gives an improvement factor of Adding a column of formulas. If you look at the previous sample of the data, you can see that it is sorted Excel takes 0.
You can exploit this by adding a column of formulas that checks if the data in this row is the same as the data in the previous row. If it is different, the formula returns 1. Otherwise, it returns 0. A full calculation of all these formulas takes 0.
Excel enables you to effectively manage much larger worksheets, and it provides significant improvements in calculation speed compared with early versions. When you create large worksheets, it is easy to build them in a way that causes them to calculate slowly.
Slow-calculating worksheets increase errors because users find it difficult to maintain concentration while calculation is occurring. By using a straightforward set of techniques, you can speed up most slow-calculating worksheets by a factor of 10 or You can also apply these techniques as you design and create worksheets to ensure that they calculate quickly.
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.
Privacy policy. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. The importance of calculation speed Poor calculation speed affects productivity and increases user error. Excel has two main calculation modes that let you control when calculation occurs: Automatic calculation - Formulas are automatically recalculated when you make a change.
Understanding calculation methods in Excel To improve the calculation performance in Excel, you must understand both the available calculation methods and how to control them.
Full calculation and recalculation dependencies The smart recalculation engine in Excel tries to minimize calculation time by continuously tracking both the precedents and dependencies for each formula the cells referenced by the formula and any changes that were made since the last calculation.
At the next recalculation, Excel recalculates only the following: Cells, formulas, values, or names that have changed or are flagged as needing recalculation. Cells dependent on other cells, formulas, names, or values that need recalculation. Volatile functions and visible conditional formats.
Calculation process Excel formulas that reference other cells can be put before or after the referenced cells forward referencing or backward referencing. Excel has distinct calculation phases: Build the initial calculation chain and determine where to begin calculating. Update the visible parts of the Excel windows. This occurs for several reasons: Excel usually recalculates only cells that have changed, and their dependents.
Calculating workbooks, worksheets, and ranges You can control what is calculated by using the different Excel calculation methods. Calculate all open workbooks Each recalculation and full calculation calculates all the workbooks that are currently open, resolves any dependencies within and between workbooks and worksheets, and resets all previously uncalculated dirty cells as calculated.
Calculate : Range. Volatile functions A volatile function is always recalculated at each recalculation even if it does not seem to have any changed precedents. Volatile actions Volatile actions are actions that trigger a recalculation, and include the following: Clicking a row or column divider when in automatic mode.
Inserting or deleting rows, columns, or cells on a sheet. Adding, changing, or deleting defined names. Renaming worksheets or changing worksheet position when in automatic mode.
Filtering, hiding, or un-hiding rows. Opening a workbook when in automatic mode. If the workbook was last calculated by a different version of Excel, opening the workbook usually results in a full calculation. Saving a workbook in manual mode if the Calculate before Save option is selected. Formula and name evaluation circumstances A formula or part of a formula is immediately evaluated calculated , even in manual calculation mode, when you do one of the following: Enter or edit the formula.
Enter or edit the formula by using the Function Wizard. Enter the formula as an argument in the Function Wizard. Select the formula in the formula bar and press F9 press Esc to undo and revert to the formula , or click Evaluate Formula. A formula is flagged as uncalculated when it refers to depends on a cell or formula that has one of these conditions: It was entered. It was changed. It is in an AutoFilter list, and the criteria drop-down list was enabled.
It is flagged as uncalculated. The circumstances that cause a defined name to be evaluated differ from those for a formula in a cell: A defined name is evaluated every time that a formula that refers to it is evaluated so that using a name in multiple formulas can cause the name to be evaluated multiple times. Names that are not referred to by any formula are not calculated even by a full calculation.
Data table recalculation always uses only a single processor. Controlling calculation options Excel has a range of options that enable you to control the way it calculates. Figure 1. Calculation group on the Formulas tab To see more Excel calculation options, on the File tab, click Options. Figure 2. Calculation options on the Formulas tab in Excel Options Many calculation options Automatic , Automatic except for data tables , Manual , Recalculate workbook before saving and the iteration settings Enable iterative calculation , Maximum Iterations , Maximum Change operate at the application level instead of at the workbook level they are the same for all open workbooks.
Figure 3. Advanced calculation options When you start Excel, or when it is running without any workbooks open, the initial calculation mode and iteration settings are set from the first non-template, non-add-in workbook that you open. Automatic calculation Automatic calculation mode means that Excel automatically recalculates all open workbooks at every change and when you open a workbook.
Iteration settings If you have intentional circular references in your workbook, the iteration settings enable you to control the maximum number of times the workbook is recalculated iterations and the convergence criteria maximum change: when to stop. Workbook ForceFullCalculation property When you set this workbook property to True, Excel's Smart Recalculation is turned off and every recalculation recalculates all the formulas in all the open workbooks.
Figure 4. Setting the Workbook. ForceFullCalculation property Making workbooks calculate faster Use the following steps and methods to make your workbooks calculate faster. Processor speed and multiple cores For most versions of Excel, a faster processor will, of course, enable faster Excel calculation.
RAM Paging to a virtual-memory paging file is slow. Measuring calculation time To make workbooks calculate faster, you must be able to accurately measure calculation time. Iteration If Application. If Application. In Excel and earlier versions, Excel built a calculation chain for each worksheet in each open workbook. This resulted in some complexity in the way links between worksheets were handled, and required some care to ensure efficient recalculation.
In particular, in Excel , you should minimize cross-worksheet dependencies and name worksheets in alphabetical order so that sheets that depend on other sheets come alphabetically after the sheets they depend on. In Excel , the logic was improved to enable recalculation on multiple threads so that sections of the calculation chain are not interdependent and can be calculated at the same time.
You can configure Excel to use multiple threads on a single processor computer, or a single thread on a multi-processor or multi-core computer. When a calculation encounters an asynchronous UDF, it saves the state of the current formula, starts the UDF and continues evaluating the rest of the cells.
When the calculation finishes evaluating the cells Excel waits for the asynchronous functions to complete if there are still asynchronous functions running. As each asynchronous function reports results, Excel finishes the formula, and then runs a new calculation pass to re-compute cells that use the cell with the reference to the asynchronous function. Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments if it takes any has changed.
Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates.
For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly. Excel only learns that a UDF is volatile when it first calls it. A volatile UDF can be changed back to non-volatile as in this example. It also enables you to switch on and off the volatile status of a worksheet function. You can turn this default state off using the xlfVolatile function when the UDF is first called.
When calculation is set to automatic, recalculation occurs after every data input and after certain events such as the examples given in the previous section. For very large workbooks, recalculation time might be so long that users must limit when this happens, that is, only recalculating when they need to. To enable this, Excel supports the manual mode. Data tables are special structures in a worksheet. First, the user sets up the calculation of a result on a worksheet. This depends on one or two key changeable inputs and other parameters.
The user can then create a table of results for a set of values for one or both of the key inputs. The table is created by using the Data Table Wizard. After the table is set up, Excel plugs the inputs one-by-one into the calculation and copies the resulting value into the table.
As one or two inputs can be used, data tables can be one- or two-dimensional. Recalculation is handled asynchronously to regular workbook recalculation so that large tables might take longer to recalculate than the rest of the workbook.
Circular references are tolerated. If the calculation that is used to get the result depends on one or more values from the data table, Excel does not return an error for the circular dependency.
Given the different way that Excel handles recalculation of data tables, and the fact that large tables that depend on complex or lengthy calculations can take a long time to calculate, Excel lets you disable the automatic calculation of data tables. To do this, set the calculation mode to Automatic except Data Tables.
0コメント