Excel 2010 - Level 3

This ILT Series course, rated 5.0/5.0 in overall quality by ProCert Labs, builds on the skills and concepts taught in Excel 2010: Intermediate. Students 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 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), running and recording macros, SmartArt graphics, and conditional formatting with graphics. Course manual comes with CertBlaster exam prep software (download).

This course will help students prepare for the Microsoft Office Specialist core-level exam for Excel 2010 (exam 77-882) and the Microsoft Office Specialist Expert exam for Excel 2010 (exam 77-888). For comprehensive certification training, students should complete the Basic, Intermediate, and Advanced courses for Excel 2010.

Unit 1:Logical and statistical functions

Topic A:Logical functions

• Using the IF function
• Using a formula to apply conditional formatting
• Using OR, AND, and NOT as nested functions
• Using nested IF functions
• Using the IFERROR function

Topic B:Math and statistical functions

• Using SUMIF
• Using COUNTIF
• Using AVERAGEIF
• Using SUMIFS, COUNTIFS, and AVERAGEIFS
• Using ROUND

Unit 2:Financial and date functions

Topic A:Financial functions

• Using the PMT function

Topic B:Date and time functions

• Using date functions
• Using time functions

Topic C:Array formulas

• Using an array formula
• Applying arrays to functions
• Modifying the array formula

Topic D:Displaying and printing formulas

• Showing, printing, and hiding formulas
• Setting calculation options

Unit 3:Lookups and data tables

Topic A:Using lookup functions

• Examining VLOOKUP
• Using VLOOKUP to find an exact match
• Using VLOOKUP to find an approximate match
• Using HLOOKUP to find exact matches
• Using HLOOKUP to find approximate matches

Topic B:Using MATCH and INDEX

• Using the MATCH function
• Using the INDEX function

Topic C:Creating data tables

• Creating a one-variable data table
• Creating a two-variable data table

Unit 4:Advanced data management

Topic A:Validating cell entries

• Observing data validation
• Creating a data validation rule
• Setting date and list validation rules

Topic B:Exploring database functions

• Examining the structure of database functions
• Using the DSUM function

Unit 5:Exporting and importing

Topic A:Exporting and importing text files

• Exporting Excel data to a text file
• Importing data from a text file into a workbook
• Converting text to columns
• Removing duplicate records

Topic B:Exporting and importing XML data

• Using the XML Source pane
• Importing XML data into a workbook
• Exporting data from a workbook to an XML data file
• Deleting an XML map

Topic C:Getting external data

• Getting external data from Microsoft Query
• Using a Web query to get data from the Web
• Discussing cube functions

Unit 6:Analytical tools

Topic A:Goal Seek and Solver

• Using Goal Seek to solve for a single variable
• Activating Solver and the Analysis ToolPak
• Using Solver to solve for multiple variables

Topic B:The Analysis ToolPak

• Using the Sampling analysis tool

Topic C:Scenarios

• Creating scenarios
• Switching among scenarios
• Merging scenarios from another worksheet

Unit 7:Macros and custom functions

Topic A:Running and recording a macro

• Running a macro
• Recording a macro
• Assigning a macro to a command button
• Inserting a macro button
• Creating an Auto_Open macro

Topic B:Working with VBA code

• Observing a VBA code module
• Editing VBA code

Topic C:Creating functions

• Creating a custom function

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. macro-economics.

After completing this course, students will be able to:

• enhance productivity and efficiency by streamlining the workflow.
• collaborate with other workbook users.
• audit worksheets.
• analyze data.
• work with multiple workbooks.
• import and export data.
• integrate Excel data with the web.
• structure workbooks with XML.

To ensure your success, we recommend that you first take the following courses or have equivalent knowledge:

• Microsoft Office Excel 2010: Level 1
• Microsoft Office Excel 2010: Level 2