• LOGIN
  • No products in the cart.

COURSE DESCRIPTION

Introduction and Basic Brush-up of MS Excel and Excel Back-End Operations
Start-up with MS Excel, Quick review on MS Excel Customize Ribbon, Quick Access Toolbar, Mini Toolbar.
Conversion of Excel files to PDF.
Introduction of Excel shortcut keys: Chart will be provided by Advanced Excel.
Introduction to Excel Worksheet, Row, Column, Cells etc.
Detailed discussion on Excel design, Back-End working structure and Excel Options.
Use of Basic Operators
Introduction to the Data and Data Formats.
Inserting, Deleting, Moving, and linking the data in between the multiple sheets.
Start-up with Formulas: Part-I AND Working With Name Ranges
Text Functions
Combine multiple formulas
Writing of the Complex formulas for improvement in formulas writing skills.
Date and Time Functions
Basic If Formulas
If, If with OR, If with AND, If with AND &OR, If with OR&AND.
If with Trim, If with Concatenation, If with Left, Mid, Right.
If with Other formulas, Complex formulas writing in If.
Advanced Level if Formulas
Nested If (For Multiple Conditions), If condition used more than one time in the same formula.
Production Store Management template with the help of Nested If. (Example)
Nested if with left, Mid, Right
Super Advanced Level If Formulas: (Huge Formula Writing)
Nested if with Multiple Text Functions in single formula.
Preparation of the TAX Calculation Sheet by Nested if (Based on Indian and US Tax)
Nested if with Other Critical Lookup Formulas (To be discussed after Super Advanced Look-up Class).
Nested if with Look-up (To be discussed in Super Advanced Look-up Class).
Other Complex Nested if to be discussed in Class.
Introduction to Name Manager

  • Use of Name Manager.
  • Create Name Ranges Automatically.
Start-up With Formulas: Part-II And Use of Tool Find &Select, (Go To Special)
Math & Trig Functions
Use of Sum product as lookup and in various activities.
Statistical Functions
Use of Sum if, Sumifs, Count if, Counties, Average if, Averaefis with the Name Ranges.
Informative AND Other Functions
Start-up With Formulas: Part-III: Lookup Functions
Lookup Functions
Basic Level Lookup Formulas

  • Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset, Choose, Rows, Columns.
  • VLookup with Name Range.
  • Hlookup with Name Range.
