Excel Course 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
