A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
At the PASS Business Analytics Conferences, I spoke about building better Excel models. Some of this was technical, but a good portion of my advice had to do with thinking about problems differently. Indeed, I suggest we must think differently about development specifically.
There were three objectives I discussed:
Let’s go through each.
Development memory refers to our ability to create models that naturally capture all that we’ve done in developing our models. We must think about this for when we hand off the model to someone else—or when we look at it again having not seen it for six months.
Development memory can be increased by making things less complicated. For instance, breaking up complicated nested IF’s can go a long way into making the underlying process more easily understood. Another concept I advocate is using proper names for named ranges. Following this trajectory, instead of giving a range the name, valDCF, we could simply call the range, DiscountedCashFlowsInput. The latter is much easier to understand, and the title is not obscured needlessly by abbreviation or Hungarian notation.
Model transparency refers to traceability of the model. We know most spreadsheets have errors. While we often develop ways to handle and avoid errors, we must also develop in a way that exposes these errors. That is, a proper model setup most ramify errors throughout the entire model. If there is an error in the backend data, it must be carried through all the way to the front end. This will expose the user to the error and help the developer trace where it began. Model transparency is greatly helped by connecting the model by way of formulas (over VBA) to the extent possible. So if one formula develops an error in the formula chain, we can use formula auditing tools to find the problem.
Model efficacy refers to the confidence we have in the model to deliver on its promise. Good data visualization and design helps build confidence in the model by allowing results to be easily understood. More important, efficacy is also about trusting your own ability to show results with making things needlessly complex or too simple. You don’t need to explain everything to your audience like they are in fifth-grade but you shouldn’t also making things complex to fulfill a self-indulgent need. If you respect your audience, you should feel comfortable delivering model results to their level.
I firmly believe if we make these items objectives to our own models, we’ll be better off building work that does what it’s supposed to do.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide