Capturing Data - 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
- Link form controls to cells.
- This objective may include but is not limited to: scroll bars, check box, label, and drop-down
- 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
- 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
Calculating Data by Using Advanced Formulas - 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)
- 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)
- 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
- Audit formulas.
- This objective may include but is not limited to: trace dependents, trace precedents, and adding a watch to the Watch Window
Managing Data Ranges - 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
- 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
- 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
- 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
Summarizing and Analyzing Data - 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
- 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
- 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
Formatting Worksheet and Chart Content - Create custom number formats.
- This objective may include but is not limited to: number formats, date and time formats, and conditional number formats
- 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
- 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)
Managing Macros and User-Defined Functions - 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)
- 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
- Create a user-defined function (UDF).
|
