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.
Things are going to get interesting!
1 – Commenting Best Practices
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.
2 – Teaching
When it comes to teaching complex formulas to students, Mike explicated a set of techniques he uses to make the process of learning easier:
- Build the complex formulas from scratch in front of the students. It should be built inside out, starting from where it makes the most sense.
- Divide up different parts of a complex formula in separate cells. This is done with the aim bringing them together at the end.
- One should use F9 frequently to demonstrate what bits of a formula are doing. Make sure to undo the change by using CTRL + Z.
- Use Formula Evaluator (under FORMULAS tab) to show how small chunks are coming together.
3 – Working with Complex Formulas
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.
4 – Top 3 Formulas / Functions
When asked about top three array or data pulling formulas / functions, Mike named the following three: SUMPRODUCT, the combination of INDEX-MATCH and LOOKUP.
What’s next?
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.