The Institute of Professional Accountants (Govt Regd) offers Advanced MS Excel MIS VBA Training in Delhi NCR

Advanced Excel Training Module and Flow  :

Day 1

• Introduction and Basic Overview
• Working with Workbook and Worksheets
• Formatting Cells
• Customized formatting of Cells
• Cell References (Absolute, Relative & Mixed)
• Shortcut Keys
• Other Features (Paste Special and finding and replacing data etc.)

Day 2

• Text Formulas (CONCATENATE, UPPER, LOWER, PROPER, DATE, MOD, MODE, TODAY, NOW)
• Inserting Comments
• Use of Go to Feature
• Use of “&” to Join data in different cells
• Logical formula (IF function and its use)
• Conditional Formatting and its Use in different scenarios

Day 3

• Logical Complex Formulas (IF, OR, AND &  IFERROR)
• Use of Nested IF formula with examples
• Other Text Formulas (TEXT, CLEAN, TRIM, Len,  T, COUNT, COUNTA, COUNTBLANK, COUNTIF, SUBTOTAL)
• Defining Cell Range & Name Manager

Day 4

• Applying Autofilter
• Use of Advanced Filter
• Displaying Unique Record by using Advanced Filter
• Freezing and Unfreezing Panes
• Linking of Worksheets & Workbooks
• Open websites, existing files etc by Inserting Hyperlink
• More useful formulas (RIGHT, LEFT, LEN, FIND, SUMIF & SUMPRODUCT)
• Formatting Data as Table

 

Day 5

• Financial Formulas (PMT, PV, DISC, DB, IRR, NPV etc.)
• Text to Column to fetch data
• Lookup & Reference cell Formulas (CELL, ADDRESS, ROW, COLUMN, LOOKUP, VLOOKUP, HLOOKUP, MATCH, OFFSET, SUMPRODUCT, INDEX, CHOOSE, INDIRECT etc.)
• Data Validation and its use in different scenarios

Day 6

• Understanding the Page layout of the workbook (Orientation, Print preview, Page Break etc.)
• Consolidate the data range from multiple sources.
• What if Analysis (Goal Seek, Scenario Manager and Data Table)
• Protecting worksheet and workbook
• Customized Protection of worksheet
• Sharing Workbook
• Track and Highlight Changes
• Encrypting Document

Day 7

• Understanding & Preparing Dashboards.
• Group and Ungroup Data
• Formula Auditing (Trace Precedents/Dependents, Error Checking, Show/Evaluate Formula, Watch Window)
• Applying Subtotal to a Data Source
• Export and Import Data from (Different Sources like Access, Web, Text, CSV etc.)

Day 8

• Understanding Macros
• Recording new Macros
• Understanding the use of Form Controls
• Inserting User form / Module using VBA
• Creating own formulas using VBA
• Protecting and working on VBA projects