Advanced Excel

Objective : To learn the advanced functionalities available in MS Excel
Suitability : Executives & Officers at supervisory / middle management / top level
Prior excel background : Atleast 3-6 months of working experience on excel is required
Duration : 14 Hours or 2 Day
Functionalities covered
Fine points of the basics
  • Paste special
  • Autofilters
  • Advanced filters
  • Sort
  • Find/Replace

Exploring and editing the data
  • Go to and Go to special
  • sub-total menun
  • text to columns

Data management, validation and protection
  • Pivot tables
  • Conditional formatting
  • data validation
  • formula auditing
  • protecting cell, worksheet and workbook
  • Data Import
Macros
  • Recording a macro
  • Editing macros in VBA window
  • Running macros
Functions
  • vlookup and hlookup
  • if, and, or
  • Round, roundup, rounddown
  • sumif and sumifs
  • countif and countifs
  • counta, countblank
  • concatenate, trim
  • index and match
  • left, right, mid, len
  • isna, iserror, iferror, isnumber, istext
  • upper, lower, proper, roman
  • clean
  • day, month, year, eomonth, weekday

Charts
  • Pivot Charts
  • Dynamic Charts
Miscellaneous
  • Goal seek
  • Remove duplicates
  • R1C1 referencing
  • Creating dashboards