Microsoft Advanced Excel

Certificate in Advanced Excel – Course Syllabus

Module 1: Excel Basics Refresher

• Overview of the Excel interface

• Workbook and worksheet navigation

• Data entry and formatting essentials

• Cell referencing: relative, absolute, and mixed

• Core formulas and functions

• Sorting and filtering techniques

• Keyboard shortcuts and efficient navigation

• Page layout, print setup, and template creation

• Basic data validation principles

Module 2: Advanced Formulas & Functions

• Logical functions: IF, AND, OR

• Lookup functions: VLOOKUP, HLOOKUP, XLOOKUP

• Text functions: LEFT, RIGHT, MID, LEN, TEXTJOIN, etc.

• Date & Time functions: TODAY, NOW, EOMONTH, NETWORKDAYS

• Math functions: ROUND, SUMIF, COUNTIF, AVERAGEIF

• Array formulas and dynamic arrays

• Error handling: IFERROR, ISERROR, etc.

• Nested formulas and use of INDIRECT, ADDRESS

• Formula auditing and troubleshooting

Module 3: Data Tools & Cleaning Techniques

• Text to Columns

• Removing duplicates

• Flash Fill and Auto Fill

• Data validation rules and best practices

• Find & Replace for data standardization

• Using TRIM, CLEAN, and handling blank cells

• Go To Special for cleaning large datasets

• Data consolidation from multiple sheets

• Identifying and fixing inconsistencies

Module 4: Pivot Tables & Pivot Charts

• Creating and modifying pivot tables

• Grouping, ungrouping, and summarizing data

• Using calculated fields and items

• Filtering and slicing data with slicers

• Drill-down and drill-through analysis

• Refreshing and maintaining pivot tables

• Creating and customizing pivot charts

• Multiple consolidation ranges

• Best practices for pivot reporting

Module 5: Data Visualization & Charting

• Creating bar, line, pie, combo, and other charts

• Customizing chart elements and layout

• Using sparklines and conditional formatting

• Advanced chart types: thermometer, Gantt, waterfall

• Building interactive charts with dropdowns

• Dynamic charts using named ranges

• Dashboard-style integration and layout

• Chart templates and reuse strategies

Module 6: Advanced Data Analysis

• What-If Analysis: Goal Seek, Scenario Manager, Data Tables

• Solver Add-In for optimization

• Statistical functions: CORREL, STDEV, VAR, FORECAST

• Trendline and regression analysis

• Ranking, percentiles, and distribution tools

• Data sampling and random number generation

• Histograms and bin ranges

• Advanced filter and custom criteria

• Basics of data modeling in Excel