This 6-month course is designed to provide participants
with in-depth knowledge and hands-on experience in Microsoft Excel. The course will
cover advanced functions, data analysis, visualization techniques, and automation using
Excel. By the end of the course, participants will be able to use Excel efficiently for
complex data tasks and business processes.
Course Structure:
Month 1: Excel Basics and Intermediate
Functions
Week 1-2: Introduction and Excel Basics
• Course Overview and Objectives
• Navigating Excel Interface
• Basic Functions and Formulas (SUM, AVERAGE, COUNT, etc.)
• Cell Referencing (Relative, Absolute, and Mixed)
Week 3-4: Intermediate Functions and Data Management
• Text Functions (LEFT, RIGHT, MID, CONCATENATE, TEXT)
• Data Validation and Conditional Formatting
Month 2: Advanced Functions and Formulas
Week 5-6: Advanced Lookup and Reference Functions
• Advanced VLOOKUP and HLOOKUP
• Using MATCH and INDEX together
• Understanding and Using INDIRECT and OFFSET
Week 7-8: Array Formulas and Advanced Conditional Functions
• Introduction to Array Formulas
• Using SUMPRODUCT, AGGREGATE, and TRANSPOSE
• Nested IF Statements and Conditional Functions
Month 3: Data Analysis and Visualization
Week 9-10: Data Analysis Techniques
• Using PivotTables and PivotCharts
• Data Summarization and Grouping
• Calculated Fields and Items in PivotTables
• Data Analysis Toolpack
Week 11-12: Data Visualization
• Creating and Customizing Charts (Bar, Line, Pie, etc.)
• Advanced Charting Techniques
• Using Sparklines
• Conditional Formatting for Data Visualization
Month 4: Advanced Data Management
Week 13-14: Managing Large Datasets
• Sorting and Filtering Techniques
• Advanced Data Validation
• Using Tables and Structured References
• Working with Multiple Worksheets and Workbooks
Week 15-16: Advanced PivotTable Techniques
• Creating PivotTables from Multiple Ranges
• Using Slicers and Timelines
• Advanced Calculations in PivotTables
• Power Pivot Basics
Month 5: Automation with Excel
Week 17-18: Introduction to Macros and VBA
• Recording and Running Macros
• Introduction to VBA (Visual Basic for Applications)
• Understanding the VBA Editor
• Writing Simple VBA Scripts
Week 19-20: Advanced VBA Techniques
• Variables, Data Types, and Operators
• Control Structures (If, Loop, Case)
• Working with Excel Objects (Workbook, Worksheet, Range)
• User Forms and Dialog Boxes
Month 6: Integrating and Securing Excel
Week 21-22: Integrating Excel with Other Applications
• Importing and Exporting Data (CSV, Text, and XML)
• Linking Excel with Word and PowerPoint
• Introduction to Power Query
• Connecting to External Data Sources
Week 23-24: Excel Security and Best Practices
• Protecting Workbooks and Worksheets
• Using Data Encryption
• Creating and Managing Digital Signatures
• Best Practices for Data Management and Reporting
Assessment and Certification
Week 25-26: Final Project and Assessment
• Practical Project involving real-world data analysis and reporting
• Written and Practical Exam covering all course modules
• Feedback and Review Sessions
Prerequisites
• Basic understanding of Microsoft Excel
• Familiarity with basic computer operations and internet browsing
Certification
Upon successful completion of the course, participants will receive a Certificate in
Advanced Excel.
References
1. Excel 2019 Bible
• Author: Michael Alexander, Richard Kusleika, John Walkenbach
• Description: This comprehensive guide covers everything from Excel basics
to advanced features, including data analysis, VBA, and PivotTables.
• Publisher: Wiley
2. Excel 2019 Power Programming with VBA
• Author: Michael Alexander, Dick Kusleika
• Description: This book is a great resource for learning how to automate
tasks in Excel using VBA. It covers advanced programming techniques and best
practices.
• Publisher: Wiley
3. Excel 2019 for Dummies
• Author: Greg Harvey
• Description: A beginner-friendly guide that also delves into more advanced
Excel features. It's a good starting point for anyone looking to build a solid
foundation before moving on to more complex topics.
• Publisher: For Dummies
4. Excel Data Analysis For Dummies
• Author: Paul McFedries
• Description: Focuses on data analysis techniques in Excel, including
advanced formulas, PivotTables, and data visualization.
• Publisher: For Dummies
5. Microsoft Excel 2019 Data Analysis and Business Modeling
• Author: Wayne Winston
• Description: This book offers practical, real-world examples of how to use
Excel for data analysis and business modeling. It covers a wide range of topics,
including advanced functions, data analysis tools, and VBA.
• Publisher: Microsoft Press
6. Advanced Excel Reporting for Management Accountants
• Author: Neale Blackwood
• Description: This book focuses on creating advanced Excel reports and
dashboards for business and financial data analysis.
• Publisher: Wiley
7. Excel 2019 All-in-One For Dummies
• Author: Greg Harvey, Michael Alexander, and Dick Kusleika
• Description: An all-encompassing guide that covers a wide range of Excel
topics, including advanced data analysis, VBA, and more.
• Publisher: For Dummies
8. The Excel Analyst's Guide to Access
• Author: Michael Alexander, Jared Decker, Bernard Wehbe
• Description: A valuable resource for those looking to integrate Excel with
Access for more powerful data management and analysis capabilities.
• Publisher: Wiley
9. Excel Macros For Dummies
• Author: Michael Alexander
• Description: This book focuses on automating repetitive tasks in Excel
using macros. It's a great resource for learning how to save time and increase
efficiency.
• Publisher: For Dummies
10. Excel Dashboards and Reports For Dummies
• Author: Michael Alexander, John Walkenbach
• Description: A practical guide for creating visually appealing and
informative Excel dashboards and reports.
