Advanced Excel Online for Intermediate trainees by PRISM Network

MS-Excel SYLLABUS

Basic Excel

1. An overview of the screen, navigation and basic spreadsheet concepts
2. Various Selection Techniques
3. Short Cut
4. Cell Reference
5. Formatting Cells
6. Basic Formulas like Sum,Average,Max,Min,Count,Counta
7. Basic Conditional Formatting
8. Protecting the Worksheet and workbook
9. Working with Date and Time Functions
10.Pivot Table
11.Charts
12.Sorting
13.Filtering
14.Printing Workbooks
15.Creating and Formatting Tables

Advanced Excel

Mathematical Functions

Sumif,Sumifs,Countif,Countifs,Averageif,Averageifs

Text Functions

1.Uppwer,Lower,Proper
2.Left,Mid,Right
3.Trim,Len,Exact
4.Concatenate
5.Find,Search,Substitute
6.Text

Lookup Functions

Vlookup,Hlookup,Index and Match,Offset,Indirect,Choose

Logical Functions

IF,Nested IF,And,Or,Not,IFNA,IFERROR

Array Function using ctrl + shift + enter

Data Validation

1. Number Date and Time Validation
2. Text and List Validation
3. Custom validation using formula
4. Dynamic Dropdown and Dependent Dropdown

Advanced Conditional Formatting

Charts and Slicers

Dashboard

VBA Macro

1.What is VBA
2.What can you do with vba
3.Recording a Macro
4.Procedure and functions in VBA

Variables in VBA

Variable Date Types

Message and Inputbox Functions

IF and Select Statements

1. Simple IF Statements
2. The Elseif Statements
3. Defining Select case Statements

Looping in VBA

1. Introduction to Loops and its types
2. The Basic Do and For Loop
3. Exiting from a Loop
4. Advanced Loop Examples

Creating User Defined Function