Announcement:

Corporate Finance Institute (CF Institute) provides high-end industry oriented finance straining in the field of corporate finance, investment banking, investment research, consultancy and analytics.

About Advance Excel & VBA

Course Rating

Course Description

Advance Excel and VBA is an extremely popular & powerful tool that possesses the ability to rapidly develop professional-looking workbooks and dynamic reports to efficiently summarize and represent data.

This training offers a very well understanding of Excel & VBA application and makes user capable to completing all tasks independently and to develop programs that control Excel.

This course gives you the ability to create and share the basic employee resource schedules, a basic accounting program for check book ledger, to track product sales on a daily, weekly, monthly or quarterly basis or Tracking the total Return on Investment, creating a full-fledged customer database, create Automating repetitive operations, Creating a custom command, custom toolbar button, creating custom add-ins etc.

After completing this course, participant would be able to easily edit workbooks having multiple sheets for different purposes and situations, can work with basic & advance calculation, graphical tools and using VBA which is meant for experienced Excel users who would like to gain expertise in creating automated reports using procedures with respect to different events, understanding control structures, creating user forms, validating the data, and debugging and handling errors.

 

COURSE DURATION :
40+ Hr. (5 Weekend)

 

INDUSTRY ORIENTED CERTIFICATION:

  • Certification of Participation
  • Certificate of Excellence

CF INSTITUTE OFFERINGS:

  • 40+​hrs. of classroom training splitting into 9 days (Weekend Class)
  • Student login dashboard to manage models / content on cloud
  • Facilitator based program delivery.
  • Activities which will familiarize you with all options.
  • Real time Examples and Report Development.
  • Hands on practice which help you develop your own reports and workbooks
  • One to one attention to go with the learning flow.
  • Practice sheets / excel short-cuts etc.
  • Industry Expert Faculties
  • Certification of completion / excellence
  • 24 x 7 supports for doubt clearing

Curriculum

  • Entering and revising data
  • Moving data within a workbook
  • Finding and replacing data
  • Correcting and expanding upon worksheet data
  • Defining Excel tables

 

Advance Excel Functions for Reporting

  • Naming groups of data
  • Cell References, 3D References
  • Creating formulas to calculate values
  • Summarizing data that meets specific conditions
  • Working with iterative calculation options and automatic workbook calculation
  • Working with Advance Logical Functions
  • Working with VLOOKUP & HLOOKUP Functions
  • Replace VLOOKUP and HLOOKUP with INDEX and MATCH Function
  • Performing Dynamic calculation using INDIRECT and OFFSET Function
  • Using array formulas
  • Finding and correcting errors in calculations
  • Limiting data that appears on your screen
  • Manipulating worksheet data
  • Selecting list rows at random
  • Summarizing worksheets by using hidden and filtered rows
  • Finding unique values within a data set
  • Defining valid sets of values for ranges of cells

Reordering and summarizing data

  • Sorting worksheet data
  • Sorting data by using custom lists
  • Organizing data into levels
  • Looking up information in a worksheet

Combining Data from multiple sources

  • Managing data by using Fill
  • Grouping Multiple Sets of Data
  • Using workbooks as templates for other workbooks
  • Linking to data in other worksheets and workbooks
  • Consolidating multiple sets of data into a single workbook

Analyzing data and alternative data set

  • Defining an alternative data set
  • Defining multiple alternative data sets
  • Analyzing data by using data tables
  • Varying your data to get a specific result by using Goal Seek
  • Finding optimal solutions by using Solver
  • Analyzing data by using descriptive statistics
  • Key points
  • Creating charts
  • Customizing the appearance of charts
  • Finding trends in your data
  • Creating dual-axis charts
  • Summarizing your data by using sparklines
  • Creating diagrams by using SmartArt
  • Creating shapes and mathematical equations
  • Key points

Creating Dynamic Reports Using PivotTables and Pivot Charts

  • Analyzing data dynamically by using PivotTables
  • Filtering, showing, and hiding PivotTable data
  • Editing PivotTables
  • Formatting PivotTables
  • Creating PivotTables from external data
  • Creating dynamic charts by using Pivot Charts
  • Key points

