How to create resilient Excel models using spilling Dynamic Array Formulas for automation purposes – and not just only for SlideFab
When it comes to programming most coders or programmers accept that code and data need to be separated. “Avoiding hard-coding” is a common best practice in this regard. Unfortunately, when it comes to Excel this idea hasn’t prevailed yet. Even when it comes to VBA coding within Excel many developers still rely on intermingling code into huge XLSB files, wondering why performance and maintainability are so poor. However, this post is not about writing VBA add-ins for Excel (using XLAM files).
This post is about keeping data and calculation logic (instead of code) separate within mere Excel files. Basically an outrageous idea, as “dragging formulas” and “adapting ranges” are instantaneous associations for most Excel users once input data changes. I will explain how Dynamic Array Formulas will help to build highly robust Excel models which will adapt to changes in the raw data automatically.
Accordingly, the concepts provided here are ideal for Excel calculations that need to last, like survey evaluations or corporate reporting. Both have in common that data structure remains rather similar while new surveys or new months are just around the next corner, respectively.
Disclaimer: What you will find here is my personal opinion based on more than 10 years of Excel and VBA experience. You might have a different opinion. But maybe you will learning something new nevertheless 😉Read More »How to create resilient Excel models using spilling Dynamic Array Formulas for automation purposes – and not just only for SlideFab