Excel Mastery is What Exactly?

I used to think that the more Excel formulas you knew and the more adept you were at macros, the more you could claim Excel mastery.

I have changed my mind.  Those who create complex spreadsheets that are beyond the skill and understanding of their users are pushing the program beyond it’s useful limits.

Spreadsheets are ideally set up so that

• calculations are transparent and easy to follow
• there are no hard-coded variables within formulas
• variables are defined in a separate section or page
• lookups are used rather than deeply nested “if” statements

I have written macros, I’ve used arrays, I have used multi-nested IF statements.  The best I can say is that I know these things exist and I can always google them for a refresher course if necessary.  A spreadsheet that is constructed to minimize the possibility of errors is far more valuable than one that dazzles us with its advanced Excel wizardry.

I would much prefer three or four columns of easily understood operations that break a complex calculation into chunks than one column of nested IFs.  Calculation columns can be grouped.  An expansion button is then available to look at or hide calculations, as needed.

Most people do not receive training for this sort of thing. If you even get on-the-job training it is generally instruction on how to use certain features, not how to best design a spreadsheet.

I highly recommend the Best Practices Modeling web site, and the Spreadsheet Standards Review Board.  You really shouldn’t develop spreadsheets until you understand these construction basics.

So what is my current definition of Excel mastery?  It is knowing how to design a spreadsheet for maximum accuracy, auditability and user understanding.  The simpler the better.