Spreadsheet Models…Why Talk About It Now?

Why talk about spreadsheets in the closing months of 2023? This blog answers that question and serves as an intro to a series on spreadsheets’ role in companies, how to use them with excellence and their role in the general topic of personal digital transformation. The series will include sharing gold standard approaches and open-source links to tools and trainings.

There’s a dirty little secret from consulting and corporate experience. Spreadsheet models are pervasive and powerful in most companies. They do both glamorous and mundane tasks. It’s possible to simultaneously embrace the latest AI tool and modeling software while making amazing and constructive use of great spreadsheet models. For many, cleaning up bad spreadsheet models is a great jumping off point to better efficiency and further advancements with digital transformation in general.

Within companies, Microsoft Excel bridges between technical people and business, project management and finance team members. Even for those working with advanced, R&D or business simulation applications, Excel is great for placing models and data in the hands of non-coders. Well-formatted spreadsheets are also useful as Python model outputs for giving data to non-coders for inspection and study. Google Sheets is in the game too, but Excel and especially the desktop client version of it is still paramount in most companies I encounter. Finally, as a rationale for hitting the spreadsheet topic head on, spreadsheets’ wide-open grid can be used for both good and evil. Looking at this positively, this makes Excel a great teaching tool for the general topic of digital transformation

Ironically, after saying those positive things about spreadsheets, awareness and expertise with them helps put appropriate limits on usage for situations where they are not the best tool. I recently staked out a written position on these limits in writing about The Right Modeling and Analysis Tools for the Job directed at a generalist engineer and scientist technical audience. I am teaching undergraduate engineers from this basis in their senior design course. The blog includes a significant section titled, “Don’t overuse Microsoft Excel.”

 No shame if you haven’t thought about these topics directly, but here are subtopics that may help you engage. I will retroactively link to these bullets as I write about them:

• There is a complex brand called Microsoft Excel. Microsoft Teams promotes a watered-down, online version. Which Excel are we talking about as the gold standard for building and using spreadsheet models? (and necessarily treading into the dangerous area of thoughts on Google Sheets versus Excel pros and cons)
• What are the problems with typical spreadsheet models within most companies? I refer to this as anchoring the conversation in “a standard of crap” or SOC. Bad spreadsheets cause problems! Sincere apologies to whoever I lifted the SOC term from. I do not remember, but it’s perfect in this context. For spreadsheets, knowing the SOC means being grounded in the problems caused by poor quality spreadsheets that haunt even great companies.
• What characteristics define a Standard of Excellence (aka SOE) for spreadsheet models and, quite frankly, an SOE for models in any other software? This SOE is great for model authors, but it’s also a checklist that managers can use to survey their current landscape.

• A pivotal topic for desigining great spreadsheet models and models in general: What is the difference between structured data and non-structured (but very necessary!) summaries of data? I use a multi-point definition for what makes data “structured.” Once you know it, you cannot unsee it, and you can apply it to any situation.
• What describes a great user interface for “data”? Most people have not thought deeply about what makes a rows/columns table easy to digest, but we have. One thing that causes people to deviate into SOC practices is desperately and pragmatically trying to find humane ways of simply looking at and working with their data. We have had  success creating great user interfaces that discourage needing to do that.
• Along the way, I will share additional general or “Tech Notes” blogs on specific tools and techniques for creating outstanding models. Consulting projects have led to development of general VBA macro tools for authoring and working with Standard of Excellence models. I will share and discuss the role of those tools too.

If you are interested in a quick start on improving your MS Excel mastery, our online Digital Transformation with Excel course is a great place to start. It is a one-workday investment to take but can be broken into shorter sessions with topics covered in short videos that refer to hands-on examples.