Excel topics you'd want to teach a Noob you were mentoring

[Oaktree]

I haven't decided yet if I want to writeup (and steal writeups for ) these items yet, but I wanted to make a list of things that I'd teach a Noob about Excel. Not an "all-inclusive" list by any means, and not a book that's 100s of pages long. I think J-walk, Bill Jelen, Steve Bullen, etc. fill the need for books...

But, I kept thinking that I wish there was a (somewhat short) list out there somewhere that had things to teach the "new guy" that would make his job easier and combine some of the bigger lessons learned and things we've seen on this board. Perhaps there's a better one out there and I'm just reinventing the wheel...?

I came up with this and was curious as to what I've missed that you would have included:

1.  Navigation
   a.  Shortcut Keys and how learning them can save you oodles of time
   b.  F5 is your friend
   c.Freezing Panes
   d.PasteSpecial
2.  Cosmetics
   a.Number Formatting
   b.Conditional Formatting
   c.  The few cases where it may be appropriate to use merged cells
   d.  F4 is your friend
   e.  Formatting cells outside your used range will cause your file size to balloon
3.  Printing
   a.Print area
   b.  Fit to
   c.  Repeating rows/columns on each page
4.  Entering formulas
   a.  Using F9 to audit formulas
   b.F2
   c.  CTRL { and CTRL }
   d.Trace Dependents / Precedents
5.  Dates and Time
   a.  =A2 - A1 calculates the # of days between two dates
   b.  NETWORKDAYS function
   c.Weekday
   d.  EDATE/EOMONTH
6.  Calculating and summarizing
   a.Conditional calculations
       i.CountIf
       ii.SumIf
       iii.SumProduct
       iv. How to use an array formula to do AVERAGEIF, PERCENTILEIF, etc.
   b.  How the manual/automatic calculation setting works and affects other workbooks
   c.  SHIFT+F9 is your friend with large workbooks
   d.Data   ' subtotals
   e.  Add something about random numbers here
   f.  The few cases where it may be appropriate to hard code data
   g.Goal Seek And Solver
7.  Retrieving data
   a.Filters
       i.AutoFilter
           1.  The SUBTOTAL function
           2.  Alt + ; is your friend
       ii.Advanced Filter
           1.  Getting unique records
           2.  I'm debating whether I want to go further with this
   b.Linking Cells
       i.  Why this is a great thing
       ii. Why this is a terrible thing
   c.VLookup
       i.  The difference between an "Exact Match" and an "Inexact match" and the assumptions for each
       ii. How to use { , ; }'s to put a list in your formula
   d.  INDEX/MATCH
   e.Choose
   f.INDIRECT
       i.  Warning for needing to keep other referenced workbooks open
   g.  LOOKUP and returning the last item
   h.Array formulas
       i.  Match on two columns
       ii. TRIM/LEFT within VLOOKUP
   i.Pivot tables
       i.What they  're for
       ii.Grouping dates
8.  Charts
   a.  Linking chart aspects (heading/etc.) to cells
   b.  Add more stuff here
9.  Named ranges
   a.  Dynamic named ranges
10. BOOK.xlt, SHEET.xlt, and PERSONAL.xls
11. Security
   a.Data Validation
       i.Dependent lists
       ii.Input messages
   b.Protecting Worksheets / Workbooks
       i.  How to
       ii.Limitations
       iii.xlVeryHidden
   c.  Locking your code
   d.Digital Signatures
       i.  Scheduling automatic tasks
12. Do I want to go into details of the data analysis offerings here…?
13. VBA
   a.Events
       i.Open
       ii.Change
       iii.Calculate
       iv.BeforeSave
       v.BeforePrint
   b.Input box And MsgBox
   c.  Add something about the macro recorder here
   d.BackgroundQuery = False
   e.  A short introduction to userforms
14. References
   a.Obviously , www.mrexcel.com / board2
   b.  http://www.mrexcel.com/board2/viewtopic.php?t=131224 (recommended add-ins and links)
   c.www.msdn.com
   d.  http://support.microsoft.com

