Excel Trainings
EXCEL SYLLABUS
1. EXCEL FROM SCRATCH
1.1 Touring the interface
1.2 Enter data & saving workbook
1.3 Applying Formulas
1.4 Changing a worksheet’s structure
1.5 Find, Replace & Spell Check
1.6 Applying fonts, background colors, and borders
1.7 Formatting numbers and dates
1.8 Making the pieces fit
1.9 Inserting headers & footers and Repeating Rows
1.10 Managing Large Workbooks
1.11 Quiz 1
2. EXCEL ESSENTIALS
2.1 Formula Writing
2.2 Exercise of Formula Writing (Don’t Miss This)
2.3 Excel files for practice
2.4 Quiz 2
3. SUM COUNT AVERAGE FUNCTIONALITY (WITH CONDITIONS)
3.1 SumIf & SumIfs
3.2 Exercise of SumIf & SumIfs
3.3 Count Functions
3.4 Exercise of Count Functions
3.5 AverageIf & AverageIfs
3.6 Exercise of AverageIf & AverageIfs
3.7 Excel files for practice
4. TEXT FUNCTIONS (MANIPULATING TEXT)
4.1 Change case (Upper, Lower, Proper)
4.2 Exercise of Change Case (Upper, Lower, Proper)
4.3 Separating Text on Symmetrical Data
4.4 Exercise of Separating Text on Symmetrical Data
4.5 Separating Text on Non-Symmetrical Data
4.6 Exercise of Separating Text on Non-Symmetrical Data
4.7 Excel files for practice
5. LOGICAL FUNCTIONS (WORKING WITH CONDITION)
5.1 If Functionality
5.2 Exercise of If Functionality
5.3 If with AND
5.4 Exercise of If with AND
5.5 If with OR
5.6 Exercise of If with OR
5.7 If with AND & OR, IF with Countif
5.8 Exercise of If with AND & OR, IF with Countif
5.9 Nested If (A Genie with 3 wishes)
5.10 Exercise of Nested If
5.11 Excel files for practice
6. LOOKUP FUNCTIONALITY (BURN SIMPLE AND TECHNIQUE
6.1 Vlookup (Exact Match) #1
6.2 Exercise Vlookup (Exact Match) #1
6.3 Type of References (Use of $ sign)
6.4 Vlookup (Exact Match) #2
6.5 Exercise of Vlookup (Exact Match) #2
6.6 Double Vlookup
6.7 Exercise of Double Vlookup
6.8 Vlookup on Duplicate values
6.9 Exercise of Vlookup on Duplicate values
6.10 Vlookup (Approximate Match)
6.11 Exercise of Vlookup (Approximate Match)
6.12 Vlookup with IF (Conditional Vlookup)
6.13 Exercise of Vlookup with IF (Conditional Vlookup)
6.14 Hlookup (Exact Match)
6.15 Exercise Hlookup (Exact Match)
6.16 Hlookup (Approximate Match)
6.17 Exercise Hlookup (Approximate Match)
6.18 Vlookup & Match (Create Magical Vlookup)
6.19 Exercise of Vlookup & Match (Create Magical Vlookup)
6.20 Match (Gives us Column & Row Number)
6.21 Exercise of Match (Gives us Column & Row Number)
6.22 Index & Match (Made for each other)
6.23 Exercise of Index & Match (Made for each other)
6.24 Index & Match (Made for each other)
6.25 Exercise of Index & Match (Made for each other)
6.26 Excel files for practice
7. DATE & TIME FUNCTION (A TRICKY GAME)
7.1 How Excel Records Date & Timer
7.2 Now, Today & Autofill
7.3 DateValue & TimeValue
7.4 Calculate Working Days
7.5 DateIf
7.6 Excel files for practice
8. FINANCIAL FUNCTIONS (USEFUL FOR NON- FINANCE GUYS TOO)
8.1 Loan Calculation (PMT, PPMT, IPMT etc.)
8.2 Exercise of Loan Calculation (PMT, PPMT, IPMT etc.)
8.3 Creating a Loan Table
8.4 Exercise of Creating a Loan Table
8.5 Calculate Depreciation
8.6 Exercise of Calculate Depreciation
8.7 Excel files for practice
9. SORT & FILTER (COMMON FOR ALL)
9.1 Basic Sorting & Shortcut keys
9.2 Exercise of Basic Sorting & Shortcut keys
9.3 Sorting Using Custom List
9.4 Exercise of Sorting Using Custom List
9.5 Horizontal Sorting
9.6 Exercise of Horizontal Sorting
9.7 Basic Filter & Shortcut Keys
9.8 Exercise of Basic Filter & Shortcut Keys
9.9 Filter Problem # 1 (Copy Filtered Data Without hidden rows)
9.10 Exercise of Filter Problem # 1 (Copy Filtered Data Without hidden rows)
9.11 Filter Problem # 2 (Pasting Values on Filtered Data)
9.12 Exercise of Filter Problem # 2 (Pasting Values on Filtered Data)
9.13 Filter Problem # 3 (Performing Calculation on Filtered Data)
9.14 Exercise of Filter Problem # 3 (Performing Calculation on Filtered Data)
9.15 Advanced Filter (Using Complex Criteria’s)
9.16 Exercise of Advanced Filter (Using Complex Criteria’s)
9.17 Excel files for practice
10. EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)
10.1 Excel Charts Introduction
10.2 How to create a chart
10.3 Link a Chart Title
10.4 How to Show Axis Title
10.5 How to Show Data Label
10.6 Column Chart
10.7 Bar Chart
10.8 Line chart 1
10.9 Line Chart 2
10.10 Area Chart
10.11 Pie Chart
10.12 Pie of Pie or Bar of Pie
10.13 Line Chart with Log Scale
10.14 Bubble Chart
10.15 Selecting Chart Element
10.16 Use of Format Dialog box
10.17 Modifying Data Series
10.18 How to handle Missing Data
10.19 Modifying 3-D Charts
10.20 Creating a Chart Templates
10.21 Creating Picture Charts
10.22 Creating Combination of upto 5 Different Charts
10.23 Show Data with chart in Data Table
10.24 Play with Design & Layout of chart
10.25 Excel files for practice
11. DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)
11.1 Consolidate Data using Excel Inbuilt Function
11.2 Exercise of Consolidate Data using Excel Inbuilt Function
11.3 Excel files for practice
12. DATA VALIDATION
12.1 Applying Data Validation
12.2 Exercise of Applying Data Validation
12.3 Dependent Dropdown List
12.4 Exercise
12.5 Using Data Validation with Vlookup & Match
12.6 Other Data Validation Example
12.7 Excel files for practice
13. PROTECTION OF WORKSHEET WORKBOOK
13.1 Worksheet Level Protection
13.2 Exercise
13.3 Cell Level Protection
13.4 Exercise
13.5 Hiding Formulas from Formula Bar
13.6 Exercise
13.7 Protecting Workbook Structure
13.8 Exercise
13.9 Workbook Level Protection
13.10 Excel files for practice
14. PIVOT TABLE & TECHNIQUE
14.1 Understanding pivot Table
14.2 Using Pivot Table in Real Scenario
14.3 Exercise
14.4 Grouping in Pivot Table
14.5 Exercise
14.6 Using Slicers (A Visually Attractive Filter)
14.7 Exercise
14.8 Excel files for practice
15. WHAT – IF ANALYSIS & SOLVER ADD-IN
15.1 Goal Seek (A Reverse Approach)
15.2 Exercise
15.3 One-way Data Table
15.4 Two Way Data Table
15.5 Scenario Manager
15.6 Solver Add-In
15.7 Excel files for practice