About this Course

Target Audience :

This exam is intended for candidates with the equivalent of more than one year of hands‐on experiencein efficiently utilizing advanced features of Microsoft Office Excel 2007. These skills include capturing,formatting, calculating, analyzing, and presenting data and managing automation, data ranges, andworkbooks. The successful candidate might have the role of financial analyst, data analyst, or projectmanager

Objective Domain :

1. Capturing Data

1.1. Restrict data entry by using data validation.

This objective may include but is not limited to: referencing external data in drop‐down lists,

validating data by using formulas, and encircling invalid data  

1.2. Link form controls to cells.   

This objective may include but is not limited to: scroll bars, check box, label, and drop‐down

1.3. Import data from an external source.  

This objective may include but is not limited to: importing data from a text file, importing

data by using a Web query, using the Microsoft Query Wizard to edit an existing query, and

referencing data in a database

1.4. Link to data in an external source.  

This objective may include but is not limited to: referencing data in another workbook,

refreshing and editing workbook links, and changing  data refresh options to optimize file size

2. Calculating Data by Using Advanced Formulas

2.1. Create formulas that combine Lookup & Reference and Statistical functions.

This objective may include but is not limited to: using common Lookup & Reference functions

(HLOOKUP, VLOOKUP, TRANSPOSE, INDIRECT, MATCH, INDEX, CHOOSE) and using common

Statistical functions (COUNTIF, COUNTIFS, COUNTBLANK, AVERAGEIF, AVERAGEIFS,

FREQUENCY)

2.2. Create formulas that combine Date & Time, Text, and Logical functions.   

This objective may include but is not limited to: using Date & Time functions, using Text

functions, and using common Logical functions (IFERROR,Nested IF, OR, and AND)

2.3. Manage and reference defined names.  

This objective may include but is not limited to: creating a dynamic named range, creating

and referencing a named formula, modifying named ranges by using Names Manager, and

navigating across worksheets by using named ranges

2.4. Audit formulas.  

This objective may include but is not limited to: trace dependents, trace precedents, and

adding a watch to the Watch Window

3. Managing Data Ranges

3.1. Consolidate data ranges.

This objective may include but is not limited to: consolidating data ranges by position, by

category, or with links to a source

3.2. Select and manipulate similar cells and objects.

This objective may include but is not limited to: blanks, comments, objects, conditional

formats, formulas, visible cells only, and find and replace by format

3.3. Apply advanced filtering.  

This objective may include but is not limited to: using multiple conditionals on the same

column, using the OR condition across multiple columns, and  applying filtering to unique

records only

3.4. Protect data in a worksheet.  

This objective may include but is not limited to: locking and unlocking cells, locking and

unlocking objects, and hiding formulas

4. Summarizing and Analyzing Data

4.1. Create PivotTables and PivotCharts.

This objective may include but is not limited to: consolidating multiple data ranges by using

PivotTables, creating a PivotTable from an existing worksheet, creating a PivotTable linked to

an external database, and creating a PivotChart from an existing worksheet

4.2. Modify PivotTable content.   

This objective may include but is not limited to: grouping (group by dates, group by numbers,

group by text), inserting a calculated field, and showing report filter pages

4.3. Perform what‐if analysis.  

This objective may include but is not limited to: using Scenario Manager (create scenarios,

compare scenarios),  using a data table, using Goal Seek, and using the Solver add‐in

5. Formatting Worksheet and Chart Content

5.1. Create custom number formats.

This objective may include but is not limited to: number formats, date and time formats, and

conditional number formats

5.2. Define advanced conditional formatting rules by using formulas.    

This objective may include but is not limited to: creating rules based on dates, creating rules

based on numbers, and creating rules based on text

5.3. Add visual elements to a chart.  

This objective may include but is not limited to: adding a Z axis, adding a second Y axis, and

adding analysis indicators (Trendlines, R‐squared value, Series lines, High‐Low lines)

6. Managing Macros and User-Defined Functions

6.1. Record and edit a macro.

This objective may include but is not limited to: recording a macro and editing a macro in

Visual Basic® for Applications (VBA)

*NOTE: ensure 1 item for recording a macro and 1 item in editing a macro in VBA  

*NOTE: for editing a macro in VBA, modify actions instead of input/output

6.2. Manage existing macros.  

This objective may include but is not limited to: moving macros between workbooks,

assigning a shortcut key to an existing macro, assigning a macro to a button in a worksheet,

and configuring macro security levels

6.3. Create a user‐defined function (UDF). 

Duration : 3days / 22hours

Entry Requirement : Completed Microsoft Excel 2007 Specialist Course or equivalent excel knowledge and experience

Rate this course:

Log in to submit

Comments