MS Excel

About the Course

Microsoft Office Specialist-Excel Syllabus 1. Manage Workbook Options and Settings 1.1. Create Worksheets and Workbooks ▪ Create a workbook ▪ Import data from a delimited text file ▪ Add a worksheet to an existing workbook ▪ Copy and move a worksheet 1.2. Navigate in Worksheets and Workbooks ▪ Search for data within a workbook ▪ Navigate to a named cell, range, or workbook element ▪ Insert and remove hyperlinks 1.3. Format Worksheets and Workbooks ▪ Change worksheet tab color ▪ Rename a worksheet ▪ Change worksheet order ▪ Insert and delete columns or rows ▪ Change workbook themes ▪ Adjust row height and column width ▪ Insert headers and footers 1.4. Customize Options and Views for Worksheets and Workbooks ▪ Hide or unhide worksheets ▪ Hide or unhide columns and rows ▪ Customize the Quick Access toolbar ▪ Modify document properties ▪ Display formulas 1.5. Configure Worksheets and Workbooks for Distribution ▪ Inspect a workbook for hidden properties or personal information ▪ Inspect a workbook for accessibility issues ▪ Inspect a workbook for compatibility issues 2. Apply Custom Data Formats and Layouts 2.1. Apply Custom Data Formats and Validation ▪ Create custom number formats ▪ Populate cells by using advanced Fill Series options ▪ Configure data validation 2.2. Apply Advanced Conditional Formatting and Filtering ▪ Create custom conditional formatting rules ▪ Create conditional formatting rules that use formulas ▪ Manage conditional formatting rules 2.3. Create and Modify Custom Workbook Elements ▪ Create and modify simple macros ▪ Insert and configure form controls 3. Create Tables 3.1. Create and Manage Tables ▪ Create an Excel table from a cell range ▪ Convert a table to a cell range ▪ Add or remove table rows and columns 3.2. Manage Table Styles and Options ▪ Apply styles to tables ▪ Configure table style options ▪ Insert total rows 3.3. Filter and Sort a Table ▪ Filter records ▪ Sort data by multiple columns ▪ Change sort order ▪ Remove duplicate records 4. Perform Operations with Formulas and Functions 4.1. Summarize Data by using Functions ▪ Insert references ▪ Perform calculations by using the SUM function ▪ Perform calculations by using MIN and MAX functions ▪ Perform calculations by using the COUNT function ▪ Perform calculations by using the AVERAGE function 4.2. Perform Conditional Operations by using Functions ▪ Perform logical operations by using the IF function ▪ Perform logical operations by using the SUMIF function ▪ Perform logical operations by using the AVERAGEIF function ▪ Perform statistical operations by using the COUNTIF function 4.3. Format and Modify Text by using Functions ▪ Format text by using RIGHT, LEFT, and MID functions ▪ Format text by using UPPER, LOWER, and PROPER functions ▪ Format text by using the CONCATENATE function 5. Create Charts and Objects 5.1. Create Charts ▪ Create a new chart ▪ Add additional data series ▪ Switch between rows and columns in source data ▪ Analyze data by using Quick Analysis 5.2. Format Charts ▪ Resize charts ▪ Add and modify chart elements ▪ Apply chart layouts and styles ▪ Move charts to a chart sheet 5.3. Insert and Format Objects ▪ Insert text boxes and shapes ▪ Insert images ▪ Modify object properties ▪ Add alternative text to objects for accessibility 6. Manage Workbook Options and Settings 6.1. Manage Workbooks ▪ Save a workbook as a template ▪ Enable macros in a workbook ▪ Display hidden ribbon tabs 6.2. Manage Workbook Review Restrict editing ▪ Protect a worksheet ▪ Configure formula calculation options ▪ Protect workbook structure ▪ Manage workbook versions ▪ Encrypt a workbook with a password 7. Apply Custom Data Formats and Layouts 7.1. Apply Custom Data Formats and Validation ▪ Create custom number formats ▪ Populate cells by using advanced Fill Series options ▪ Configure data validation 7.2. Apply Advanced Conditional Formatting and Filtering ▪ Create custom conditional formatting rules ▪ Create conditional formatting rules that use formulas ▪ Manage conditional formatting rules 7.3. Create and Modify Custom Workbook Elements ▪ Create custom color formats ▪ Create and modify cell styles ▪ Create and modify custom themes ▪ Create and modify simple macros ▪ Insert and configure form controls 7.4. Prepare a Workbook for Internationalization ▪ Display data in multiple international formats ▪ Apply international currency formats ▪ Manage multiple options for +Body and +Heading fonts 8. Create Advanced Formulas 8.1. Apply Functions in Formulas ▪ Perform logical operations by using AND, OR, and NOT functions ▪ Perform logical operations by using nested functions ▪ Perform statistical operations by using SUMIFS, AVERAGEIFS, and COUNTIFS functions 8.2. Look up data by using Functions ▪ Look up data by using the VLOOKUP function ▪ Look up data by using the HLOOKUP function 8.3. Apply Advanced Date and Time Functions ▪ Reference the date and time by using the NOW and TODAY functions ▪ Serialize numbers by using date and time functions 8.4. Perform Data Analysis and Business Intelligence ▪ Import, transform, combine, display, and connect to data ▪ Consolidate data ▪ Perform what-if analysis by using Goal Seek and Scenario Manager ▪ Calculate data by using financial functions 8.5. Troubleshoot Formulas ▪ Trace precedence and dependence ▪ Monitor cells and formulas by using the Watch Window 8.6. Define Named Ranges and Objects ▪ Name cells ▪ Name data ranges ▪ Name tables ▪ Manage named ranges and objects 9. Create Advanced Charts and Tables 9.1. Create and Manage PivotTables ▪ Create PivotTables ▪ Modify field selections and options ▪ Create slicers ▪ Group PivotTable data ▪ Add calculated fields ▪ Format data 9.2. Create and Manage Pivot Charts ▪ Create PivotCharts ▪ Manipulate options in existing PivotCharts ▪ Apply styles to PivotCharts ▪ Drill down into PivotChart details