Thanks for reading,
Cheers,
Matt

 

[Jay Petrulis]

1. The distinction and proper use of absolute/relative cell referencing is a must. Also, F4 for toggling between the options (make sure they know the other use of F4 to repeat a previous action).

2. Don't expand any more than unique records for the Advanced Filter option.

3. Get rid of the Events section in VBA -- go with the basic subroutines only at first.

I would expand on the reason for VBA in the first place -- initially to automate repetitive tasks, then to expand the capabilities of the program and user.

Above all, stress that to use VBA wisely, the user must have a good handle on Excel as a standalone product. S/he should be comfortable building Excel models before using VBA. Why try to run before you can walk?

4. Use of the Indent option and Alt-Enter!!!!! If I see one more workbook where the spacing is done by using the space bar, I may go postal.

 

[Joe4]

Matt,

I would include using Text to Colums and importing Text Files.

Also, if it is a Excel NOOB, I probably would not get into VBA at all, other than the Macro Recorder and running pre-written Macros (and discussing Macro Security Levels). That is more of an intermediate skill, in my estimation.

 

[Oaktree]

Good call, guys.

I agree with your points, though I'm still on the fence with VBA. It's almost too important to leave out all together, too complex to go into any detail with, and brushing over it with just the macro recorder doesn't seem quite right either. Goldilocks syndrome, I suppose.

Nice Statler and Waldorf retro throwback, Jay.

 

[Joe4]

Matt,

I think VBA is a great thing to teach and learn, just not in a beginner class. Maybe have a follow-up class for that, where you can really dive into it.

I don't know the demographic of your environment, but I have taught beginner classes at my work, and I don't think most of them would understand or do well with VBA. Formulas are hard enough for them to figure it.

I guess it really depends on the audience you are trying to target...

 

[tusharm]

Commendable effort. But, I suspect it will go down the road of so many FAQs. No one reads them because the regulars don't insist on it. Nonetheless, if you do persist...

I prefer to emphasize doing things right the first time around. That influences my comments below.

#2: One place where MS broke its own rule about number formatting not affecting the kind of information is with the Text format. If a cell is pre-formatted as text, new information entered in the cell will be treated as text.

#4: The Evaluate button on the Auditing toolbar. Nothing better than it to figure out what XL is is actually doing with a formula. For an unintended introduction to the capability see
Array formulas and Excel’s “Evaluate Formula” capability
http://www.tushar-mehta.com/excel/tips/array_formulas.htm#_Array_formulas_and_Excel%92s%20%93Evaluat

#4: R1C1 addressing. IMO, a "must" for XL2007.

#4: *Mixed* absolute/relative addressing.

#7: I am not a big fan of array formulas for extracting data. Introduce people to a very powerful and highly unused tool: MS Query. See

An introduction to array formulas
http://www.tushar-mehta.com/excel/tips/array_formulas.htm
and
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

#8: Dynamic (self-adjusting) charts

#9: Names for ranges defined with relative addresses.

#9: Define names at the most restrictive scope possible, i.e., use worksheet names rather than workbook names whenever possible.

#9: Point out the Name Box (to the left of the formula bar).

#9: Name shapes by selecting them and entering a name in the Name Box. Naming an embedded chart doesn't help much. Instead name the container (called a chartobject). To select the chartobject, select any cell, then hold the CTRL or SHIFT key and click the chart.

#9: Use names to define cells and use them in formulas making the workbook model that much more readable.

#10: Not a fan of any of those approaches. IMO, these were mistakes on MS's part. I know I am in a minority of a minority but I would do this "right" and discuss templates and logically segmenting code into funtionally organized add-ins (rather than have everything dumped in personal.xls).

#10: The correct way to use a template is to create a new workbook based on the template, *not* open the XLT file and then do a "Save As"

#11: A major weakness with data validation is people copying data into those cells. For a more comprehensive validation scheme see
http://www.mrexcel.com/board2/viewtopic.php?p=1068023#1068023
and a soon to be published add-in on my website.

