Abstract
“All men of action are dreamers.” [James G. Huneker]
Everybody likes to be seen as a competent, knowledgeable person. But expertise is relative. You might think you know something perfectly fine. But in the eyes of another person you might look like a beginner.
I like to call a spade a spade. I like to use the whole spectrum of judgments from beginner to expert, from reasonable usage to dazzling exaggerations which lead people up the garden path. Obviously this is based on my own, subjective view, on my education and my knowledge and on my valuation system. Here we go…
Are you an Excel expert?
| Excel Area / Level of expertise | What you should know | Leading others up the garden path | Areas or levels you might want to develop into |
|---|---|---|---|
| Worksheet functions / Muppet | Not to format all cells right aligned: cell format General shows strings left aligned and numbers right aligned; not to have erroneous name definitions | Learn about functions like IF, TEXT, DATE, VLOOKUP | |
| VBA / Muppet | Not to name functions or subs identically to modules | Record macros | |
| Worksheet functions / Beginner | 1<>“1”, functions: CHAR, DATE, MID, ROUND, TEXT, TODAY, VLOOKUP | Showing off with formulas you do not understand | Learn about functions like SUMPRODUCT, understand volatile functions |
| VBA / Beginner | How to record macros and to copy others' code fragments, integers can only hold values from -32768 to 32767; use OPTION EXPLICIT | Learn to clean up recorded macro code, use Reddick naming convention or similar | |
| Pivot table / Beginner | Create one-off standard Pivot tables | Learn some VBA to automate Pivot table usage (data feed, refresh) | |
| Charts / Beginner | Create one-off standard charts | Learn from (external link!) Peltier’s site | |
| Worksheet functions / Intermediate | Look up columns to the left with INDEX(MATCH()), when to use Pivot tables or VBA code instead of worksheet formulas, avoid volatile formulas | Use INDIRECT, use formulas which exceed two rows in the formula editor | Learn about proper spreadsheet design, learn some VBA |
| VBA / Intermediate | Develop special-purpose code, use mainly simple file I/O | Learn when to apply a Pivot table and when worksheet formulas will suffice | |
| Pivot table / Intermediate | Refresh them with VBA | ||
| Charts / Intermediate | Use advanced chart features | Learn even more from (external link!) Peltier’s site | |
| Worksheet functions / Expert | Profile sheets with (external link!) FastExcel by Charles Williams | Use worksheet formulas for the sake of it, even if they show quadratic runtime | Learn VBA mate, learn it |
| VBA / Expert | Add-in usage like (external link!) Fincad, use classes where reasonable, develop general purpose (re-usable) code | Learn to understand and to develop classes - see SystemState and Logging | |
| Chart / Expert | Auto-update with worksheet formulas or VBA, a good site is (external link!) Peltier’s | ||
| VBA / Wizard | Program classes - see SystemState and Logging | Don’t get stuck with flat file I/O. Learn to read & to write from / to databases | |
| Excel Connectivity / Wizard | Use database connections efficiently | ||
| General / Wizard | Add-in development | Do not publish your source code, do not care about succession = do not ensure people can use your solutions when you will be gone. |
Excel Careers
Possible Excel careers and one suggestion or example of a reasonable career path:
