X210L3	Microsoft Excel 2010 Mastery

Course Duration: 1 Day	Class Time: 9 am - 4:30 pm

Audience Profile

Students will get the most out of this course if their goal is to learn to increase productivity by understanding and employing advanced data analysis tools, PivotTables, advanced functions, data management, lookups, and macros.

At Course Completion

After completing this course, students will be able to:

• Understand advanced Excel functions including: logical functions, math and statistical functions, financial functions and display and print formulas
• Use lookup functions, MATCH and INDEX, and create data tables
• Employ advanced data management techniques, including cell entry validation and high-end database functions
• Create, modify, and work with PivotTables and PivotCharts
• Export and import text fields, XML data, and query external databases
• Incorporate Goal Seek and Solver, the Analysis ToolPak, scenarios, and views to streamline analysis and process
• Create macros via the macro recorder, understand VBA code concepts, and develop user-defined functions
• Employ conditional formatting with graphic representation and SmartArt

Course Outline

LESSON 1 – Advanced Functions

• Logical Functions
• Math and Statistical Functions
• Financial Functions
• Displaying and Printing Formulas

LESSON 2 – Lookups and Data Tables

• Using Lookup Functions
• Using MATCH and INDEX
• Creating Data Tables

LESSON 3 – Advanced Data Management

• Validating Cell Entries
• Exploring Database Functions

LESSON 4 – PivotTables and PivotCharts

• Working with PivotTables
• Rearranging PivotTables
• Formatting PivotTables
• PivotCharts

LESSON 5 – Exploring Importing and Exporting

• Importing and Exporting Text Fields
• Importing and Exporting XML Data
• Querying External Databases

LESSON 6 – Analytical Tools

• Goal Seek and Solver
• The Analysis ToolPak
• Scenarios
• Views

LESSON 7 – Macros and Custom Functions

• Running and Recording a Macro
• Working with VBA Code
• Creating Functions

LESSON 8 – Conditional Formatting and SmartArt Graphics

• Conditional Formatting with Graphics
• SmartArt Graphics
  

Student Prerequisites

Excel 2010 Advanced or equivalent working knowledge of all skills presented in that course