MICROSOFT

MS ADVANCE EXCEL

DESCRIPTION
After Basic and Intermediate Excel and you want to learn advanced functionalities like PivotTables, PivotCharts, and even introduction to VBA Macro, this course is for you.
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

1. Macros

Recording and Running a Macro

Assigning Macro to Images or Buttons

Editing Macros

2. Introduction to Visual Basic for Applications

Essential Codes

Creating Userforms and its Operations

Creating IF statements in VBA