Capital Business Machines Excel 2010 Level 3-Advanced

Excel 2010 Level 3-Advanced

This course builds on the skills and concepts taught in Excel 2010: Intermediate. Students will work with advanced formulas and functions. Such functions as lookup functions, a variety of If functions, Round, Payment functions, Dual Variable tables, Data Validation, Converting text to columns, and removing duplicates records. In addition, students will learn about a series of database versions of popular functions (DSUM, DAVERAGE…). They will learn how to 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. A very loaded day.

Course Length

1 day and 2 day version

Prerequisites

Windows Basic, Excel Intermediate

Topics

Unit 1 :Logical and statistical functions

Topic A: Logical functions

A-1:Using the IF function

A-2:Using a formula to apply conditional formatting

A-3:Using OR, AND, and NOT as nested functions

A-4:Using nested IF functions

A-5:Using the IFERROR function

Topic B: Math and statistical functions

B-1: Using SUMIF

B-2: Using COUNTIF

B-3: Using AVERAGEIF

B-4: Using SUMIFS, COUNTIFS, and AVERAGEIFS

B-5: Using ROUND

Unit 2 :Financial and date functions

Topic A: Financial functions

A-1:Using the PMT function

Topic B: Date and time functions

B-1:Using date functions

B-2:Using time functions

Topic C: Array formulas

C-1:Using an array formula

C-2:Applying arrays to functions

C-3:Modifying the array formula

Topic D: Displaying and printing formulas

D-1: Showing, printing, and hiding formulas

D-2: Setting calculation options

Unit 3 :Lookups and data tables

Topic A: Using lookup functions

A-1:Examining VLOOKUP

A-2:Using VLOOKUP to find an exact match

A-3:Using VLOOKUP to find an approximate match

A-4:Using HLOOKUP to find exact matches

A-5:Using HLOOKUP to find approximate matches

Topic B: Using MATCH and INDEX

B-1:Using the MATCH function

B-2:Using the INDEX function

Topic C: Creating data tables

C-1: Creating a one-variable data table

C-2: Creating a two-variable data table

Unit 4 :Advanced data management

Topic A: Validating cell entries

A-1:Observing data validation

A-2:Creating a data validation rule

A-3:Setting date and list validation rules

Topic B: Exploring database functions

B-1: Examining the structure of database functions

B-2: Using the DSUM function

Unit 5 :Exporting and importing

Topic A: Exporting and importing text files

A-1:Exporting Excel data to a text file

A-2:Importing data from a text file into a workbook

A-3:Converting text to columns

A-4:Removing duplicate records

Topic B: Exporting and importing XML data

B-1:Using the XML Source pane

B-2:Importing XML data into a workbook

B-3:Exporting data from a workbook to an XML data file

B-4:Deleting an XML map

Topic C: Getting external data

C-1: Getting external data from Microsoft Query

C-2: Using a Web query to get data from the Web

C-3: Discussing cube functions

Unit 6 :Analytical tools

Topic A: Goal Seek and Solver

A-1:Using Goal Seek to solve for a single variable

A-2:Activating Solver and the Analysis ToolPak

A-3:Using Solver to solve for multiple variables

Topic B: The Analysis ToolPak

B-1:Using the Sampling analysis tool

Topic C: Scenarios

C-1: Creating scenarios

C-2: Switching among scenarios

C-3: Merging scenarios from another worksheet

Unit 7 :Macros and custom functions

Topic A: Running and recording a macro

A-1:Running a macro

A-2:Recording a macro

A-3:Assigning a macro to a command button

A-4:Inserting a macro button

A-5:Creating an Auto_Open macro

Topic B: Working with VBA code

B-1:Observing a VBA code module

B-2:Editing VBA code

Topic C: Creating functions

C-1:Creating a custom function