SYSTEMETRICS

HANDS-ON, PERSONAL COMPUTER SEMINAR

Intermediate Microsoft Excel - Level 2

Duration: 1 day (6 hours)

Prerequisite(s): Intermediate Microsoft Excel or equivalent knowledge

Environment: Microsoft Windows

Version(s): All, including Excel 2007. Taught using Excel 2003.

Support: Upon completion of this seminar, the student will be entitled to unlimited, free telephone support in Microsoft Excel.

Who Should Take This Seminar: Intermediate Microsoft Excel - Level 2 is designed to enhance the student's knowledge of the many advanced features of Microsoft Excel, the popular spreadsheet package. Students who take this seminar will develop a greater understanding of formulas, data manipulation and analysis, controlling the Excel environment, and programming. Students should have familiarity with the concepts covered in Intermediate Microsoft Excel, including creating formulas using functions, particularly IF and VLOOKUP, using multiple sheets, and AutoFilter. Those uncertain whether this seminar is appropriate for them should call Systemetrics at (401) 275-2200.

Seminar Outline

  1. Managing Worksheet Data
    1. Using range names
    2. Using PivotTables
    3. Using AutoFilter
    4. Converting formulas to values
    5. Pasting cell contents without cell formats
    6. Restricting types of data entered into specific cells
    7. Creating drop-down lists within cells
    8. Designing your worksheets for maximum flexibility
  2. Protecting cells, worksheets and workbooks
  • Creating Powerful Formulas
    1. Creating formulas using functions
    2. Using math functions: ROUND, ROUNDUP, ROUNDDOWN
    3. Using logical functions: IF, AND, OR
    4. Using lookup and reference functions: VLOOKUP, HLOOKUP
    5. Using the database functions: DSUM, DCOUNT, DCOUNTA, DAVERAGE
    6. Using information functions: ISBLANK, ISERROR, ISNUMBER, ISTEXT, IFERROR
    7. Designing your worksheets for maximum flexibility
  • Building error-checking formulas
  • Wtiting formulas to avoid error results, such as #DIV/0!
    1. Creating custom cell formats
    2. Hiding row and column headings and sheet tabs
    3. Hiding rows and columns
    4. Using multiple windows in the same workbook
    5. Using conditional formatting
    6. Using text boxes and other tools
  • Automating Workbook Activities Using Macros and Visual Basic for Applications
    1. Recording macros
    2. Running macros
    3. Editing macros/Visual Basic for Applications code
    4. Using message boxes
    5. Creating macro buttons
    6. Creating user defined functions

    SYSTEMETRICS, INC.
    95 Sockanosset Crossroad
    Cranston, RI 02920

    Telephone: (401) 275-2200
    Home Page: www.systemetricsinc.com
    Email: training@systemetricsinc.com

    All rights reserved.