Course Description

This 1-day Excel 2010 Advanced training course is for students who are already familiar with Excel 2010 at the Intermediate level.

While the course specifically references Excel 2010, it is also suitable for students who are using earlier or later versions of Excel.

After completing this course, students will know how 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.
  • This course will help students prepare for the Microsoft Office Specialist exam for Excel 2010 (exam 77-882). For comprehensive certification training, students should complete the Basic, Intermediate, and Advanced courses for Excel 2010.

 

Pre-requisites

Attendance at our Excel 2010 Intermediate training course or equivalent experience.

Who is this course for?

For Excel users who already possess intermediate-level skills in working with any version of Excel.

Further Training

The next training course in our Excel 2010 series is Excel 2010 VBA Programming for advanced users who need to add the power of Visual Basic programming to their Excel models.

Course content

Logical and Statistical Functions
Logical functions
Math and statistical functions
Financial and Date Functions
Financial functions
Date and time functions
Array formulas
Displaying and printing formulas
Lookups and Data Tables
Using lookup functions
Using MATCH and INDEX
Creating data tables
Advanced Data Management
Validating cell entries
Exploring database functions
Exporting and importing
Exporting and importing text files
Exporting and importing XML data
Getting external data
Analytical Tools
Goal Seek and Solver
The Analysis ToolPak
Scenarios
Macros and Custom Functions
Running and recording a macro
Working with VBA code
Creating functions