Advance Excel Training Institute In Gurgaon and Delhi NCR

The purpose of this hands-on course is to gain an understanding of how Excel VBA can save you time through automation of repetitive Excel tasks. It is delivered with tutor-led mini exercises, designed to build up programming confidence. Here we spend a good amount of time on language structure and syntax. Apply the concepts to a real-life business problem, where delegates are given freedom to design a solution with tutor guidance.

 Key Learning Outcomes:

• Record, write, edit, test and run VBA macros with confidence
• Automate repetitive Excel business tasks and streamline workflow 
• Write and apply User Defined Functions (UDFs)
• Implement event procedures

Who Should Attend

The course would suit anyone with a basic understanding of Excel, who is interested in learning about how VBA can save them extensive amounts of time in their role. No prior coding experience is required. 

Advanced Excel Course – Overview of the Basics of Excel

~ Customizing common options in Excel ~ Absolute and relative cells ~ Protecting and un-protecting worksheets and cells

Advanced Excel Course – Working with Functions

~ Writing conditional expressions (using IF) ~ Using logical functions (AND, OR, NOT) ~ Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX) ~ VlookUP with Exact Match, Approximate Match ~ Nested VlookUP with Exact Match ~ VlookUP with Tables, Dynamic Ranges ~ Nested VlookUP with Exact Match ~ Using VLookUP to consolidate Data from Multiple Sheets

Advanced Excel Course – Data Validations

~ Specifying a valid range of values for a cell ~ Specifying a list of valid values for a cell ~ Specifying custom validations based on formula for a cell

Advanced Excel Course – Working with Templates

~ Designing the structure of a template ~ Using templates for standardization of worksheets

Advanced Excel Course – Sorting and Filtering Data

~ Sorting tables ~ Using multiple-level sorting ~ Using custom sorting ~ Filtering data for selected view (AutoFilter) ~ Using advanced filter options

Advanced Excel Course – Working with Reports

~ Creating subtotals ~ Multiple-level subtotals ~ Creating Pivot tables ~ Formatting and customizing Pivot tables ~ Using advanced options of Pivot tables ~ Pivot charts ~ Consolidating data from multiple sheets and files using Pivot tables ~ Using external data sources ~ Using data consolidation feature to consolidate data ~ Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field) ~ Viewing Subtotal under Pivot ~ Creating Slicers ( Version 2010 & Above)

Advanced Excel Course – More Functions

~ Date and time functions ~ Text functions ~ Database functions ~ Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Advanced Excel Course – Formatting

~ Using auto formatting option for worksheets ~ Using conditional formatting option for rows, columns and cells

Advanced Excel Course – Macros

~ Relative & Absolute Macros ~ Editing Macro’s

Advanced Excel Course – WhatIf Analysis

~ Goal Seek ~ Data Tables ~ Scenario Manager

Advanced Excel Course – Charts

~ Using Charts ~ Formatting Charts  ~ Using 3D Graphs  ~ Using Bar and Line Chart together  ~ Using Secondary Axis in Graphs  ~ Sharing Charts with PowerPoint / MS Word, Dynamically  ~ (Data Modified in Excel, Chart would automatically get updated)

Advanced Excel Course – New Features Of Excel

~ Sparklines, Inline Charts, data Charts ~ Overview of all the new features

 

Introduction to Macros

• Recording sub procedures
• Running macros with form controls, shapes, shortcut keys

Tour of the Visual Basic Editor (VBE)

• Location and uses of the VBE windows: Project explorer, properties, code, immediate, locals
• Customizing the look and feel of the VBE, including window docking

Best Practice Writing and Editing of Code

• Language structure: Objects, properties and methods (and optional parameters)
• Using the Intellisense list (CTRL+SPACE)
• Testing code with the immediate window, including debug.Print
• With…End with to assign multiple properties
• Using comments/remarks
• VBA functions
• Common error-types: Syntax, compile, run-time
• Getting Help: F1, macro recording, web resources

Using Variables and Data Types

• Declaring and assigning values to variables and constants
• Data types, e.g. integer, double, string, Boolean
• Using option explicit, Intellisense list to avoid spelling errors and speed up the coding process

User Interaction

• Message boxes – to display information and progress
• Input boxes – to collect data or obtain user feedback

Controlling Flow and Making Decisions

• Loop structures
  • For…Next
  • Do While/Until…Loop
  • For Each…Next (for Collections)
• If, then, else construction
• Counters and flags
• Calling Excel functions
• Debugging and testing code with step through (F8), breakpoints (F9), locals window
• Halting procedures with CTRL+BREAK/PAUSE (or ALT + ESCAPE)

Efficient Error Handling

• Common error types: Syntax, Compile, Run-time
• On Error Resume Next (and its dangers)
• On Error Goto [Label]
• Handling Error Numbers
• Resume Next and Exit Statements

Optional Class Challenge (Time Permitting): Loop within a Loop

Nested IF and Select Case

• IF, Else, Else IF structures
• Select Case, Case Is, Case Else Structures
• Building these into more complex UDFs

User Defined Functions (UDFs)

• Specifying Function Arguments
• Calling UDFs from Excel and sub procedures

Class Guided Project: Automated Data Importing and Processing (Approx. 4 hours)

The focus of this real-world project is on importing and processing data. We will consider at least:

• Importing
  • User Form Design with check boxes, command buttons, labels, etc.
  • Hungarian Notation (three-letter prefixes) for more robust coding
  • Working with variable file paths
  • Importing CSV files
  • Variable scoping: Procedure, module, project
• Processing
  • Handling dynamic ranges and locating the last row in a spreadsheet
  • Outputting and formatting internal VBA calculations
  • Optimizing the code, e.g. turning off calculations, alerts, screen updates, etc.
  • Testing with breakpoints (F9)

Event Procedures

• Worksheet events, e.g. Selection Change, Change
• Workbook events, e.g. After Save, Open, Before Close, New Sheet
• Non-object events, e.g. On Time