MOS: Excel 2007 Expert

Course details

Module One: Capturing Data

• Restrict data entry by using data validation: referencing external data in drop-down lists, validating data by using formulas, and encircling invalid data.
• Link form controls to cells: scroll bars, check box, label, and drop-down.
• Import data from an external source: 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: referencing data in another workbook, refreshing and editing workbook links, and changing data refresh options to optimize file size.

Module Two: Calculating Data by Using Advanced Formulas

• Create formulas that combine Lookup & Reference and Statistical functions: 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: using Date & Time functions, using Text functions, and using common Logical functions (IFERROR, Nested IF, OR, and AND).
• Manage and reference defined names: 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: trace dependents, trace precedents, and adding a watch to the Watch Window.

Module Three: Managing Data Ranges

• Consolidate data ranges: consolidating data ranges by position, by category, or with links to a source.
• Select and manipulate similar cells and objects: blanks, comments, objects, conditional formats, formulas, visible cells only, and find and replace by format.
• Apply advanced filtering: 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: locking and unlocking cells, locking and unlocking objects, and hiding formulas.

Module Four: Summarizing and Analyzing Data

• Create PivotTables and PivotCharts: 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: grouping (group by dates, group by numbers, group by text), inserting a calculated field, and showing report filter pages.
• Perform what-if analysis: using Scenario Manager (create scenarios, compare scenarios), using a data table, using Goal Seek, and using the Solver add-in.

Module Five: Formatting Worksheet and Chart Content

• Create custom number formats:: number formats, date and time formats, and conditional number formats.
• Define advanced conditional formatting rules by using formulas: creating rules based on dates, creating rules based on numbers, and creating rules based on text.
• Add visual elements to a chart: adding a Z axis, adding a second Y axis, and adding analysis indicators (Trendlines, R-squared value, Series lines, High-Low lines).
• Module Six: Managing Macros and User-Defined Functions (6 hours)
• Record and edit a macro: recording a macro and editing a macro in Visual Basic for Applications (VBA).
• Manage existing macros: 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).