Advanced Excel Complete Syllabus :

Basic | Intermediate | Advanced | Power Query

Section 1: Basic Excel (Foundation)

Prerequisite: None

• 1.1 Excel Interface & Navigation: Mouse vs keyboard, Ctrl shortcuts, moving without mouse
• 1.2 Entering & Editing Data: F2, Ctrl+Enter, Ctrl+D, Ctrl+R, Alt+Enter
• 1.3 Basic Formulas: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, AutoSum (Alt+=)
• 1.4 Cell Referencing: Relative (A1), Absolute ($A$1), Mixed ($A1, A$1), F4 shortcut
• 1.5 Formatting: Number formats (Ctrl+Shift+1-6), Bold, Borders, Wrap text
• 1.6 Sorting & Filtering: Ctrl+Shift+L, Alt+↓, Sort A-Z/Z-A, Filter by value
• 1.7 Find & Replace: Ctrl+F, Ctrl+H, Ctrl+G, Go To Special (blanks, formulas, errors)
• 1.8 Data Validation: Dropdown lists, custom rules, input messages
• 1.9 Tables (Ctrl+T): Structured references, auto-expand, slicers
• 1.10 Named Ranges: Name box, Ctrl+F3, Ctrl+Shift+F3

Basic Excel Capstone: Build a simple expense tracker with dropdown categories, formulas, and a formatted table.

Section 2: Intermediate Excel

Prerequisite: Basic Excel completed

• 2.1 Logical Functions: IF, AND, OR, NOT, nested IF
• 2.2 Modern Logical Functions: IFS, SWITCH
• 2.3 Conditional Aggregations: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS
• 2.4 Lookup Functions (Legacy): VLOOKUP, HLOOKUP, MATCH
• 2.5 Lookup Functions (Modern): XLOOKUP, XMATCH (full coverage)
• 2.6 Index + Match: INDEX, MATCH combination for 2-way lookups
• 2.7 Text Functions (Part 1): LEFT, RIGHT, MID, LEN, FIND, SEARCH
• 2.8 Text Functions (Part 2): TRIM, CLEAN, SUBSTITUTE, REPLACE, UPPER, LOWER, PROPER
• 2.9 Text Functions (Part 3): CONCAT, TEXTJOIN, TEXT, VALUE
• 2.10 Date & Time Functions: TODAY, NOW, DATE, YEAR, MONTH, DAY, WEEKDAY, WEEKNUM
• 2.11 Date Math Functions: DATEDIF, EDATE, EOMONTH, NETWORKDAYS, WORKDAY
• 2.12 Error Handling: IFERROR, IFNA, ISERROR, ISBLANK, ISNUMBER
• 2.13 What-If Analysis: Goal Seek, Data Tables (1 & 2 variable)
• 2.14 PivotTables (Basic): Row/Column fields, Values, Filters, Slicers
• 2.15 PivotTables (Advanced): Calculated fields, grouping dates, show values as (% of total)
• 2.16 Basic Charts: Column, Bar, Line, Pie, Combo charts

Intermediate Excel Capstone: Create a sales dashboard with XLOOKUP, SUMIFS, PivotTables, and charts.

Section 3: Advanced Excel

Prerequisite: Intermediate Excel completed

• 3.1 Dynamic Array Functions: FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, RANDARRAY
• 3.2 Spill Range Behavior: # operator, spill errors (@), intersection
• 3.3 Advanced Lookups: CHOOSE, OFFSET, INDIRECT, FORMULATEXT, GETPIVOTDATA
• 3.4 SUMPRODUCT: Single condition, multiple conditions, weighted averages
• 3.5 Legacy Array Formulas: CSE (Ctrl+Shift+Enter), FREQUENCY, TRANSPOSE
• 3.6 LET Function: Define variables inside formulas
• 3.7 LAMBDA Functions: LAMBDA, MAP, SCAN, REDUCE, BYROW, BYCOL
• 3.8 Advanced Charting: Waterfall, Funnel, Gantt, Thermometer, Bullet, Sparklines
• 3.9 Dynamic Chart Ranges: OFFSET + COUNTA, INDEX, named ranges
• 3.10 Form Controls: Combo box, List box, Scroll bar, Option buttons, Check boxes
• 3.11 Advanced Conditional Formatting: Formula-based rules, entire row highlighting, icon sets, color scales
• 3.12 Camera Tool: Live linked images, dashboard assembly
• 3.13 Power Pivot (Data Model): Relationships, Star Schema, Fact vs Dimension
• 3.14 DAX Fundamentals: Calculated columns vs Measures, SUM, COUNT, AVERAGE, MIN, MAX
• 3.15 DAX Advanced: CALCULATE, FILTER, ALL, ALLEXCEPT, RELATED
• 3.16 DAX Iterators: SUMX, AVERAGEX, MINX, MAXX, RANKX
• 3.17 DAX Time Intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESBETWEEN
• 3.18 KPI Design: Denial Rate, Days in AR, NPA Ratio, Collection Efficiency
• 3.19 Macros & VBA (Basics): Record macro, relative/absolute, Personal Macro Workbook
• 3.20 VBA Editor & Variables: Dim, data types, Range, Cells, Offset
• 3.21 VBA Loops & Conditions: For Each, For Next, If Then Else, Select Case
• 3.22 VBA User Forms: TextBox, ComboBox, ListBox, CommandButton
• 3.23 VBA Error Handling: On Error Resume Next, On Error GoTo
• 3.24 Scenario Analysis: Scenario Manager, Solver Add-in

Advanced Excel Capstone: Build an interactive executive dashboard with Power Pivot, DAX measures, form controls, and VBA automation.

Section 4: Power Query (Complete)

Prerequisite: Basic Excel completed (Tables concept)

• 4.1 What is Power Query: Connection vs load, refresh behavior, snapshot strategies
• 4.2 First Power Query: Load from table, Power Query editor interface, Applied Steps panel
• 4.3 Data Types: 123 (number), ABC (text), Calendar (date), locale settings
• 4.4 Query Settings: Naming queries, Connection Only vs Load, refresh options
• 4.5 Simple Transformations: Remove columns, filter rows, sort, change data type
• 4.6 Replace Values: Find/replace in Power Query
• 4.7 Conditional Column: IF/THEN/ELSE, nested conditions, vs Custom Column
• 4.8 Custom Column: M language basics, formula bar, each and if-then-else
• 4.9 Group By: Single aggregation, multiple aggregations, vs Pivot Table
• 4.10 Unpivot Columns: Wide to long, unpivot selected vs all, unpivot other columns
• 4.11 Merge Queries: LEFT JOIN, INNER JOIN, FULL OUTER JOIN, RIGHT JOIN
• 4.12 Append Queries: UNION, same columns, different columns
• 4.13 Parameters: Dynamic file paths, switching dev/prod data sources
• 4.14 Query Folding: Performance optimization, when folding breaks
• 4.15 Best Practices: Step naming, Connection Only for intermediates, managing dependencies

Power Query Capstone: Clean and transform 1 million rows of claims data, group by payer, calculate denial rate, load only summary to Excel.

Secttion -5 ARTIFICIAL INTELLIGENCE