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
  1. IF, COUNTIF, SUMIF, etc VLOOKUP, etc


• MODULE II: Review of the Formulas Tab
  1. Name Manager/ Naming Cells
  2. Evaluating Errors in Formulas
  3. Defining Errors
  4. Evaluating Formulas and Dependent Tracking o Watch Window Calculation Options


• MODULE III: Intermediate Excel Functions
  1. Logical Functions: AND, OR, IFERROR, ISERROR
  2. Statistical Functions: COUNTIES, AVERAGEIF/S, SUMIFS, AGGREGATE, MAX& AVERAGEA, etc
  3. Lookup Functions: VLOOKUP, HLOOKUP, CHOOSE, MATCH, INDEX
  4. Date and Time Functions
  5. Financial Functions
  6. Array Functions


• MODULE IV: Database Management
  1. Data Validation
  2. Creating Advanced Dropdown lists o Formula- Based Data Validation
  3. Consolidation Tools
  4. Creating 3D Formulas
  5. Consolidation Tool
  6. Using Groups and Subtotals


• MODULE V: Tables and Charts
  1. Creating Tables
  2. Creating and Editing Charts
  3. Using Sparklines


• MODULE VI: Conditional Formatting
  1. Data Bars, Color Scales, and Icon Sets
  2. Formula- Based Conditional Formatting


• MODULE VII: Analysis Tools
  1. Goal Seek
  2. Scenario Manager
  3. Data Tables


• MODULE VIII: Advanced PivotTables and PivotCharts
  1. Using Slicers
  2. Creating Summary Pivots
  3. Using Groups
  4. Adding Calculated Fields and Items
  5. Layout and Themes
  6. Creating PivotTable and PivotCharts o Drilling Up and Drilling Down
  7. Security Options
  8. Protecting Cell Contents and Worksheets
  9. Editing Permissions
  10. Protecting the File
  11. Other Security Options


• MODULE IX: Introduction to Macros
  1. Macros
     1. Recording and Running a Macro
     2. Assigning Macro to Images or Buttons
     3. Editing Macros
  2. Introduction to Visual Basic for Applications
     1. Essential Codes
     2. Creating Userforms and its Operations
     3. Creating IF statements in VBA