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.
Slowing 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.
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.
Check your spreadsheet
You might be thinking, “well this might be true, but what about the spreadsheets I built before that still have these double calculations.” Well we have a solution for that! Our Excel tool, PerfectXL Risk Eliminator, highlights every instance where Excel calculates the same formula more than once, and notifies you as to where those calculations can be found and then changed.
- Avoid >70 types of risks
- Improvement suggestions
- Quality reports
Read more about risks in spreadsheets
On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.
Here you will find information like system requirements, release notes, and answers to most questions about our software.
VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.
Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.
Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.
Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.
(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.
Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.
The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.
Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.
It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!
It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.
Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.
To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.
Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.
Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.
Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files.