I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Are you an Excel Expert?

"All men of action are dreamers." [James G. Huneker]

[Please also look at my page Are you a good programmer?]

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...

Excel Area / Level of expertise

What you should know

Leading others up the garden path

Areas / 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 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 Peltier's site

Worksheet functions / Expert

Profile sheets with FastExcel ©

Use worksheet formulas for the sake of it, even if they show quadratic runtime. Example of exaggerated usage of worksheet formulas: Excel Formeln

Learn VBA mate, learn it

VBA / Expert

Add-in usage like 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 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. A bad example: Morefunc. I hope that Charles Williams will find a good solution to ensure FastExcel © will have a long lifetime

 

Possible Excel © careers and one suggestion or example of a reasonable career path:

20110226_PB_01_Excel_Careers_Screen
Sulprobil   Get it done   Contact   Disclaimer   Download