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).  

Course :