Collaborating with colleagues

  • Sharing workbooks
  • Saving workbooks for electronic distribution
  • Managing comments
  • Tracking and managing colleagues’ changes
  • Protecting workbooks and worksheets
  • Authenticating workbooks
  • Visual Basic for Application (VBA)
  • What is the difference between Macros and VBA
  • Recording and creating a Simple Macro
  • Recording a Macro to Format Currency
  • Run the Macro
  • Assign a Shortcut Key to run a Macro
  • Look at the Macro
  • Saving a Macro enable workbook
  • Changing multiple Properties at Once
  • Manipulating Recorded Properties
  • Recording Methods in a Macro
  • Trusting Macro-Enabled Workbooks

Make a Macro to Do Complex Tasks

  • Watching a Macro Run by Stepping Through it
  • Select a File While Running a Macro
  • Record a macro for Filling the Missing Labels
  • Adding a Column of Dates
  • Appending to the Database
  • Record a Relative Reference macro
  • Create a Macro to Delete the Active Worksheet
  • Record Macros in Pieces and then Assemble them into One
  • Understanding Modules
  • Creating a Standard Modules
  • Understanding Procedures
  • Creating a Sub Procedure
  • Calling Procedures
  • Using immediate Window to call Procedures
  • Creating a Function procedure
  • Naming Procedures
  • Working with the Code Editor

Using Expression, Variables, and Intrinsic Functions

  • Understanding Expressions and Statements
  • Declaring Variables
  • Understanding Data Types
  • Working with Variable Scope
  • Using intrinsic Functions
  • Understanding Constants
  • Using Message Boxes Form
  • Using Input Boxes Form
  • Declaring and using Object Variables

Explore and Understand VBA Objects

  • What is an Object?
  • Navigating the Excel Object Hierarchy
  • Using the object Browser
  • Working with Object Properties.
  • Understanding Workbooks Object
  • Work with Worksheets Object.
  • Exploring and Working with range Objects
  • Using Data Object
  • Explore Graphical Object and their uses
  • Understanding Control-of-Flow Structure
  • Using the With Statement
  • Using Conditions with If and Switch Statements
  • Making a Double Decision
  • Test for a valid Entry
  • Understanding loops
  • Creating your first loop
  • Using the Do While….. Loop Structure
  • Using Do Until…..Loop Structure
  • Using For….To…..Next Structure
  • Using For Each……Loop
  • Managing large Loops
  • Show Progress in a loop
  • Understanding user Forms
  • Creating a Custom Form User Interface
  • Using the ToolBox
  • Working with User Form , Properties, Events, and Methods
  • Understanding Controls
  • Setting Control properties in the Properties Windows
  • Working with the Textbox Control
  • Working with Label Control
  • Working with Command Box Control
  • Working with Combo Box Control
  • Working with Frame Control
  • Working with Options Button Controls
  • Working with Control Appearance
  • Setting the Tab Order
  • Populating a Control
  • Adding Code to Controls
  • Launching a Form from Code window

Using Dialog Box Controls on a Worksheet

  • Using a Loan Payment Calculator
  • Creating an Error-resistant Loan Payment Calculator
  • Retrieving a value from a List
  • Protecting the worksheet
  • Creating a Custom Form User Interface
  • Using the ToolBox
  • Working with User Form , Properties, Events, and Methods
  • Understanding Controls
  • Setting Control properties in the Properties Windows
  • Working with the Textbox Control
  • Working with Label Control
  • Working with Command Box Control
  • Working with Combo Box Control
  • Working with Frame Control
  • Working with Options Button Controls
  • Working with Control Appearance
  • Setting the Tab Order
  • Populating a Control
  • Adding Code to Controls
  • Launching a Form from Code window

Debugging Code and Handling Errors

  • Understanding Errors
  • Using Debugging Tools
  • Setting Breakpoints
  • Using Break Mode during Run Mode
  • Understanding Error handling
  • Using VBA’s Error Trapping Options
  • Trapping Errors with the On Error Statements
  • Understanding the Err Object
  • Writing an Error-Handling Routine
  • Working with inline Error Handling

Course Price Rs.15,000 + Taxes

  • Duration – 9 classes of 5 hours each
  • Certificate of Participation / Excellence
  • Batch date – 30 July’17

Buy Now

Enquiry Form

HOW IT WORKS

Classroom Training

Starts with personalized classroom training (40+ Hrs)

Relevant Case discussion

Focus on improving working knowledge

MS Office Tips and Tricks

Exhaustive training on how to use the tools effectively

Practice assignments

Enough practice assignments to master the skills

Unparalleled Mentorship

Personalized coaching and mentorship

24*7 Support

Support for helping and enhancing student learning experience

Students Speak

Candidates who completed our in-house programs got placed in below companies.