Advance Excel – Full Syllabus

1. Excel Essentials (Revision for Advanced Use)

• Working with large datasets
• Cell referencing (Relative, Absolute, Mixed)
• Advanced formatting
• Number formats (Custom)

2. Advanced Functions

a) Logical Functions

• IF
• Nested IF
• AND, OR, NOT

b) Text Functions

• LEFT, RIGHT, MID
• LEN, FIND, SEARCH
• CONCAT, TEXTJOIN
• TRIM, CLEAN

c) Date & Time Functions

• TODAY, NOW
• DATEDIF
• EOMONTH, NETWORKDAYS

d) Lookup & Reference Functions

• VLOOKUP
• HLOOKUP
• XLOOKUP
• INDEX + MATCH
• OFFSET
• INDIRECT

e) Math & Statistical Functions

• SUMIF, SUMIFS
• COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS

3. Data Cleaning & Data Management

• Remove Duplicates
• Text-to-Columns
• Flash Fill
• Data Cleaning Techniques
• Handling errors (IFERROR)
• Advanced filtering
• Sorting (Multi-level)

4. Data Analysis Tools

• Pivot Table (Advanced)
• Pivot Chart
• Slicers & Timelines
• Grouping data
• Data Consolidation
• Subtotal Reports
• What-If Analysis
• Goal Seek
• Scenario Manager

5. Charts & Visualization

• Column, Bar, Line, Pie Charts
• Combo Charts
• Trendlines
• Sparklines
• Creating KPIs
• Designing Professional Dashboards

6. Data Validation & Protection

• Drop-down lists
• Input messages
• Error alerts
• Dependent drop-downs
• Sheet protection
• Workbook protection

7. Excel Automation (Non-Macro)

• Dynamic formulas
• Dynamic ranges
• Advanced shortcuts
• Template creation
• Time-saving techniques for office work

8. MIS & Business Reporting

• Sales Report
• Purchase Report
• Inventory Report
• HR & Attendance Sheet
• Cash Flow Sheet
• Expense & Profit Analysis
• Monthly MIS Dashboards

9. Power Query (Optional Module)

• Data import
• Data transformation
• Clean & reshape data
• Append & merge queries

10. Practical Projects

• Dashboard Project
• Lookup-based project
• MIS Report Project
• Data Cleaning Project
• Business Analysis Assignment