Duration 3 days – 21 hrs
Prerequisites
- Excel Basic & Intermediate
Course Content
Day 1
Module 1: Managing workbooks and worksheets
- Working with multiple worksheets
- Linking worksheets with 3-D formulas
- Linking Formulas Across Multiple Workbooks
- Grouping of Worksheets
- Consolidating of Data
Module 2: Grouping and Subtotals
- Grouping of data
- Creating subtotals
Module 3: Tables, Conditional Formatting & Quick Analysis Tools
- Sorting and filtering data
- Working with tables
- Create a conditional formatting rule
- Conditional formatting presets
- Using Quick Analysis Tools Window
Day 2
Module 4: Advanced Functions
- Logical Functions / Text Functions
- Statistical Functions
- Date / Time Functions
Module 5: Lookup Functions & Smart Lookup
- Using lookup functions
- Using Match and Index
- Using Xloop Functions
Module 6: Data Validation
- Validating cell entries
- Creating customized formulas
Day 3
Module 7: Pivot Tables and Pivot Charts
- Working with PivotTables
- Rearranging PivotTables
- Formatting PivotTables
- Using Slicers
- Pivot Charts
- Creating Dashboards using Pivot Charts
Module 8: Introduction to Power Pivot & Power Query
- Working & enabling Power Pivot
- Working with Power Query
Module 9: Analytical Tools
- Goal Seek
- Scenario Managers
- Solver
- Data Table
Module 10: Introduction to Excel Macros
- Create and run Macros
- Modify Macro codes using VBA editor
