Microsoft Office Specialist-Excel Syllabus

1.   Manage Workbook Options and Settings

• 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

• Navigate in Worksheets and Workbooks
  • Search for data within a workbook
  • Navigate to a named cell, range, or workbook element
  • Insert and remove hyperlinks

• 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

• 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

• 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

• Apply Custom Data Formats and Validation
  • Create custom number formats
  • Populate cells by using advanced Fill Series options
  • Configure data validation

• Apply Advanced Conditional Formatting and Filtering
  • Create custom conditional formatting rules
  • Create conditional formatting rules that use formulas
  • Manage conditional formatting rules

• Create and Modify Custom Workbook Elements
  • Create and modify simple macros
  • Insert and configure form controls

3.   Create Tables

• 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

• Manage Table Styles and Options
  • Apply styles to tables
  • Configure table style options
  • Insert total rows

• 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

• 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

• 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

• 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

• Create Charts
  • Create a new chart
  • Add additional data series
  • Switch between rows and columns in source data
  • Analyze data by using Quick Analysis

• Format Charts
  • Resize charts
  • Add and modify chart elements
  • Apply chart layouts and styles
  • Move charts to a chart sheet

• 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

 

• Manage Workbooks
  • Save a workbook as a template
  • Enable macros in a workbook
  • Display hidden ribbon tabs

• 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

 

• Apply Custom Data Formats and Validation
  • Create custom number formats
  • Populate cells by using advanced Fill Series options
  • Configure data validation

• Apply Advanced Conditional Formatting and Filtering
  • Create custom conditional formatting rules
  • Create conditional formatting rules that use formulas
  • Manage conditional formatting rules

• 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

• 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

 

• 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

• Look up data by using Functions
  • Look up data by using the VLOOKUP function
  • Look up data by using the HLOOKUP function

• 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

• 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

• Troubleshoot Formulas
  • Trace precedence and dependence
  • Monitor cells and formulas by using the Watch Window

• Define Named Ranges and Objects
  • Name cells
  • Name data ranges
  • Name tables
  • Manage named ranges and objects

9.    Create Advanced Charts and Tables

 

• Create and Manage PivotTables

• Create PivotTables
• Modify field selections and options
• Create slicers
• Group PivotTable data
• Add calculated fields
• Format data

• Create and Manage Pivot Charts

• Create PivotCharts
• Manipulate options in existing PivotCharts
• Apply styles to PivotCharts
• Drill down into PivotChart details