Calculate formulas only once

Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Make sure that you calculate formulas only once!

Time consuming and error prone

A good example is a calculation for inflation, which is the needed in several other formulas. Spreadsheets live a long time, and during this lifespan, your calculation of inflation is most likely going to change. When the calculation is repeated in multiple formulas, and the inflation changes, you will need to go back and adjust all of those formulas. This process is not only time consuming, it is also likely to result in errors. You could easily forget to modify the inflation formula in one or more locations.

Don’t slow down your spreadsheet

Furthermore, calculations that are repeated as part of a formula make your spreadsheet slower, this is because Excel really calculates each formula, and it will calculate it as many times as you write it out.

The solution

A better option is to place the sub formula in a separate cell, and refer back to that cell in the other formulas that make use of it. This has several advantages. Firstly, the spreadsheets will be easier to maintain since you only have to adjust one cell in case of a change. Secondly, the spreadsheet is easier to understand as the formula is placed separately, and you can now add a label to describe it, and you don’t need to search for it in other formulas. Finally, when you place the formula in a cell of its own, Excel calculates the formula only once and then “sends” the results to the cells that use it. This leads to a more efficient calculation and your spreadsheet does not get unnecessarily slow.