Advanced Level Lookup Formulas
Vlookup with Match, Hlookup with Match.
Lookup from the Left side of Data. (Lookup, Index and Match)
Vlookup with Left, Mid and Right.
Vlookup with Multiple Text Functions.
Hlookup with Multiple Text Functions.
Super Advanced Level Lookup Formulas: (Huge Formula Writing)
Vlookup from multiple Data Ranges. (With the help of Logical and Lookup Functions).
Vlookup with arrays.
Hlookup with arrays.
Vlookup with other Multiple Lookup functions.
Hlookup with other Multiple Lookup functions.
Creation of Hyperlink.
Magic with Array Formulas
What are the Array Formulas, How Array Formulas work and Use of the Array Formulas, Acceptance of Array Formulas in today’s scenario?
Basic Array Formulas Example.
Linking of Spread sheet with the help of Array
Array in Multiple formulas.
Array with Lookup functions.
Advanced Use of formulas with Array.
Use of Sum product in critical situations.
Structuring Of The Data & What If Analysis
Use of Sorting to arrange the data in ascending and descending order. Addition and deletion of levels to sort the data on multiple parameters.
Use of Sorting to arrange the data in Left to Right Order. Addition and deletion of levels to sort the data on multiple parameters.
What if Analysis: Detailed Discussion On What if Analysis. Analysis of Data by using Scenario Manager and Data Table and Goad Seek.
Conditional Formatting and Working with Charts
Working With Charts: Detailed discussion on graphically presentation of Data by using Charts.
Presentation with different kind to Basic charts like Column Charts, Line Charts, Pie Charts, Bar Charts, Scatter Charts
Preparation of Advanced Level of Charts: Gantt Chart, Bubble Chart, Speedo Meter Chart. Pareto Chart, Waterfall Chart.
Introduction to Multiple switches and buttons: Like Form Control, Combo Box, Check Box, Spin Box, List Box and Option Box.
How to Use Switches with Offset function effectively in Charts?
Preparation of the Interactive Charts.
Preparation of the Dashboards: Different of Kind of Advanced level of Dashboards
Use of Formulas Like Offset, Match, Sum if, Sumifs and many more to prepare the Dashboards.
Use of Sparkline to your Sheet, Interactive Sparkline’s
Working with 2axis and 3axis charts.
Use of Data Validation in Charting.
Pivot Table and Pivot Charts with Slicer and Handling of Errors In Excel
Start with Pivot Table, Do the Multiple Field Setting in Pivot Table.
Pivot form the Multiple Source of Data.
Pivot from the Multiple Data Ranges.
Pivot from the Name Range.
Insert slicer in Pivot Table and Pivot charts.
Use of Tool “Error Checking”.
Removing Duplicates.
Trace Precedents, Trace Dependents.
Discussion of Errors and handling of multiple errors like.
What is Circular References error? How to rectify Circular Reference?
Start with VBA Macro Programming: Recording of Macros and Writing of Macro
Introduction of VBA
Display of Developers Tab and VB Editor.
Macro Security.
Introduction to Macro Recordings
Working on Various Examples of Macro recorder.
Introduction to the writing of basic VBA codes.
Working in the Visual Basic Editor
Saving Off Your Macro.
Introduction to the Personal Macros.
VBA Macro Programming: Programming Writing Concepts & Data Types
Defining Variables to Macro.
Variable Naming rules, Declaring, Initializing Variables, Option Explicit, Object Variables
Scope and lifetime of variables.
Discussion on different Types of Variables.
Use of Local Window.
Working on Programming, Understanding on VBA Language.
Introduction of Message Box in Macro.
Introduction of Input Box in Macro.
Use of decision making statements in Macro by using: If and Else conditions, And /OR conditions, Select-Case and Not statements.
VBA Macro Programming: Working with Loops and Range Objects
Introduction of the Loops in Macro.
Use of Different type of loops
Working with Range Objects: Selecting, Coping and Pasting the Ranges and extract the data on same Worksheet level.
Selecting, Coping and Pasting the Ranges and extract the data on different Worksheet level in the same Workbook.
Selecting, Coping and Pasting the Ranges and extract the data on different Workbook
Level.
Consolidation from the multiple worksheets to a single worksheet by using range object and multiple loops.
Consolidation from the multiple workbooks to a single workbook by using range object and multiple loops.
Use of Multiple Inner Loops in complicated situations.
VBA Macro Programming: Writing of Formula in VBA and Error Handling
Introduction of programming concepts for writing the Excel Formulas in to the Program.
Difference between the A1 and R1C1 style of writing formula in VBA.
Look at the style A1 and R1C1 and decide which is better.
Find out the better solution to write the formulas in VBA.
Writing of the basic formulas in to VBA.
Writing of the complex formulas in to the VBA.
Writing of Logical formulas and Lookup formulas in VBA.
Freezing of the formula in VBA.
Other useful formulas to be write in Class.
Types of Errors: Design Errors, Compile Errors, Runtime Errors. Logical Errors.
How to Debug the Errors?
VBA Macro Programming: Advanced VBA Functions
User Define Function (UDF)
ADD-IN: Discussion on ADD-IN: What is ADD-IN, Use of ADD-IN and How to create ADD-IN.
Working with VBA Events: Discussion on VBA Events, what are Events, How and when to use the VBA Events.
VBA Macro Programming: Form Vs. Active X Controls & Advanced User Forms
Using User Form Controls
User Form Techniques and Tricks
Form Control vs Active X control, Accessing Your Macros through the User Interface.
VBA User Forms

 

Interested in learning Excel Advanced?

0 responses on "Excel Advanced"

Leave a Message

Your email address will not be published. Required fields are marked *

Course Reviews

4

4
1 ratings
  • 5 stars0
  • 4 stars0
  • 3 stars0
  • 2 stars0
  • 1 stars0

No Reviews found for this course.

© 2018 Schoolfortraining.Powered By Embtelsolutions.com
Note: School for Training is a vocational school. We promise quality education. We do not promise a job.
WhatsApp