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
• 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

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

### Look Up & Reference functions

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

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

### Statistical Functions

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

• 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
• 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