MOS: Excel 2007 Expert CTC Academy

# MOS: Excel 2007 Expert

• Duration: 24 Hours

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

### Course Location

We_They Academy | Authorized Training Center: We_They Academy is an Egyptian company established in 1999 in Cairo-Egypt.

We_They Academy Training centers are private independent training centers.

Our office is located in Cairo. We_They Academy Training centers programs are offered by highly professional training methods. We_They Academy is one of the leading private educational centers which offers International Universities and Training Colleges programs.

Employers in the international employment market are confident that holders of our certificates and diplomas have been well trained and have acquired good skills and knowledge. Certificates and diplomas awarded by We_They Academy Training Centers are stamped by highly professional international training colleges and universities. The demand on We_They Academy Training Centers courses, programs, certificates and diplomas are on the increase nationally in the Arab Republic of Egypt.