Intermediate Microsoft Excel

Instructor:   Paul N. Lee, [Owner/Consultant of Nahee Enterprises]

This course assumes you have taken the previous Introduction class, or that you have equivalent knowledge of Excel from other classes and/or personal experience.   The course focuses on referencing and formulas, but begins with a review of a few general Excel features.   Students are welcomed to bring data from projects they are currently working on to use as input during practice sessions.   Besides the class handout, a CD is supplied with 814 files of examples, templates, add-ins, training, help files, and tips/tricks (a total of over 225-MB of information).

The following is the outline used for this class:

  1. Brief Overview
    1. (see "Microsoft Excel Fundamentals" outline)

  2. Working with Worksheets and Workbooks.
    1. Referencing.
      1. The A1 reference style.
      2. Relative references.
      3. Absolute references.
      4. Mixed references.
      5. The 3-D referencing method.
      6. The R1C1 reference style.
      7. Switching reference styles.
      8. Circular References.
        1. Locating and Removing.
        2. Making it work.
    2. Data Forms.
    3. Grouping and Outlining.
      1. Preparing data to be outlined.
      2. Displaying and hiding outlined data.
      3. Automatic versus manual outlining.
        1. Outlining automatically.
        2. Outlining manually.
        3. Customizing an outline with styles.
    4. Named Cells.
    5. Filtering.
    6. Validation.

  3. Formulas.
    1. Operators.
      1. Arithmetic operators.
      2. Comparison operators.
      3. Text Concatenation operator.
      4. Reference operators.
      5. Operator precedence.
      6. Use of parentheses.
    2. Arrays.
    3. Functions.
      1. Date functions.
        1. TODAY and NOW.
        2. WEEKDAY and MONTH.
      2. Lookup and Reference functions.
        1. VLOOKUP.
        2. HLOOKUP.
      3. Logical functions.
        1. IF.
      4. Statistical functions.
      5. Text and Data functions.
        1. CONCATENATE.
        2. LEFT.
        3. TRIM.

  4. PivotTables.
    1. Microsoft Excel lists or databases.
    2. External data sources.
    3. Multiple consolidation ranges.
    4. Another PivotTable report.
    5. Changing an existing report's source data.

  5. PivotCharts.
    1. PivotChart terminology.
    2. Differences between PivotChart reports and regular, non-interactive charts.