If you have ever edited a long formula with many nested functions, then you must remember how difficult it is sometimes to understand its structure, the order of the arguments, the number of opening-closing brackets, etc. This is especially true for some array formulas:
As a rule, only its author can quickly understand such “beauty”. And even then – if not more than a week has passed. For the rest, working with such a formula is a real torment and usually happens according to the principle “it works – don’t touch it.”
Programmers, however, long ago found a way to conveniently represent nested objects (processes, loops, calculations). In most programming languages (including VBA), two main techniques are successfully used for this:
- indentation system on the left when writing code – each level of indentation indicates its degree of nesting
- comments to explain complex or non-obvious code snippets
Something similar can be depicted with formulas in Excel.
Hyphenation and indentation
To break a long formula into several separate lines, for example, by individual functions or arguments, you can use the keyboard shortcut ALT + Enter, after placing the cursor in the formula bar in the right place. The formula bar itself (starting with Excel 2007) can be easily increased in height by dragging the bottom edge:
Indentation between functions and arguments can be safely done with the space key – this will not affect the formula in any way and will not lead to errors in calculations:
Comments
Notes or comments on formula fragments can be made using the function Ч (N), which turns any text into zero, i.e. will not affect the result in any way.
If the formula or argument should give out not a number, but a text, then it is no longer possible to plus the function H – we will get an error VALUE, because You can’t add numbers and text. To comment on text, you can use the function instead REPEAT (REPT). It, in theory, repeats the given text the Nth number of times, but we can set N equal to zero and glue the resulting empty string to our formula using the glue symbol &:
Together, all of the above can significantly facilitate the understanding of complex formulas in “hard cases”:
I admit that to some this will seem like “cluttering up” the formula with redundant information, an unnecessary complication. Unfortunately, there is no more convenient way to improve the visibility of complex formulas in Excel yet. But if I have to face in someone’s file with a three-story formula of five levels of nesting, then I would prefer that its author use something similar to the above.
- Hide and seek with formulas
- Convenient simultaneous viewing of formulas and results
- Improving the visibility of tables with a color map from the PLEX add-on