Start Time

12:00 am

Sunday, June 16, 2019

access_time
End Time

12:00 am

Tuesday, July 16, 2019

access_time
Address

Birmingham, UK

location_on


Event Description

  • Learn Advance Microsoft Excel with Detailed Macros Training in a Live Instructor Based Online Class 
  • Duration – 24 Hrs

 

  • Training Prerequisite
  1. Basic Understanding of Excel
  2. Basic Understanding of any Programing Language
  3. Basic Understanding of Data Analysis
  4. Basic Understanding of Data Types
  • Warm Up
  1. Revise of the Excel Basic Functions
  2. Get up to speed on Terminologies
  3. Autofill
  4. Round Functions
  5. Understanding the Tabs and Ribbons
  6. 30 % Time Saving Tricks

 


Learning Objectives

Beginning Advance Excel

 

  1. Logical Statements
    1. Designing If Else
    2. Nested If
    3. Logical Versions of If
  2. Working with Dates
    1. Autofilling
    2. Creating Date Series
    3. Extracting Date Parts
    4. Understanding Date Functions in Details
  3. Data Cleaning : Text Cleaning
    1. Cleaning Text
    2. Extracting Text
    3. Text Functions : Left(), Right() and Others
    4. Removing Extra Spaces
    5. Making Text Tidy
  4. Data Cleaning : Text Manipulations
    1. Finding and Replacing Wildcard Text
    2. Goto Tricks
    3. Text Cleaning Shortcuts
    4. How to convert one Text format to other
    5. Text to columns Tricks
  5. Analysis of Data
    1. Sum Functions
    2. Subtotal Functions
    3. Multiple Conditional Sum, Count
  6. Pivoting Data : Basics of Pivoting
    1. Learn the Easy way Pivot : Multivariable Analysis
    2. Understanding the Computations in Pivot
    3. Sum vs Percentage in Pivot
    4. Understanding Pivot Functions
  7. Working with Multiple Files
    1. Understand how to merge Files
    2. Lookup Function : Vlookup Hlookup
      1. Problems in Lookup
    3. Understand One way Index Match
  8. Reporting Functions
    1. Autosum
    2. Conditional Formatting
    3. Graphs in the cells
  9. Reporting Techniques to Stand Out
    1. Trendline
    2. Data Validation
    3. Cell-Range Naming
    4. Grouping
    5. Hyperlinking
    6. Freeze Unfreeze
    7. Hide what you don’t want to show

Beginning Excel Advanced Pro

 

  1. Data Lookups
    1. Index() and Match()
    2. Dubble Match()
  2. Making Reports Better
    1. Formula Based Conditional Formatting
    2. Making Formatting Easy
  3. What If Analysis
    1. Data Table
    2. Goal Seek
  4. Select Formulas & Techniques
    1. IfError()
    2. Search()
    3. Substitute()
    4. Error Debugging
  5. Pivot Charts
    1. Make 4 Charts in one Go
    2. Making Pivot INTERACTIVE
    3. How Date Can change Pivots
    4. Interactivity Customized
  6. Making Boring Task Automated
    1. Making Simple Task Automated
    2. Understanding Macros
    3. Using Macros with Simple Changes
    4. Record and Run Macros

 

Securing Your Work

  1. Inbuilt Security Features
  2. How to lock sheets
  3. How to make sure no one edit your work
  4. Cell Locking

 

Understanding Macros In Detail

 

  1. Visual Basic Editor
  2. How to use VBE
  3. Inbuilt Features of VBE
  4. Common Terminologies
  5. Editing Recorded Macros
  6. Variables And Constants
  7. Loops and Conditions in VBA
    1. For Loops
    2. If Else
    3. Multiple Conditions
    4. Branching it better
  8. Working with Hands on Examples
    1. Hands on Example of Automations
    2. Using Loops for Processing
    3. Using Conditions for Processing
  9. Effective Codings
    1. How to write optimal Code
  10. Testing Code and Error Handling
    1. Handling Errors
    2. Remove Errors
    3. Where to get help?

Detailed Course Content

For any further clarification on the Course Content or a Customized Course for your Specific Training need please reach out at info@instrovate.com