Calculate Formulas Only Once!

Sometimes, you need the exact same formula, based on the same numbers, in multiple places. Like a calculation for inflation, on which you base several other formulas. You might be tempted to calculate the formula twice, but resist this temptation: Calculate formulas only once!

Time Consuming and Error Prone

Excel Guideline 03: Calculate Formulas Only Once :: PerfectXL

Spreadsheets live a long time, and during this lifespan, your calculation of inflation is likely to change. When the calculation is repeated in multiple formulas, you will have to adapt all of them. This is time consuming and also error prone, as you may forget one or more of the copies. Furthermore, calculations that are repeated as part of a formula make your spreadsheet slow, since Excel will really calculate the formula or subformula twice.

Better Solution

A better solution is to place the subformula in a separate cell, and refer back to this cell in other formulas. This has several advantages. Firstly, the spreadsheets will be easier to maintain since you only have to adapt 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. 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.

Calculate Formulas Only Once - Slow Spreadsheet :: PerfectXL

All Subformulas in One Worksheet

When you have several subformulas that are used multiple times, it might be better to place all of them together on one worksheet called, for example “parameters” or “calculations”. This helps future users to understand and maintain the sheet.

Check your Spreadsheet

Do you want to check your spreadsheet for repeated formulas? PerfectXL highlights every calculation that is found more than once. Create a free account now and check it today! 

Calculate Formulas Only Once - Duplicate Formulas in PerfectXL :: PerfectXL

The image shows the PerfectXL overview of duplicate calculations in a spreadsheet, as one type of risk in the category ‘Structure Issues’

Never miss a Guideline!