### Course Overview

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 Prerequisites

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

### 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.

### What You'll Learn

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.

1 Day

### Course Outline

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
• 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