### MS Excel Level III, ITI092

Summer 2014 Course Description: (Click here for Fall 2014 Description) Learn to work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, learn about data validation and database functions such as DSUM. Import and export data, and query external databases. You will learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, SmartArt graphics, and conditional formatting with graphics.

This course will help you prepare for the Microsoft Office Specialist core-level exam for Excel 2010 (exam 77-882). For comprehensive certification training, you should complete MS Excel Level I, II, and III. Course manual comes with CertBlaster exam prep software (download)

Prerequisites: MS Excel--Levels I and II or equivalent experience is a must.

Classroom Hours: 10

Unit 1: Logical and statistical functions
Topic A: Logical functions
Topic B: Math and statistical functions

Unit 2: Financial and date functions
Topic A: Financial functions
Topic B: Date and time functions
Topic C: Array formulas
Topic D: Displaying and printing formulas

Unit 3: Lookups and data tables
Topic A: Using lookup functions
Topic B: Using MATCH and INDEX
Topic C: Creating data tables

Unit 4: Advanced data management
Topic A: Validating cell entries
Topic B: Exploring database functions

Unit 5: Exporting and importing
Topic A: Exporting and importing text files
Topic B: Exporting and importing XML data
Topic C: Getting external data

Unit 6: Analytical tools
Topic A: Goal Seek and Solver
Topic B: The Analysis ToolPak
Topic C: Scenarios

Unit 7: Macros and custom functions
Topic A: Running and recording a macro
Topic B: Working with VBA code
Topic C: Creating functions

MS EXCEL – LEVEL III
Fall 2014 Course Description: This course builds on the skills and concepts taught in Excel 2013-Level II.  Learn how to work with advanced formulas, as well as lookup functions. Learn about data validation and how to import and export data. Work with PivotTables and PivotCharts. Finally, learn how to export and import data, use the analytical features of Excel such as Goal Seek and the new Analysis tool, and run and record macros. This course follows the curriculum for the Microsoft Office Specialist core-level for Excel 2013 (exam 77-420) and the Microsoft Office Specialist Expert for Excel 2013 (exams 77-427 & 428). For comprehensive certification training, students should complete MS Excel Levels I, II, III. Prerequisites: MS Excel Level II or equivalent experience, Working with Windows, File Management or equivalent experience.

Textbook: Please bring your textbook to the first class session. See MC Bookstores for more information on your textbook; bring to first class along with a USB Flash Drive/memory stick (at least 512 MB).

Course Objectives:

After completing this course, students will know how to:

• Use IF and other logical operator functions to calculate a value based on specified criteria; use conditional functions to summarize data; use PMT function to calculate periodic payments for a loan; use text function to extract data strings and modify text; use date functions to calculate duration in years, months, and days; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
• Use the VLOOKUP, HLOOKUP and Lookup functions to find values in worksheet data; and use data tables to project values.
• Use DATA Validation feature to validate data entered in cell; and use database functions to summarize data values that meet criteria you specify.
• Format data points in charts; create combination charts and trendlines; insert sparklines; use chart templates; and add and modify drawing objects and shapes.
• Create a PivotTable for analyzing and comparing large amounts of data; modify the PivotTable view by using slicers to filter data and by rearranging fields; improve the appearance of a PivotTable by changing its field settings and applying a style; create a PivotChart to graphically display data from a PivotTable; and use PowerPivot to create a PivotTable from imported data.
• Export data from Excel to other formats, and import data from a text file into an Excel workbook.
• Use Goal Seek utility to meet a target output for a formula by adjusting the values in the input cells; create scenarios to save various sets of input values produce different results; and analyze data instantly with Quick Analysis.
• Run a macro to perform tasks automatically; record macros; assign a macro to a button in the worksheet; edit a macro by editing VBA code; and copy a macro to another workbook.
• Use the Accessibility Checker to resolve accessibility issues; insert international symbols, and install a different language.