
Excel & VBA
Course Overview
Course Outline
Part I. Basic Excel – Calculating data in Excel
1.1. Excel basics
Excel overview
Basic operations
Keyboard shortcuts
References (relative, absolute, mixed, cross-sheet)
Using Excel functions
Excel data types and conversions
Handling different data types
1.2. Excel functions and formulas
Creating formulas with Excel functions
Conditional formulas
Nesting functions
Aggregation functions
Using lookup to cross-reference data
Dealing with unique and duplicate data
Part II. Advanced Excel – Processing data in Excel
2.1. Statistics
Data type and functions
Managing text with functions
Sorting and formatting
Index, search and match
Aggregating and pivoting data
Presenting graphical data (pie, line and bar)
2.2. Processing data
Enhancing the work
Invoking Macros
Adding controls in Excel
Processing large amount of data with million rows
Dealing multiple Excel and other data sources
Thinking of enhancement
Part III. Power Excel – Making magic in Excel
3.1. Introduction of VBA
Handling dynamic and various scenarios
Managing external data
3.2. The magic of VBA
Using Access database
Invoking queries
Excel in and out
Finalization