This is the Course Details

You have selected the Microsoft Corporation for the Microsoft Office Specialist (MOS) course in Exam 77-851 MOS Excel 2007 Expert

CourseChapTopicID

CourseChapTopicName

118

Capturing Data

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl
1403	118	Restrict data entry by using data validation•Reference external data in drop-down lists, validate data by using formulas and encircle invalid data	Learn
1404	118	Link form controls to cells•Scroll bars, check box, label and drop-down	Learn
1405	118	Import data from an external source•Import data from a text file, import data by using a web query, use the Microsoft Query Wizard to edit an existing query and reference data in a database	Learn
1406	118	Link to data in an external source•Reference data in another workbook, refresh and edit workbook links and change data refresh options to optimise file size	Learn

119

Calculating Data by Using Advanced Formulas

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl
1407	119	Create formulas that combine Lookup & Reference and Statistical functions•Use common Lookup & Reference functions (HLOOKUP, VLOOKUP, TRANSPOSE, INDIRECT, MATCH, INDEX, CHOOSE) and use common Statistical functions (COUNTIF, COUNTIFS, COUNTBLANK, AVERAGEIF, AVERAGEIFS, FREQUENCY)	Learn
1408	119	Create formulas that combine Date & Time, Text and Logical functions•Use Date & Time functions, use Text functions and use common Logical functions (IFERROR, Nested IF, OR and AND)	Learn
1409	119	Manage and reference defined names•Create a dynamic named range, create and reference a named formula, modify named ranges by using Names Manager and navigate across worksheets by using named ranges	Learn
1410	119	Audit formulas•Trace dependents, trace precedents, and add a watch to the Watch Window	Learn

120

Managing Data Ranges

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl
1411	120	Consolidate data ranges•Consolidate data ranges by position, by category, or with links to a source	Learn
1412	120	Select and manipulate similar cells and objects•Blanks, comments, objects, conditional formats, formulas, visible cells only and find and replace by format	Learn
1413	120	Apply advanced filtering•Use multiple conditionals on the same column, use the OR condition across multiple columns, and apply filtering to unique records only	Learn
1414	120	Protect data in a worksheet•Lock and unlock cells, lock and unlock objects, and hide formulas	Learn

121

Summarizing and Analyzing Data

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl
1415	121	Create PivotTables and PivotCharts•Consolidate multiple data ranges by using PivotTables, create a PivotTable from an existing worksheet, create a PivotTable linked to an external database and create a PivotChart from an existing worksheet	Learn
1416	121	Modify PivotTable content•Group (group by dates, group by numbers, group by text), insert a calculated field and show report filter pages	Learn
1417	121	Perform what-if analysis•Use Scenario Manager (create scenarios, compare scenarios), use a data table, use Goal Seek and use the Solver add-in	Learn

122

Formatting Worksheet and Chart Content

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl	imgurl1	iimgurl
1418	122	NIL	Learn

123

Managing Macros and User-Defined Functions

CourseChapTopicDetailsID	CourseChapTopicID	DetailTopicName	imgUrl
1419	123	Record and edit a macro•Record a macro and edit a macro in Visual Basic for Applications (VBA)	Learn
1420	123	Manage existing macros•Move macros between workbooks, assign a shortcut key to an existing macro, assign a macro to a button in a worksheet, and configure macro security levels	Learn
1421	123	Create a user-defined function (UDF)	Learn

data-share="true" data-width="450" data-show-faces="true">