Module 1 : Excel Basics Cell, Column, Row, Worksheet and Workbook. Sum & Sumif Average Max and Min Basic Operator (+ - / *^) Greater than, equal to or less than (< = >) Round , Roundup and Round down Sorting and filtering data If and Else Conditional Formatting Vlookup True & False Module 2 : Data Handling - I Data Validation Grouping,
Ungrouping and Subtotals Filters, Custom
Filters, Advanced Filters & List Type Pivot Tables &
Merge pivots from multiple locations Goal Seek Consolidate data
From multiple files Split Data using
Text to Columns Remove Duplicates Watch Window Data Recovery Module 3 : Data Handling - II Importing Data from another location or file. Sort Data on single & multiple criteria. Protect Excel Files, Workbook, Protect Worksheet and Cells Find Replace Blank Cells Format Painter Hyperlinks Naming a Range, Cell etc. Module 4 : Advance Excel a) Text Functions LEFT,RIGHT,MID & TRIM PROPER, UPPER, LOWER, Clean…..etc. FIND & REPLACE Exact | Module 4 : Advance Excel... continued b. Date and Time Functions Day Days360 Now Today Time…etc. c. Mathematical Functions SumIFS Even Combine ODD Product RAND RANDBETWEEN d. Statistical Functions Len Count Count A CountIF, CountIFS Count Blank Rank Average,AverageIF &AverageIFS SubtotalSign. e) Logical Informatics Functions CONCATENATE & “&”.Cell ISERROR ISNA INFO...etc. Formula editing mode. Show Formula Error Checking Calculations Options. And & OR IF & Nested IF IF Error , Is Error & ISNA Multiple IF Conditions IF….Vlookup….Match IF…Hlookup….Match f) Look Up & Reference Functions Lookup & H-lookup Address Choose INDEX MATCH Rows Transpose…etc. | Module 5: Macro & VBE Basics Excel and Visual Basic Editor Cell, Column, Row, Worksheet and Workbook. Basic formula SUM, Max and Min, If & Else, Vlookup, Sum, Mid, Is Error Etc. Combine Multiple Formulas in One Record and running macro Store Macro in different books Create Buttons & Assign Macro to it Sort and Filter Data Create Custom Menus & Toolbars Module 6: Automation with VBA Macros Introduction to VBE (Visual Basic Editor) Edit Macro Using VBE How to start write code Create Conditions (If…. Elseif …… Else or Select Case) Loop constructs (For….. Next, Do….. Loop, While… Wend) Logical Operators ( + - x ÷) Working with Cell, Column, Row, Range, Worksheet and Workbook. Copying and pasting Variables, data types and constants Creating Data Entry forms and other Use of User forms. Working with VBA Toolbox and forms Creating Add-ins Assignment Module 8 : Excel
Re-Engineering Creating functions Error Handling Call Macros Pivot Table . Module 9 - MS - Access & SQL Basics Introduction to MS - Access, Create Query, Tables & Relationships, Basic Queries Using SQL Import & Export Data from Other Sources Data Queries Left Inner & Right Joints SQL Expressions Terminology SQL Basic - DDL, DML, DCL, DQL Import, Export, Joints,Truncate Database key |