Microsoft Excel 2010 Advanced Training

How To Take This Class

Live Instructor-Led Online Class

Cost: $225.00

  • Open enrollment class for individuals
  • Live class with an instructor
  • Free class retakes forever!
  • Six months of instructor email support
  • Hands-on exercises and student labs
  • Classes never cancelled due to low enrollment
  • Money-back guarantee

Onsite or Offsite Group Training

Cost: Based on number of students

  • For groups as small as 3 people
  • Class Held at our location or yours
  • Hands-on exercises and student labs
  • Customization at no extra charge
  • Six months of instructor email support
  • All-inclusive pricing
  • Money-back guarantee
Request Group Pricing Proposal

Course Duration

1 Day

What Students Receive

Each student will receive:
  • A printed courseware manual with hands-on exercises and samples for use during and after class.
  • A PC headset for communicating with your instructor and fellow students during your Excel 2010 Advanced Training Class
  • A course completion certificate upon successful completion of your Excel 2010 Advanced Training course
  • Six months of post-class email support from a Excel 2010 Advanced Instructor

Course Description

In this Microsoft Excel 2010 Advanced Training course, students will automate common Excel tasks using Macros, apply advanced analysis techniques to more complex data sets, use VLOOKUP and HLOOKUP to find values in worksheets, and share Excel data with other applications.

Course Objectives

Upon successful completion of this course, students will be able to:
  • Use the IF and SUMIF functions to calculate a value based on specified criteria; use a nested IF function to evaluate complex conditions; and use the ROUND function to round off numbers;
  • Use the PMT function to calculate periodic payments for a loan; use Date and Time functions to calculate duration in years, months, and days or time; display, print, and hide formulas; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
  • Use the VLOOKUP and HLOOKUP functions to find values in worksheet data; use the MATCH function to find the relative position of a value in a range; use the INDEX function to find the value of a cell at a given position in a range; and use data tables to project values.
  • Use the Data Validation feature to validate data entered in cells; and use database functions to summarize data values that meet criteria you specify.
  • Export data from Excel to other formats, and import data from a text file into an Excel workbook; import XML data into a workbook, and export data from a workbook to an XML data file; and use Microsoft Query and the Web query feature to import data from external databases.
  • Use the Goal Seek and Solver utilities to meet a target output for a formula by adjusting the values in the input cells; use the Analysis ToolPak to perform statistical analysis; and create scenarios to save various sets of input values that produce different results.
  • Run a macro to perform tasks automatically; record macros; assign a macro to a command button and a button in the worksheet; use a button to run the macro; create an Auto_Open macro; edit a macro by editing VBA code; and create a custom function to perform calculations when built-in functions are not available.

Course Audience

This course was designed for students desiring to gain the skills necessary to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data.

Course Prerequisites

Students should have completed, or have experience with, topics covered in the Microsoft Excel 2010 Introduction and Intermediate Training courses.

Course Syllabus

  1. Logical and statistical functions
    • Logical functions
    • Math and statistical functions
  2. Financial and date functions
    • Financial functions
    • Date and time functions
    • Array formulas
    • Displaying and printing formulas
  3. Lookups and data tables
    • Using lookup functions
    • Using MATCH and INDEX
    • Creating data tables
  4. Advanced data management
    • Validating cell entries
    • Exploring database functions
  5. Exporting and importing
    • Exporting and importing text files
    • Exporting and importing XML data
    • Getting external data
  6. Analytical tools
    • Goal Seek and Solver
    • The Analysis ToolPak
    • Scenarios
  7. Macros and custom functions
    • Running and recording a macro
    • Working with VBA code
    • Creating functions