UPDATE: Please also have a look at the blog post explaining how to build a resilient Excel model using Dynamic Array Formulas and spilling ranges. It’s worth a look
There are myriads of things which are doable with Excel. And for each of these things, there is another myriad of possibilities on how do it, exactly. In fact, many Excel design approaches lead to some kind result. But when it comes to changeability, maintainability, understandability, robustness and calculation performance some of these approaches are better than others. So, what is important when creating an Excel workbook, actually?
Even though Excel has been around for 30+ years and even though many people have lots years of experience, there is no widespread understanding on what is actually important when designing an Excel model. This is also true, when it comes to creating Excels for PowerPoint automation with SlideFab 2. What might work somehow OK in a normal Excel might not be sufficient when designing it for automation purposes: For such cases the data and formulas need to be structured in a very clean and consistent way. This is true for SlideFab 2 but also for other VBA-coded macros which rely on a certain structure for the automation.
Even though building a structured and clean Excel workbook might take a little longer in the beginning, it typically pays off in many ways. In particular, when underlying data changes a little bit. Or when there is a small adaption to some calculation. Then you see the difference between a good and a weak workbook design. The weak workbook design will be much harder to change and is more prone to errors. (For all who know some VBA: Have you ever experienced that changing a worksheet a little bit, has numerous consequences for the VBA code, too? If so, then the VBA code was poorly written. Sorry, to say that. Better think twice before starting to code some VBA macro. So, why not starting with a good workbook design from the very beginning.
In order to empower users to create better Excel models – for SlideFab 2 and also in general – I took some time to create an overview of best practices. The recommendations regarding best practices are based on many years of Excel and VBA-programming experience during which I built hundreds or maybe thousands of Excel models. The mistakes I made don’t have to be yours. These recommendations give a clear answer to the question regarding what is important when creating an Excel model.