+91 989 971 4317

Excel & VBA Training

Online Sas Training Institute

Learn Through Case Studies and Get a Project Completion Certificate.

The goal of this Section is to help you to learn how to use Microsoft Office Excel formulas, functions and macros (VBA) through our free and good quality training video tutorials. Our coming videos will demonstrate how you can use MS-Excel for storing, organizing and manipulating large amounts of data.You should be able to find solutions to many financial calculations and what-if scenarios quickly after studying the presented help articles and content. Why learn Excel? Why is MS-Excel so important in today's work and business environment? Microsoft Office Suite is used by nearly every employer, school and university in the world and the more extensive your skills with the application suite, particularly Excel spreadsheets, the more attractive a candidate you are when applying for jobs. How we have organized our Excel learning course: Our program is based on the fundamental understanding of the working of the computer: Input-Process-Output. Therefore, every work that we do must take this into account. Our data entry should be done after finalizing a plan on paper even for relatively uncomplicated analysis. The process should use the relevant formulas, functions and macros and Excel then takes care of optimum CPU and memory usage. Finally we need to get our output for various purposes like printing a hard copy or preparing a presentation for our meetings. With this in mind let's see how we can benefit from Microsoft's excellent software.


Learn SAS Online
Excel Training

Excel Training

We share best use of Excel.Program is based on real scenario problem.Entire program is full of illustration and case study. Practical experience of Program Director will help to have a practical view of each function.Program is spread over couple of weekends that give time to practice and raise question in subsequent sessions.

More

Excel Courses

Warm-up
  • Essential shortcuts
  • AutoFill options
  • Paste Special (Value, Transpose)
  • Absolute & Relative referencing ($)
  • ROUND()
Data Analytics
  • Sort & Filter
  • Using SUBTOTAL() with filtered data
  • SUMIFS(), COUNTIFS()
  • Pivot Table for multi-variable analysis
  • Computations %, Sum, Max, Min, Average, Count
  • Grouping (Clustering)
  • Generating multiple reports

Data Lookup
  • VLOOKUP() – a practical perspective
  • VLOOKUP() with MATCH()

Logical Statements
  • IF(), Nested IFs, AND(), OR()
  • ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR()

Data Cleaning – I
  • LEFT(), RIGHT(), MID()
  • LEN()
  • UPPER(), PROPER(), LOWER()
  • TRIM(), VALUE()
  • CONCATENATE(), & Data Cleaning – II
  • Find & Replace (using wildcard character - Asterisk * )
  • Go To (Special)
  • Text-to-Columns (incl. advanced tricks)
  • Remove Duplicates Working with Dates
  • Date correction techniques
  • DAY(), MONTH(), YEAR()
  • EDATE(), EOMONTH()
  • TODAY()

MIS reporting
  • Automatic row-wise Subtotal
  • Conditional Formatting (Blanks, Errors, Cell Values, Duplicates)
  • File Password Select Dashboard Techniques
  • Activate-Deactivate Gridlines
  • Data Validation (list)
  • Cell-Range Naming
  • Grouping
  • Hyper linking
  • Freeze-Unfreeze panes
  • Hide-Unhide Columns & Rows

Data Lookup
  • INDEX() and 2 MATCH()

MIS reporting
  • Formula based Conditional Formatting
  • 3-D Data Consolidation

What-IF Analysis
  • Data Tables, Goal Seek
  • Form Controls - Spin Button & Scroll Bar

Macros
  • Concept; Macros Recorder
  • Record & Run

Special Charts (with Videos)
  • Thermometer chart
  • Two-axis chart
  • Trend line chart
  • Exploded Pie-chart

Select Formulas & Techniques
  • IFERROR()
  • SEARCH(), SUBSTITUTE()
  • SUMPRODUCT()
  • Cell Protection

Less...
Online SAS & Analytics Training
VBA Training

VBA Training

Microsoft Visual Basic for Applications (VBA, Macros) when used with Microsoft Excel can build powerful automated business tools & Solutions through which the impossible can be made possible . You can automate the repetitive task , Create the Global Templates, Analytical Dashboards and many more. We are the Pioneer in providing excel macro training

More

VBA Course:

Introduction To Programming Introduction to logical thinking, flowcharts & algorithms

  • • Define objective, start & end points; Identifying solution & breaking it into sequential steps Writing an algorithm
  • • Step-by-step instructions, process flow diagrams/flowcharts.Excel Macros – an introduction
  • • The Power of Macros - What can be done with Macros and When to use Macros Introduction to object oriented programming
  • • Objects, their functions, methods and properties Introduction to Events
  • • What are events, how & when to use them Preparing to ‘Macro’ Visual Basic Editor (VBE) – Developer Tab, Security
  • • Introduction to the VBE, Project Explorer, Properties window, Password protection of code How to use the VBE – Features, Options, Intellisense technology
  • • Debugging mode, Breakpoints, Bookmarks, Watch window, Immediate window and Locals window Inbuilt VBE Help feature – Tips and Tricks.
  • • How and when to use the inbuilt help features, Object browser Common terminology
  • • Terms like ‘Keywords’, ‘Compile’, ‘Debug’ etc.Recording a Macro
  • • If and when to use Macro recorder along with the code More On Excel Macros - I Structure of an Excel workbook from VBA point of view -Common Objects
  • • MS Excel Objects like Applications, Workbook, work sheets, etc. Where to write the code? Variables and Constants
  • • What are variables and constants?
  • • Type of variables; How and when to use variables to store information.
  • • Typical Variable naming conventions
  • • When to use Variables or constants More on Excel Macros - II Loops
  • • For-Next, For-Each, Do-While, Do-Until Decision-making and Code Branching
  • • If-Then-Else, Select-Case, And/Or conditions User defined functions (UDF)
  • • What are user defined functions. How to create & use them.Form Controls vs. ActiveX Controls Getting into the Code MsgBox and Input Box Working with Data in Excel
  • • Data types in Excel worksheets, like Dates, Texts, Nulls etc. Working with workbooks, worksheets, ranges, cells etc.
  • • Using offset and other cell navigation methods. Working with Arrays Power Data Processing Working with Data in Excel
  • • Working with dynamic ranges. Protecting worksheets, cells and ranges. Working with multiple files. Opening & Saving files Introduction to User Forms
  • • Working with User Forms & User Forms events
  • • User form Controls
  • • Data Validation & Input restrictions Effective Coding
  • • Testing and Debugging your code
  • • Effective Error Handling

Less...

Apply now

Back to Top