Corporate Training in Advanced Excel

IBAF do conducts Corporate Training in Advanced Excel since Business managers often need to take decisions that can affect the business for example detail of sales with distributor’s network. While it is not possible to use complex formulas by hand and immediately generate a report with accuracy and efficiency, hence routine task of feeding inputs and obtaining suggestion/ reports as output with the help of computer application like MS Excel spread sheet is very common these days. In any MIS environment standard reports are generated by advanced functions of Ms Excel , for comparing actual performance with original budget. Keeping in mind the present business scenarios it has become compulsory to learn MIS drafting and analyzing by joining MIS training which mainly deals with advanced Ms Excel and its applicability.

During the MIS training at IBAF our primary focus remains on the following areas:-

• Various MIS techniques in Advanced Excel VBA Programming
• Getting an overview of Analytical Process
• Data handling in MIS Reports
• Reporting and Formatting
• Fundamentals of data handling, MIS, EXCEL

IBAF is the best Excel classes Training Institute in Dehradun and the following features prove our superiority:-

• Lowest Fee
• Full Course covering every advanced topic
• Experienced Corporate Trainers
• Fully Air conditioned class rooms
• Small batch sizes of 7 to 8 students
• Individual attention
• Standard study material
• Free Excel Templates

Curriculum (Advance Excel)

Introduction to Ms-Excel

• Introduction to Ms-Excel Options, Ribbon, Sheets
• Difference between Excel 2007, 2010 & 2013
• Saving Excel File as PDF, CSV & Older Versions
• Using Excel Shortcuts with Full List of Shortcuts
• Copy, Cut, Paste, Hide, Unhide & Link Data in Rows & Columns & Sheet
• Using Paste Special Options
• Formatting Cells, Rows, Columns & Sheets
• Protecting & Unprotecting Cells, Rows, Columns & Sheets with or without Password
• Page Layout & Printer Properties
• Inserting Pictures & Objects in worksheets

Working with Formulas/ Functions

• Lookup & Reference Functions
• Logical Functions
• Date & Time Functions
• Information Functions
• Math & Trigonometry Functions
• Statistical Functions
• Text Functions

Conditional Formatting

• Conditional Formatting Using Conditional Formatting
• Using Color Scale & Icon Sets in Conditional Formatting
• Creating New Rules & Managing Existing Rules

Data Sorting & Filtering

• Sorting Data A-Z & Z-A
• Using Filters to Sort Data
• Advance Filtering Options

Pivot Tables

• Creating Pivot Tables
• Using Pivot Tables Options
• Changing & Updating Data Range
• Formatting Pivot Table & Making Dynamic Pivot Tables

Pivot Charts

• Creating Pivot Charts
• Types of Pivot Charts & Their Usages
• Formatting Pivot Charts & Making Dynamic Pivot Charts

Advanced Excel Training Module and Flow:

Day One

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

Day Two

• 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 Three

• 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 Four

• Applying Auto filter
• 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 Five

• 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 Six

• 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 Seven

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

Day Eight

• 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

Our Trainer

IBAF trainers are Microsoft Certified Trainer, Microsoft Certified Specialist for Excel 2007/2010/2013, Microsoft Certified Specialist for Microsoft Office 2007/2010; All are associated with the application of Information Technology with well over 10 years. Apart from diversified development assignments, most of them actively involved in training and teaching professionals with many institutions as visiting faculty member, and taught hundreds of professionals.

Our trainers expertise in training area: (Hands on Workshops with practical examples)

• Microsoft Excel Advance 2003/2007/2010/2013
• Microsoft Excel VBA Programming
• Presentation Skills with MS PowerPoint 2003/2007/2010/2013
• Microsoft Word 2003/2007/2010/2013

Who can join?

• Students Passed 12th and Graduates
• MBA (Finance, HR, Marketing)
• Engineers
• Chartered Accountants
• Managers (working with small and large companies)
• Working professionals
• MIS Executives
• MIS Personals
• Accounts Personal
• Project Managers
• Research Workers
• Freelancers

Course Duration : 2 Months