Posted on

Course Content

Course Content

Course 1:        Basic Excel

Basic Excel

  • Create New, Save, Delete Sheet, Workbook File
  • Sorting and filtering data
  • Cell, Column, Row, Worksheet and Workbook.
  • Sum , Sumif & Sumifs
  • Average Max and Min
  • Basic Operator (+ – / *^)
  • Greater then equal to or less then (< = >)
  • Round , Roundup and Round down
  • If and Else
  • Conditional Formatting
  • V-lookup
  • True & False
  • 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
  • Data Recovery
  • Importing Data from another location or file.
  • Sorting Data on single and multiple criteria.
  • Protection of Excel Files, Workbook, Worksheet and Cells
  • Find Replace Blank Cells & Format Painter
  • Hyperlinks
  • Naming a Range, Cell etc.

 

  

Course 2:        Basic Excel + Advance Excel

Functions

  • Left, Right, Mid & Trim
  • Proper, Upper, Lower and Clean…..etc.
  • Find & Replace
  • Exact 

Date and Time Functions

  • Day
  • Days360
  • Now
  • Today
  • Time…etc.

Look Up & Reference functions

  • Lookup& Hlookup
  • Address
  • Choose
  • Index
  • Match
  • Rows
  • Transpose…etc.

Mathematical Functions

  • SumIFS
  • Even
  • Combine
  • Odd
  • Product
  • Rand
  • Randbetween

Statistical Functions

  • Len
  • Count
  • Count A
  • CountIF,CountIFS
  • Count Blank
  • Rank
  • Average,AverageIF &AverageIFS
  • Subtotal Sign.

 Information Functions

  • Cell
  • ISERROR
  • ISNA
  • ..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
  • Concatenate & “&”

Basic Macros

  • VBA Editor-Info
  • MsgBox
  • Adding Workbook
  • Adding Sheet
  • Range…etc. 

Introduction to Excel and Macros

  • 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.
  • Combining multiple Formulas in one
  • Recording and running macro
  • Storing macro in different books
  • Creating buttons and assigning macro on it
  • Sorting and filtering data
  • Creating custom menus and toolbars
  • Assignment
  • Identify where we can record macros
  • Find VBA codes using record macros

 

Course 3:        Complete Excel Advance Automation with VBA Macros (VBE)

Introduction to VBE (Visual Basic Editor)

  • Introduction and Understanding the VBE
  • 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 ÷)
  • Offset
  • R1C1 Concept
  • Variable Declaration
  • Working with Cell, Column, Row, Range, Worksheet and Workbook.
  • Copying and pasting
  • Array
  • Reading Text File Using File Picker(File Dialogue)
  • File System Object
  • Using Immediate Window.
  • ActiveCell & Selection
  • ThisWorkbook & ActiveWorkbook
  • Selected Sheets & Active Sheet.

Introduction to User forms and its Toolbox

  • Variables, data types and constants
  • Creating Data Entry forms and other Use of User forms.
  • Working with VBA Toolbox and forms
  • Creating Add-ins

Excel Re-Engineering

  • Creating functions
  • Error Handling
  • Call Macros
  • Pivot Table
  • Free VBA Source Code
  • Assignment