Advance Excel Training Syllabus
0
Day 1
EXCEL INTRODUCTION (5 min)
1. An overview of the screen, navigation and basic spreadsheet
concepts
2. Various selection techniques
3. Shortcut Keys
USING BASIC FUNCTIONS (10 min)
1. Using Functions – Sum, Average, Max, Min, Count, CountA
MATHEMATICAL FUNCTIONS (20 min)
1. SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs
TEXT FUNCTIONS (10 min)
1. Upper, Lower, Proper
2. Left, Mid, Right
3. Trim, Len, Exact
4. Concatenate
5. Find, Substitute.
DATE AND TIME FUNCTIONS (10 min)
1. Today, Now
2. Day, Month, Year
3. Date, Date if, DateAdd
4. EOMonth, Weekday
FORMATTING AND PROOFING (10 min)
1. Formatting Cells with Number formats, Font formats, Alignment,
Borders, etc
1
2. Basic conditional formatting
Day 2
ADVANCED PASTE SPECIAL TECHNIQUES (10 min)
1. Paste Formulas, Paste Formats
2. Paste Validations
3. Transpose Tables
Sorting and FILTERING (15 min)
1. Filtering on Text, Numbers & Colours
2. Sorting Options
3. Advanced Filters on 15-20 different criteria(s)
LOGICAL FUNCTIONS (15 min)
1. If Function
2. How to Fix Errors – IFERROR
3. Nested If
DATA VALIDATION (10 min)
1. Number, Date & Time Validation
2. Text and List Validation
LOOKUP FUNCTIONS (20 min)
1. Vlookup / HLookup
2. Index and Match
3. Worksheet linking using Indirect.
PIVOT TABLES (20 min)
1. Creating Simple Pivot Tables
2. Basic and Advanced Value Field Setting
3. Classic Pivot table
2
4. Calculated Field
CHARTS and slicers (15 min)
1. Various Charts i.e., Bar Charts / Pie Charts / Line Charts
2. Using SLICERS, Filter data with Slicers
Day 3
Introduction to VBA
1. What Is VBA?
2. What Can You Do with VBA?
3. Recording a Macro
4. Procedure and functions in VBA
Variables in VBA
1. What are Variables?
2. Using Non-Declared Variables
3. Variable Data Types
4. Using Const variables
Message Box and INPUTBOX FUNCTIONS
1. Customizing Msgboxes and Inputbox
2. Reading Cell Values into Messages
3. Assign Buttons to Execute Macro
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
3
2. The Basic Do and For Loop
3. Exiting from a Loop
Mail Functions – VBA (30)
1. Using Outlook Namespace
2. Send automated mail.
3. Outlook Configurations, MAPI
