Duration: 1 Day
Audience: Information Workers
Delivery Method: Instructor-led
Software Assurance Value: N/A
Microsoft CPE: Microsoft Office Specialist Exam 77-882 and Microsoft Office Specialist Expert Exam 77-888
Description: This instructor-led course builds on the skills and concepts taught in Excel 2010: Intermediate. Participants will work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, 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, Solver, and Analysis ToolPak), running and recording macros, and sharing Excel data via the Web.
Unit 1: Advanced functionsTopic A: Logical functionsTopic B: Math and statistical functionsTopic C: Financial functionsTopic D: Displaying and printing formulasUnit 2: Lookups and data tablesTopic A: Using lookup functions Topic B: Using MATCH and INDEXTopic C: Creating data tablesUnit 3: Advanced data managementTopic A: Validating cell entriesTopic B: Exploring database functionsUnit 4: PivotTables and PivotChartsTopic A: Working with PivotTablesTopic B: Rearranging PivotTablesTopic C: Formatting PivotTablesTopic D: PivotChartsUnit 5: Exporting and importingTopic A: Exporting and importing text filesTopic B: Exporting and importing XML dataTopic C: Querying external databasesUnit 6: Analytical toolsTopic A: Goal Seek and SolverTopic B: The Analysis ToolPakTopic C: ScenariosTopic D: ViewsUnit 7: Macros and custom functionsTopic A: Running and recording a macroTopic B: Working with VBA codeTopic C: Creating functionsUnit 8: Conditional formatting and SmartArt graphicsTopic A: Conditional formatting with graphicsTopic B: SmartArt graphics