How Excel calculates?

How Excel calculates is seldom asked question, because we perceive this operation as something that is implicit. After all, how much can drive a car while never knowing how’s the engine running ? However, there is a situation where the calculation method can be very important: when the data is downloaded from external sources or when we perform complex calculations of the ones that are important to use the full power of the CPU.

Default settings are that Excel calculates automatically.Formulas ribbon we can set the calculation options by choosing from the Calculation Options menu, where we choose whether the calculation is done automatically (Automatic), except for the data in the data tables, usually originating from external sources (Automatic Except for Data Tables) or on request (Manual). By clicking the Calculate Now button or by pressing the F9 key, we perform manual calculations, and by clicking Calculate Sheet we perform manual calculations for the current worksheet.

We can set the options for calculation after clicking on the File tab, further we go to Backstage View and by clicking on Options, we open Excel options. The Formulas section contains options for setting the calculation method. Here we can also indicate the possibility of iterative calculation (Enable Iterative Calculation), as well as to indicate the number of iterations in each clculation (Maximum Iterations) and the maximal change value (Maximal Change). For example, if we choose a manual calculation method, we assume that the number of iterations is 100, and in the cell A1 enter the formula = A1 + 1, if the value of this cell was 1, after clicking the F9 key or selecting Calculate Now, the value of the cell will become 101 This is a simple example of a recursive formula, and for some complex calculations a much more complex formula is used, where the power of this option comes to light.

In Excel options, within the Advanced group, there is a possibility to choose whether the calculation is done using one or more processor cores. This is achieved by highlighting the Enable multi-threaded calculation field, and then selecting the number of cores that participate in the calculation. It is useful to know that this option exists, but nevertheless it is used relatively rarely in very complex calculations.

Excel, according to its basic settings, completes rounding to 15 decimals. No matter how many decimals we’ve shown, after setting the number display format, the rounding method does not change. It can be awkward because it can happen that some of the decimals that are not visible on the screen can be collected. This can be changed by selecting the Set precision as displayed field in the Advanced group of options in the section called When Calculating This Workbook. In this way, when we change the number of decimals, Excel completes in relation to the visible number of decimals. That is, only what can be seen can result in the result, not the hidden decimals.