MICROSOFT EXCEL FOR ADVANCED SYLLABUS
Chapter 1: Using Pivot Tables
Creating Pivot Tables
Inserting Slicers
Working with Pivot Tables
Inserting Pivot Charts
More Pivot Table Functionality
Chapter 2: Auditing Worksheets
Tracing Precedents
Tracing Dependents
Showing Formulas
Chapter 3: Data Tools
Converting Text to Columns
Linking to External Data
Controlling Calculation Options
Data Validation
Consolidating Data
Goal Seek
Chapter 4: Working with Others
Protecting Worksheets and Workbooks
Tracking Changes
Marking a Workbook as Final
Chapter 5: Recording and Using Macros
Recording Macros
Running Macros
Editing Macros
Adding Macros to the Quick Access Toolbar
Chapter 6: Random Useful Items
Sparklines
Preparing a Workbook for Internationalization and Accessibility
Importing and Exporting Files
Chapter 7: Microsoft Excel Features that Were New in 2013
Using Slicers to Filter Data
Creating a PivotTable Timeline
Creating a Standalone PivotChart
Workspaces in Excel 2013
Chapter 8: Features New in 2016
PivotTable Updates
Ink Equations
Multi-Select Option in Slicers
Quick Shape Formatting
Sharing with SharePoint or OneDrive
MICROSOFT EXCEL FOR ADVANCED SYLLABUS
MODULE I: Review of Intermediate Excel Functions
1. IF, COUNTIF, SUMIF, VLOOKUP, CONCATENATE
MODULE II: Review of the Formulas Tab
1. Name Manager/ Naming Cells
2. Evaluating Errors in Formulas
3. Defining Errors
4. Evaluating Formulas and Dependent Tracking o Watch Window Calculation Options
MODULE III: Intermediate Excel Functions
1. Logical Functions: AND, OR, IFERROR, ISERROR
2. Statistical Functions: COUNTIES, AVERAGEIF/S, SUMIFS, AGGREGATE, MAX& AVERAGEA, etc
3. Lookup Functions: VLOOKUP, HLOOKUP, CHOOSE, MATCH, INDEX
4. Date and Time Functions
5. Financial Functions
6. Array Functions
MODULE IV: Database Management
1. Data Validation
2. Creating Advanced Dropdown lists o Formula- Based Data Validation
3. Consolidation Tools
4. Creating 3D Formulas
5. Consolidation Tool
6. Using Groups and Subtotals
MODULE V: Tables and Charts
1. Creating Tables
2. Creating and Editing Charts
3. Using Sparklines
MODULE VI: Conditional Formatting
1. Data Bars, Color Scales, and Icon Sets
2. Formula- Based Conditional Formatting
MODULE VII: Analysis Tools
1. Goal Seek
2. Scenario Manager
3. Data Tables
MODULE VIII: Advanced PivotTables and PivotCharts
1. Using Slicers
2. Creating Summary Pivots
3. Using Groups
4. Adding Calculated Fields and Items
5. Layout and Themes
6. Creating PivotTable and PivotCharts o Drilling Up and Drilling Down
7. Security Options
8. Protecting Cell Contents and Worksheets
9. Editing Permissions
10. Protecting the File
11. Other Security Options
MODULE IX: Introduction to Macros
1. Macros
a. Recording and Running a Macro
b. Assigning Macro to Images or Buttons
c. Editing Macros
2. Introduction to Visual Basic for Applications
a. Essential Codes
b. Creating User forms and its Operations
c. Creating IF statements in VBA
