Advanced Microsoft Excel 2010

Duration: 12 hours

Description: Students will work with more advanced functions including logical, date, time, statistical and financial, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about array formulas, data validation and database functions such as DSUM. They will learn how to import and export data, and how to query external databases. Finally, students will learn about the analytical features of Excel (such as Goal Seek and Solver) and how to automate Excel using macros.

Objective: The student will learn to use advanced features in worksheet applications.

Course Content:

Unit 1: Logical and Statistical functions Topic A: Logical functions Topic B: Math and statistical functions Unit 2: Financial and Date functions Topic A: Financial functions Topic B: Date and Time functions Topic C: Array formulas Topic D: Displaying and printing formulas Unit 3: Lookups and data tables Topic A: Using lookup functions Topic B: Using MATCH and INDEX Topic C: Creating data tables Unit 4: Advanced data management Topic A: Validating cell entries Topic B: Exploring database functions

Unit 5: Exporting and importing Topic A: Exporting and importing text files Topic B: Exporting and importing XML data Topic C: Getting external data Unit 6: Analytical tools Topic A: Goal Seek and Solver Topic B: The Analysis ToolPak Topic C: Scenarios Unit 7: Macros and custom functions Topic A: Running and recording a macro Topic B: Working with VBA code Topic C: Creating functions