For Trainings contact: Mustafa 00971-56-6060872 firstname.lastname@example.org
EXCEL EXPERT COURSE
Learn at your own home and flexible timings and days. Call for further information: 056-6060872
Use advanced graphs in minutes instead of wasting hours trying to figure them out Increase interactivity by automating your spreadsheets with macros and VBA Solve complex problems with superpower functions Turn raw data into ‘must make’ decisions using PivotTables and PowerPivots.
12 hour course: AED 900 only 36 hour course: AED 2,500 only
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 using Offset. 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: Detailed Discussion on Arrays: 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 Sumproduct 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. Use of Filter to extract the unique and desired data. Use of Custom Filter to fulfil the desired conditions. Use of Advance Filter to fulfil the multiple desired conditions. Import the data from the multiple applications to Excel. Use of Text to Columns for Rearrangement of Data. Remove Duplicates from Data. Use of Data Validation and Consolidation. Use of Data Validation as a magical tool. Data of Grouping, Ungrouping and Subtotal.
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: Conditional Formatting: Detailed discussion on conditional formatting. Conditional Formatting with multiple cell rules and Top/Bottom Rules. Conditional Formatting with Data Bars. Colour Scales and Icon Sets. Conditional Formatting on Desired Output, Create New rules, Manage the rules. Apply any formula to Conditional formatting. Choose Formatting as Table and different Cell Style. 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: Gnatt 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, Sumif, Sumifs and many more to prepare the Dashboards. Use of Data Validation in Charting. Use of Sparkline to your Sheet, Interactive Sparklines Working with 2axis and 3axis charts.
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. Do the Juggling of Data in Pivot Table and Work on layout of Pivot Table. Creating Groups, Insert additional Calculated Field in Pivot Table. Perform the % calculation on the basis of multiple fields. Start-up with Pivot charts. 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. #DIV/0! , #N/A, # REF, #NAME, #VALUE, # NULL, #NUM and ########Error. 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: Detailed Introduction of VBA Macro Programming. Discussion on need of VBA Macros. Where to apply the VBA Macro. Benefit and limitation of VBA Macro. Type of VBA Macro. Display of Developers Tab and VB Editor. Macro Security. Introduction to Macro Recordings: Using the Excel Macro Recorder, Macro Security, Recording Macro, Naming Macro, Executing Macro, Saving and Editing Macro Various Key Board Short Cuts related to VBA Macros. Working on Various Examples of Macro recorder. Introduction to the writing of basic VBA codes. Working In the Visual Basic Editor: Project Explorer, Properties window, Object Browser, Standard Module and Sheet Module 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 like, For & Next LOOP, For & Each Loop, Do & Until LOOP While and While and Wend Loop. 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): Detailed discussion on User Defined Function: What is User Defined Function, Use of User Defined Function and How to create any Function OR Formula which is not available in Excel and you want it to work for you by the help of Macro.
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 UserForm Controls UserForm Techniques and Tricks Form Control vs Active X control, Accessing Your Macros through the User Interface. VBA User Forms: Discussion on VBA User Forms, What are User Forms, Why to Create User Forms and How to create User Forms
Uisng Power Query to Extract, Trasnsform and Load Data Data Consolidation, merging and appending Using Joins Case Studies
Using Power Pivot, Introduction and how to Load data Data modeling Using DAX functions Table Functions, Calculate