MS Excel syllabus : Latest Updated Syllabus for syllabus

Manage Workbook Options and Settings 

Create Worksheets and Workbooks

• Creating new workbooks 
• Saving workbooks 
• Closing workbooks 
• Opening workbooks 
• Selecting cells 
• Import data 
• 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

• Rename a worksheet 
• Change worksheet order and colour
• Insert and delete columns or rows 
• Change workbook themes 
• Adjust row height and column width 
• Insert headers and footers

Customise Options and Views for Worksheets and Workbooks

• Hide or unhide worksheets 
• Hide or unhide rows and columns 
• Customise 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 and compatibility issues

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 

• Create custom conditional formatting rules 
• Create conditional formatting rules that use formulas
• Manage conditional formatting rules 

Data Filtering

• Using AutoFilters 
• Applying a custom AutoFilter
• Creating advanced filters
• Applying multiple criteria 
• Using complex criteria
• Copying filtered results to a new location 
• Using database functions

Create and Modify Custom Workbook Elements 

• Create and modify simple macros 
• Insert and configure form controls

Auditing Worksheets 

• Tracing precedent and dependent cells 
• Tracing errors 
• Cell Validation

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

Perform Operations with Formulas and Functions

Summarise Data by using Functions

• 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, SUMIF and AVERAGEIF functions
• 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 

Create Charts and Objects

Create Charts

• Create a new chart
• Add additional data series
• Switch between rows and columns in the source data
• Analyze data 

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 
• Format charts
• Modify object properties 
• Add alternative text to objects for accessibility

Apply Custom Data Formats and Layouts

Apply Custom Data Formats and Validation

• Create custom number formats 
• Learn how to use the Flash fill function
• 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 colour 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

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

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

Security  

Security Features

• Unlocking cells
• Worksheet protection
• Workbook protection
• Password-protecting Excel files