Course Duration
24 hours, 3-day training workshop
Delivery
Face to face training or online training
Course Inclusive
Training materials & certificate of completion
Description
After Basic and Intermediate Excel and you want to learn advanced functionalities like PivotTables, PivotCharts, and even introduction to VBA Macro.
Course Content
- MODULE I: Review of Intermediate Excel Functions
- IF, COUNTIF, SUMIF, etc VLOOKUP, etc
- MODULE II: Review of the Formulas Tab
- Name Manager/ Naming Cells
- Evaluating Errors in Formulas
- Defining Errors
- Evaluating Formulas and Dependent Tracking o Watch Window Calculation Options
- MODULE III: Intermediate Excel Functions
- Logical Functions: AND, OR, IFERROR, ISERROR
- Statistical Functions: COUNTIES, AVERAGEIF/S, SUMIFS, AGGREGATE, MAX& AVERAGEA, etc
- Lookup Functions: VLOOKUP, HLOOKUP, CHOOSE, MATCH, INDEX
- Date and Time Functions
- Financial Functions
- Array Functions
- MODULE IV: Database Management
- Data Validation
- Creating Advanced Dropdown lists o Formula- Based Data Validation
- Consolidation Tools
- Creating 3D Formulas
- Consolidation Tool
- Using Groups and Subtotals
- MODULE V: Tables and Charts
- Creating Tables
- Creating and Editing Charts
- Using Sparklines
- MODULE VI: Conditional Formatting
- Data Bars, Color Scales, and Icon Sets
- Formula- Based Conditional Formatting
- MODULE VII: Analysis Tools
- Goal Seek
- Scenario Manager
- Data Tables
- MODULE VIII: Advanced PivotTables and PivotCharts
- Using Slicers
- Creating Summary Pivots
- Using Groups
- Adding Calculated Fields and Items
- Layout and Themes
- Creating PivotTable and PivotCharts o Drilling Up and Drilling Down
- Security Options
- Protecting Cell Contents and Worksheets
- Editing Permissions
- Protecting the File
- Other Security Options
- MODULE IX: Introduction to Macros
- Macros
- Recording and Running a Macro
- Assigning Macro to Images or Buttons
- Editing Macros
- Introduction to Visual Basic for Applications
- Essential Codes
- Creating Userforms and its Operations
- Creating IF statements in VBA