As Excel experts, we are constantly in need of using complex formulas to get tasks done faster and elegantly. But this can prove to be troublesome for the users if they want to understand the formulas or change something in them. One solution is to use comments extensively, but they can become messy really quickly. So what’s the way out?
There’s nothing to worry about. Our panel of experts is here to guide us. We have Jordan Goldmeier facilitating the session in the presence of Rick Grantham, Oz du Soleil and our special guest Mike “ExcelIsFun” Girvin.
Oz and Mike shared their practice of commenting on formulas. Whenever possible, Oz employs text boxes to contain the comments on a separate page. Also, his comments are high-level, explaining the approach, and do not delve into individual formulas. Mike said that he tends to include much detailed explanations of how the formulas are calculating around the cells, either on the sides, or above or below them.
As an alternative to commenting, Jordan suggested extensive use of line spacing and line breaks (by using ALT+ENTER) to lay out the formulas, not dissimilar to how code is laid out in VBA.
When it comes to teaching complex formulas to students, Mike explicated a set of techniques he uses to make the process of learning easier:
On this topic, all our experts pooled in their insights from their experiences. Oz pointed out that many a times the math is quite straight-forward and simple, and it’s the error-handling part that makes formulas complex. Rick suggested that breaking such formulas apart, like Mike suggested, makes commenting unnecessary in many cases.
Jordan emphasized using of named ranges (explanatory names without using abbreviations) in aiding the process. They make complex formulas much easier for everyone to understand. In the same vein, Mike pointed towards the usefulness of table formula nomenclature.
When asked about top three array or data pulling formulas / functions, Mike named the following three: SUMPRODUCT, the combination of INDEX-MATCH and LOOKUP.
Try some of the suggestions our experts have put forward. And share with us the solutions you use in dealing with such problems.
Also, do not forget to share these nuggets of wisdom with your friends and colleagues.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.