MS Excel Advance Training: Solve Difficult Problems

Course Curriculum

Chapter 1: Getting Stated with Excel:

Lecture 1: Introduction to the Course:

Chapter 2: Basic Excel Calculations

Lecture 1: Define Short Cut key to Open Excel

Lecture 2: Basic Excel Calculations

Lecture 3: Define Name Ranges to Perform Calculations

Lecture 4: Relative Referencing

Lecture 5: Mixed Referencing

Lecture 6: Write 3D Formula and Learn to Use Condolidate

Chapter 3: Quick Excel Short Cut Keys

Lecture 1: Short Cut to AutoSum, Copy, Paste

Lecture 2: Repete Privious Task

Lecture 3: Short Cut to Fill Down , Fill Right

Lecture 4: Insert and Delete Row & Columns

Lecture 5: Hide and Unhide the Ribbon with Short Cut Key

Lecture 6: Use Ribben with Quick Short cut, Remove Gridlines

Lecture 7: Customize the Short Cuts from Ribbon with Quick Access Toolbar

Lecture 8: Customize Ribbon: New 2010 Feature

Chapter 4: Conditional Function: IF

Lecture 1: IF for Single Criteria

Lecture 2: IF for Multiple Criterias Using and Function

Lecture 3: IF for Multiple Criterias Using And & Sum Functions

Lecture 4: IF for Multiple Criterias Using And, Or & Sum Functions

Lecture 5: Nested if For Multiple Grades and Outcomes

Chapter 5: Paste Special

Lecture 1: Paste Value, Format, Formulas, Comment only with Paste Special

Lecture 2: Update Value Without Formulas

Lecture 3: Transpose The From Col To Row

Lecture 4: Paste Link

Chapter 6: Conditional Formatting

Lecture 1: Use CF to Colour cell based on content

Lecture 2: Identify Bottom And Top No's Using Conditional Formatting

Lecture 3: Display data with Bar, Color Scales and Icon sets

Lecture 4: Use of Formulas To Format The Cell

Chapter 7: IS and Lookup Functions:

Lecture 1: Introducing IS Functions: IsError, IsNumber, IsBlank Covered

Lecture 2: Identify Common Items with Match, IF, IsError

Lecture 3: Use of Choose Function based on Index No:

Lecture 4: Use of Choose for Scenario Analysis:

Lecture 5: Vlookup to Locate data from right from Table

Lecture 6: Dynamic use of Vlookup with IF, IsError and Match

Lecture 7: Hlookup to locate data from Table

Lecture 8: Combined use of Vlookup & Hlookup with IFerror

Lecture 9: Index Function: Far better then Vlookup and Hlookup

Lecture 10: Indirect Function to create output sheet and summarize data

Chapter 8: Basic Statistical Functions

Lecture 1: Max, Min, Median, Average, Sum

Lecture 2: Count, Counta, Countblank

Lecture 3: Rank Function

Lecture 4: Large & Small Functions

Lecture 5: Inroducing Sumif, Sumifs, Averagif, Averageifs functions

Lecture 6: SumIF and AverageIF

Lecture 7: SumIF continued

Lecture 8: AverageIF continued

Lecture 9: CountIF Function to count Cells with Conditions

Lecture 10: SumIFs and AverageIFs for Multiple Conditions

Lecture 11: CountIFs Function to count Cells with Multiple Conditions

Chapter 9: Rounding Function

Lecture 1: Int, Trunc, Odd, Even

Lecture 2: Round, RoundDown, RoundUp

Lecture 3: Mround, Ceiling, Floor

Chapter 10: DataBase Functions:

Lecture 1: 1. Introducing Database Functions Final

Lecture 2: 2. Using Dsum For And Criteria

Lecture 3: 3. Dsum With Or Criteria

Lecture 4: 4. Dget To Extract Single Record From Table

Chapter 11: Text Functions:

Lecture 1: Right, Left, Mid

Lecture 2: Find, Search, Len

Lecture 3: Trim, Proper, Upper

Lecture 4: 4. Use Of & And Concatenate to Join Different Values

Lecture 5: Replace & Substitute Functions

Lecture 6: Rept & Len Function for Area Code

Lecture 7: Practical Exercise For Text Functions

Lecture 8: Second Practical Exercise For Text Functions

Lecture 9: Extract The Name Of The Worksheet In Excel

Lecture 10: Extract The Name Of The Workbook In Excel

Chapter 12: Financial Functions

Lecture 1: Analyse Investment with FV & PV Functions

Lecture 2: Fvschedule for Variable Interest Rates

Lecture 3: Effect, Nominal Functions

Lecture 4: PMT, IPMT, PPMT Functions for Loan Repayment Schedule

Lecture 5: CUMPRINC & CUMIPMT for Cum Int & Principle Payment

Chapter 13: Date & Time Functions

Lecture 1: Introducing Date & Time Functions

Lecture 2: Change The Default Settings

Lecture 3: Custom Date Formatting

Lecture 4: Today & Now Functions

Lecture 5: Weekday Function

Lecture 6: Workday & Netwrokdays: Related to working days between two dates

Lecture 7: Datedif: To Calcuate Exact age of a Person

Lecture 8: Edate & Eomonth:

Lecture 9: Year, Month, Day and Date Functions

Lecture 10: AutoFill

Chapter 14: What If Analysis, SubTotal

Lecture 1: Goal Seek

Lecture 2: SubTotal to Summarise large Data

Lecture 3: Data Table For Sensitivity Analysis

Lecture 4: Scenario Manager

Chapter 15: Pivot Table