Spreadsheet good practice guidelines

Guidelines:

• Avoid using spaces and new lines in formulae.
• Any spaces and new lines in formulae do not cause unintended side-effects.

Adding spaces and and new lines to formulae can make them more readable. But they are not passive characters – they are the "intersection" operator, which can inadvertently change the result.

The intersection operator

For reasons that are not obvious, Excel uses the Space character as an intersection operator, meaning that two ranges separated by a space returns the intersection of those ranges. For example, =B5:F8 C3:E10 returns the range C5:E8. The Alt+Enter combination, which adds a new line in a formula, behaves in the same way. If the ranges do not intersect, then the result is a #NULL! error.

Because Space and Alt+Enter are not passive, adding them to a formula is risky. If it is not done carefully, then we can create subtle bugs. In general, it is best to not include spaces or new lines in formulae.

Uses of spaces in formulae

Users may put spaces in a formula for two main reasons:

• To make the formula easier to read, making it look more like how it would be handwritten or written in many other programming languages, with spaces between the parts.
• Adding structure to a formula, by putting parts of the formula on separate lines using Alt+Enter, and possibly indenting each line using spaces. This can make the formula look like the structure used in most programming languages.

Example of spaces in a formula

For example, the image shows a sales report, which has sales by branch for each of the last five quarters (all of last year, plus the first quarter of this year). We want to aggregate sales into a rolling 6 month total over all branches, where we can specify the starting quarter. Therefore, we use the formula:

=IFERROR(SUM(CHOOSE(C18, B5:C16, C5:D16, D5:E16 E5:F16)), 0)

In C18 we have specified the 6 months starting with quarter 2, and our formula returns total sales of 1789.71. We check this total by selecting cells C5:C16 and note that the sum shown on the Status Bar is the same, so all is good.

After writing the formula, we added spaces between some of the parameters to make our formula easier to read. Unfortunately, when adding a space between one pair of parameters, we selected the existing comma, which was then deleted when we pressed the Space key.

Importantly, Excel doesn't tell us that there's a problem. The formula, even with a missing comma, is still valid. However, it no longer does what we intended. Specifically:

• 6 months starting quarter 1: The formula returns 1732.44, which is correct.
• 6 months starting quarter 2: The formula returns 1789.71, which is correct.
• 6 months starting quarter 3: The formula returns 940.61, which is incorrect. Rather than summing D5:E16 (quarters 3 and 4), the formula sums the intersection of D5:E16 and E5:F16, which equates to E5:E16 (quarter 4 only). We might not notice that this result is less than expected.
• 6 months starting quarter 4: The formula returns 0.00, which is incorrect. The CHOOSE function returns a #VALUE! error because it does not have a [value4] parameter, as we combined the [value3] and [value4] parameters when we deleted the comma between them. But the error is suppressed by the IFERROR function, so we get zero instead.

The problem is fairly obvious in this example, because the formula is short. Though if we tested only the first two cases, then it is likely that the problem would not emerge until later. Even then, it might not be immediately recognized because the formula returns a number for the 6 months starting quarter 3, albeit a number that is too low.

But in a long and complex formula – the kind of formula where new lines and indentation could be useful – such an error can be hard to find. That's why it is best to not include spaces and new lines in formulae.

As an aside, having two quarters named 1 is not good. It doesn't cause a problem in this example, but nonethless it would be a good idea to improve the naming to eliminate the ambiguity.