#13: Please emphasize the downside of VBA. Too many, including many regulars here, jump to VBA for problems that are best solved in XL itself.

#13: Option Explicit is not an option. It is mandatory. Set the appropriate setting in the VBE.

#13: Declare variables at the most restrictive scope possible.

#13: Use arguments rather than global variables to exchange information between procedures.

#13: Modularize code. I should share the concepts in a chapter I wrote for "VBA and macros for Microsoft Excel"

#13: Introduce OnTime. See
http://www.cpearson.com/excel/ontime.htm
except that RunWhen should be a Date not a Double

#13: Introduce UDFs. Too many including those who use VBA regularly, don't know enough about the power of UDFs. See
Excel formulas and User Defined Functions
http://contrarianview.blogspot.com/2006/02/excel-formulas-and-vba-user-defined.html

and another unintended and somewhat incomplete introduction to writing array aware UDFs:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html#from_worksheet

#13: If you must introduce events, point out the significant problems of worksheet and workbook events. See
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm

#13: Leverage the XL OM. Invest time learning it. F2 is your *best* friend in the VBE. See
Beyond Excel's recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

and

Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm

#13: Emphasize writing good code from the beginning. See
Case Study – Defensive Programming
http://www.tushar-mehta.com/excel/vba/vba-defensive_programming.htm

#13: Use mnemonics (MS and user-defined constants and enumerations) and not numbers whereever possible. See a page that is neither complete nor linked into the site navigation scheme:
Mnemonics or Constant Names
http://www.tushar-mehta.com/excel/vba/mnemonics.htm

#13: I hate InputBox and the method is rather user-hostile if one needs more than 1 piece of information. Have folks invest in developing a properly designed userform.

OK, I should have covered almost everything, though I doubt everything, I would have liked to. Not to mention that an hour+ on a topic that may not even get off the ground is not necessarily the smartest use of my day. {grin} But, I guess it reflects the passion I have for the subject.

 

[Jay Petrulis]

Nice Statler and Waldorf retro throwback, Jay.

Those guys are my heroes!

For those who don't know/recall, Statler and Waldorf were the two hecklers in the audience of "The Muppet Show."

Tushar has made a very nice list. If I were to teach a class on this, I would emphasize the "right" way to structure a workbook over the specific tools and techniques. A correct design helps a lot. Below are some additional thoughts.

1. Separate data from processing from output, although the calculation and output is often one and the same in Excel.

2. If you know that the model parameters will expand, build that in at the outset. As the model incorporates enhanced features, it is far better to have planned for that extra item(s) rather than adding it haphazardly in an out-of the way cell or column. Of course, no one always has unlimited foresight here, but steps can be taken to ensure that flexibility is gained.

3. Get the functionality first, then the formatting. I know that I may be in the minority here, but the presentation aspect of spreadsheet output is given too much thought to the detriment of the functionality, ease of use, maintainability, etc.

4. I have inherited/examined a number of spreadsheets where color coding conveys important information, but the scheme is not explicitly defined and listed anywhere. I would recommend against any data analysis that has color coding as a critical component of the logic (outlier data one exception). For final reports/presentation, coloring is fine, but not for data analysis.

5. Discuss consistency in formula building (don't mix within a row or column, for example).

 

[tusharm]

Oh, one more "must have" is data organization/architecture/structure. None of these structures that on their face are naively-appealing: no one sheet/book per person/week/whatever.

Put the data in a single table with an additional column that identifies the person/week/whatever. This should go between #1 and #2.

 

[SydneyGeek]

Some great ideas in here.

This is a very simple one: Extend a range to include a single blank column / row, press Alt = , and you have column / row totals.

Denis

 

[tusharm]

Another basic idea that doesn't seem the easiest to grasp. Shows up in many forms:

What's a string literal?
What's a zero length string?
When's a number not a number?
Why is an IP address not a number?
What's the difference between a string literal and a variable?
How does one take a string literal and replace a piece of it with a variable?
How does one get a double-quote into a string?