Advanced Excel Course

Advanced Excel Syllabus

1. Excel Fundamentals & Professional Formatting

• Excel Interface and Workbook Management
• Worksheets, Rows, Columns and Cells
• Data Entry and Editing
• Number, Text and Date Formatting
• Cell Styles and Professional Formatting
• Page Setup and Printing
• Freeze Panes and View Options

2. Advanced Formulas & Functions

• Relative, Absolute and Mixed References
• Mathematical and Statistical Functions
• IF, AND, OR and Nested IF
• SUMIF, SUMIFS
• COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS
• ROUND, ROUNDUP and ROUNDDOWN
• Text Functions
• Date and Time Functions
• Error Handling Functions

3. Lookup & Reference Functions

• VLOOKUP
• HLOOKUP
• XLOOKUP
• INDEX and MATCH
• Lookup with Multiple Criteria
• Practical Lookup Applications

4. Data Management & Cleaning

• Sorting and Filtering
• Advanced Filter
• Remove Duplicates
• Text to Columns
• Flash Fill
• Find and Replace
• Data Cleaning Techniques
• Handling Blank and Incorrect Data

5. Conditional Formatting & Data Validation

• Conditional Formatting
• Custom Rules
• Highlighting Duplicate Values
• Data Bars, Color Scales and Icon Sets
• Data Validation
• Drop-Down Lists
• Input Restrictions

6. Excel Tables & Advanced Data Handling

• Creating and Managing Excel Tables
• Structured References
• Table Formatting
• Dynamic Data Management
• Working with Large Datasets

7. PivotTable & PivotChart

• Creating PivotTables
• Grouping Data
• Filtering and Sorting PivotTables
• Calculated Fields
• PivotCharts
• Slicers and Timelines
• Interactive Data Analysis

8. Charts & Data Visualization

• Column, Bar and Line Charts
• Pie and Doughnut Charts
• Combination Charts
• Customizing Charts
• Dynamic Charts
• Professional Data Presentation

9. Dashboard Development

• Dashboard Concepts
• KPI Creation
• Interactive Reports
• Charts and Visualizations
• Slicers and Filters
• Designing Professional Excel Dashboards
• Real-World Dashboard Projects

10. What-If Analysis & Advanced Tools

• Goal Seek
• Scenario Manager
• Data Tables
• What-If Analysis
• Basic Solver Concepts
• Subtotal and Consolidation

11. Power Query & Data Analysis

• Introduction to Power Query
• Importing Data
• Data Transformation
• Cleaning and Combining Data
• Merging and Appending Queries
• Refreshing Data
• Preparing Data for Analysis

12. Practical Projects & Reporting

• Sales Report
• Attendance Report
• Salary / Payroll Report
• Inventory Report
• Expense Analysis
• Customer Data Analysis
• MIS Report
• Interactive Business Dashboard
• Final Practical Project