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.

What-IF Analysis

  • Formula based Conditional Formatting
  • 3-D Data Consolidation

Data Lookup

  • INDEX() and 2 MATCH()

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
  • Hide-Unhide Columns & Rows

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

Warm-up

  • Essential shortcuts
  • AutoFill options
  • Paste Special (Value, Transpose)
  • Absolute & Relative referencing ($)
  • ROUND()

Macros

  • Data Tables, Goal Seek
  • Form Controls – Spin Button & Scroll Bar

Special Charts (with Videos)

  • Concept; Macros Recorder
  • Record & Run

Special Charts (with Videos)

  • Thermometer chart
  • Two-axis chart
  • Trend line chart
  • Exploded Pie-chart

MIS reporting

  • Formula based Conditional Formatting
  • 3-D Data Consolidation

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

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
  • 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
  • Working with User Forms & User Forms events
    User form Controls
  • Data Validation & Input restrictions Effective Coding
  • Testing and Debugging your code
  • Effective Error Handling
  • 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
  • How and when to use the inbuilt help features, Object browser Common terminology
  • Terms like ‘Keywords’, ‘Compile’, ‘Debug’ etc.Recording a Macro
  • 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
  • Debugging mode, Breakpoints, Bookmarks, Watch window, Immediate window and Locals window Inbuilt VBE Help feature – Tips and Tricks.
  • Fee : RS 10,000
  • Duration : 20 Hours

Apply Here

If you’d like to know more about our services please use the contact form below.

DATA SCIENCE

Click Here To view
Detail Information for this course

STATISTICAL SOFTWARE CERTIFICATION

Click Here To view
Detail Information for this course

TABLEAU

Click Here To view
Detail Information for this course

BIG DATA HADOOP

Click Here To view
Detail Information for this course