Keep a Formula Close to its Input
Place a formula close to its input variables. You have a lower chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else (what’s new?)
Optimal Use of the Support in Excel
If you modify a formula in Excel (F2), all the cells used in that formula will light up. This is convenient if you want to see what you are doing. But, this only works if all the cells used are on the same worksheet. Preferably you place the cells close to the formula itself, so you do not have to search and scroll and lose the context of the formula.
Less Chance of Errors
The chance of making a mistake in a cell or range in the formula is smaller if the range is close to the formula, because you see what needs to be done. And the other way around, perhaps even more important: if you change a cell or range, the chance that you will notice that this has consequences for one or more formulas is much greater if the formula is close to the data.
Calculate Everything 1 Time
What does this guideline have to do with “Calculate only once“? Quite simply, if a calculation is close to the variables, you can also do something in the design to show that things belongs together. Then, if you need the result of the same formula somewhere else and start recalculating it again, you will see that when you create the formula you have already calculated it. The same clarity you’ve made now, can be used if you transfer the spreadsheet to colleagues.
Input and Output Separated?
But is this not contrary to strict separation of input, calculations and output, preferably on different tabs? We admit, sometimes that is a bit of a problem. The clever solution is to ‘link in’ the source data by means of references and then apply the calculations to the references (in the spreadsheet). If possible, do this, as it also keeps the formulas short. But sometimes a spreadsheet is so big that it is better to leave out these kinds of clarifying solutions. Then we prefer to place the calculations on a separate tab and not on the source data. Guideline 4 goes beyond this guideline 13, in our opinion.
Unexpected Formula Location
Do you use PerfectXL? Then you will have noticed that PerfectXL does not give a risk message for any formula far away from the source cells. That is too strict, we must draw a line somewhere in what we can do for you. However, PerfectXL does warn against a very specific case of formulas far away from the source. We call this the ‘Unexpected Formula Location’ and it occurs when, in a long formula, there are considerably more references to a specific tab than within the tab of the formula itself. Then there is clear room for improvement: moving the formula to the more appropriate tab.