I created a monster spreadsheet that nobody else in my company will ever figure out. It’s so complicated, that I won’t even remember how to use it when I come back from vacation.
When speaking with colleagues, I frequently hear gripes about Excel and the argument that third party software is a better solution to budgeting and financial analysis than Excel. Many business intelligence and budget software packages also advertise around this line, as the simple and straightforward “solution” to spreadsheets. Excel is often treated as a volatile beast or your worst enemy.
I don’t necessarily disagree with Excel’s detractors; it can be your worst enemy in many situations. Spreadsheets are created with the best intentions, to slice data and present it in various ways for simple reports. As these simple reports make their way up the management ladder, various requests may trickle back down demanding that you pivot the data into various ways or add new data categories that didn’t previously exist. In some cases, this may lead to a change in scope from the original plan, requiring extensive spreadsheet modifications and work-arounds to achieve the desired result. It’s often around this point where a simple report becomes a monster spreadsheet, and by design, your worst enemy.
However, Excel can be—and likely is—your most valuable and versatile tool. Where third party applications work to control how users interact with the program, Excel offers flexibility and customization. Almost every time my company purchases a new third party program to replace Excel, I find myself going back to Excel either partially or fully to have full control over my data.
While it can be difficult and time consuming to build a spreadsheet that properly manages Excel’s drawbacks, if done right, we can create stable and user friendly spreadsheets that solve complex business challenges.
Within this blog, I hope to introduce readers to proper techniques for managing complex Excel applications. I define an Excel application as a spreadsheet that is used on a recurring basis to manage large sets of data. The application should be easily transferable to new users, and it should be flexible enough to manage minor institutional changes. This will guarantee the Excel application’s longevity, ensuring that a project you spent 100+ hours on will last for months/years/fiscal years into the future.
My intention is that this blog will be relevant to users of all levels. I hope to provide code snippets that beginner and intermediate users can easily copy/paste to the VBA editors, while providing enough explanation and context for advanced users to go in and make tweaks to meet their needs.
I would like to thank you for visiting this blog. I hope you find its content as both a learning resource and a future reference.