CourseChapTopicID | CourseChapTopicName | |
118 | Capturing Data |
CourseChapTopicDetailsID | CourseChapTopicID | DetailTopicName | imgUrl | imgurl1 | iimgurl |
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 | imgurl1 | iimgurl |
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 | imgurl1 | iimgurl |
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 | imgurl1 | iimgurl |
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 | imgurl1 | iimgurl |
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
| | |
|