MS Excel Advance Training: Solve Difficult Problems
4.0 (50 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,017 students enrolled
Wishlisted Wishlist

Please confirm that you want to add MS Excel Advance Training: Solve Difficult Problems to your Wishlist.

Add to Wishlist

MS Excel Advance Training: Solve Difficult Problems

Advance Excel 2007 and 2010 features: Do Advance Excel Training and use Excel like a Pro: Charts, Pivot Table & Array,
4.0 (50 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,017 students enrolled
Created by Manoj Kumar
Last updated 9/2016
Price: $25
30-Day Money-Back Guarantee
  • 9.5 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • If you want to learn Excel 2007 from basic and reach to very advanced level of Excel usage, then you are at the right place.
  • In this course, we are starting from very basic excel features and taking to very Adv. tools like Vlookup, Hlookup, text, Financial Functions, Array Function, Pivot Table and scenario & Sensitivity analysis.
  • Over 6 Hours of Video content with more then 100 videos
  • Learn Excel Short Cut keys, over 100 Excel Functions
  • Become more creative, efficient and accurate & Fast in Excel
View Curriculum
  • Students dont need any prior knowledge for doing this course. They just need desire to advance thier Excel skills
  • Updated on 18 August, 2015
  • "Advanced Excel 2007 for Professionals" is a comprehensive online training program. It is covered in 6 hours of videos and covers exhaustive list of topics. There are so many features available in excel that can save a lot of time & can make your work presentable.
  • We are specialists in offering expert level training courses and other great features of Excel. As part of our Advanced Excel training you will gain the necessary knowledge required for the efficient use of the tools and other exciting features which this outstanding software has to offer.
Categories of all Functions Covered:
  1. Basic Excel Calculation & Function
  2. Excel Short Cuts
  3. Conditional Functions
  4. Text and Lookup Function: Right, Left, Mid, Find, Search, Len, Rept etc
  5. Finance, Database Functions NPV. IRR, FvSchedule, PMT, IPMT, PPMT
  6. Maths & Stats Sumif, Averageif, Countif, Ceiling, Floor etc
  7. Date & Time Workday, Networkdays, Datedif, Edate etc
  8. What IF analysis Data Table, Scenario manager, Goal Seek
  9. Array Functions SumProduct, Transpose
  10. Additional Features in 2010 Customize Ribbon, Slicer

Tools and Commands:

  1. Conditional Formatting
  2. Paste Special
  3. Go To Special, Sorting, Filter, Adv Filter
  4. Error Tracking, Evaluate Formula, Name Manager
  5. Import Date from web
  6. Text to Column, Data Validation, Consolidate
  7. What IF Analysis, Scenario Manager, Goal Seek
  8. Grouping, SubTotal, Solver
  9. Workbook & workSheet Protections
  10. Pivot Table
  11. Spinner, Scroll Bar, List Box, Combo Box
  12. Analysis Tool Pack

Much more to explore in the videos.

Who is the target audience?
  • Formulas & Function course is for everyone. If you are basic user or expert still you will find many important tools to advance your knowledge and skills for more efficiency and productivity. We are taking some basic concepts of Excel and taking it to Advanced Excel training with Practical Examples. By the end of this training, you will have mastered the advanced features and functions of this supreme software.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
133 Lectures
Getting Stated with Excel:
1 Lecture 05:36
Basic Excel Calculations
6 Lectures 16:33

Learn how you can define a short cut key to Open Excel

Define Short Cut key to Open Excel

Learn why using sum function is better instead of adding every cell one by one.

Basic Excel Calculations

Define Name Ranges to Perform Calculations

Use of Relative lining vs Absolute referencing.

Relative Referencing

When you link in cell with single $ , it is called Mixed Linking. Write a universal formula.

Mixed Referencing

How to refer to the same cell address in multiple sheets with 3D formulas in Excel.

Preview 03:38
Quick Excel Short Cut Keys
8 Lectures 21:05

Quick short cut key Alt + = , Ctrl C & Ctrl V to copy and paste fast.

Preview 02:49

Repeat previous task with Ctrl Y and F4

Preview 02:09

Fill down with Ctrl + D and Fill right with Ctrl + R

Short Cut to Fill Down , Fill Right

Learn how to insert and Delete multiple rows and columns.

Preview 01:56

Hide and collapse Ribbon with Quick Short Cut Keys

Preview 01:29

Learn to access Ribbon without Mouse. and remove gridlines with Key tips.

Preview 02:55

Customize and access the tools on Ribbon more quickly.

Preview 04:47

We can add , remove, change any tab in ribbon with this new feature in 2010

Preview 03:50
Conditional Function: IF
5 Lectures 19:40

If Function is used when the output is based on any criteria or condition.

IF for Single Criteria

IF for Multiple Criterias Using and Function

Lets see how to join multiple functions (IF, And, Sum) within one Formula.

IF for Multiple Criterias Using And & Sum Functions

Lets see how to join multiple functions (IF, And, Or, Sum) within one Formula.

IF for Multiple Criterias Using And, Or & Sum Functions

Learn how to use IF within IF function to provide different grades.

Nested if For Multiple Grades and Outcomes
Paste Special
4 Lectures 09:50

Paste special: The most used tool in Excel to exploit copied data. Learn how to paste values, format, comment or formulas only.

Paste Value, Format, Formulas, Comment only with Paste Special

How to double or half the values in single step using Paste Special.

Update Value Without Formulas

Transpose the data from Row to Column and from column to Row quickly.

Transpose The From Col To Row

Here is a great tool of paste special that is Paste Link.

Paste Link
Conditional Formatting
4 Lectures 14:00

Conditional Formatting based on the data in the cell. Color the cell if it is greater or less than 50.

Use CF to Colour cell based on content

Format top and bottom nos with Conditional Formatting.

Identify Bottom And Top No's Using Conditional Formatting

Magical display of values.

Display data with Bar, Color Scales and Icon sets

Instead of using the defined rules of Conditional Formatting, you can make your own rule to format the cell.

Use of Formulas To Format The Cell
IS and Lookup Functions:
10 Lectures 50:31
Introducing IS Functions: IsError, IsNumber, IsBlank Covered

Identify Common Items with Match, IF, IsError

Use of Choose Function based on Index No:

Use of Choose for Scenario Analysis:

Vlookup to Locate data from right from Table

Dynamic use of Vlookup with IF, IsError and Match

Hlookup to locate data from Table

Combined use of Vlookup & Hlookup with IFerror

Index Function: Far better then Vlookup and Hlookup

Indirect Function to create output sheet and summarize data
Basic Statistical Functions
11 Lectures 34:24

Simple excel functions: Max, Min, Median, Average, Sum. You can skip them if already aware of them.

Max, Min, Median, Average, Sum

Learn how to count cells with numerical values, blank cells and cells with text.

Count, Counta, Countblank

Learn how to assign rank based on descending or ascending nos.

Rank Function

Find a certain largest or smallest no in range.

Large & Small Functions

Inroducing Sumif, Sumifs, Averagif, Averageifs functions

How to sum or average the nos based on a certain criteria or condition.

SumIF and AverageIF

SumIF continued

AverageIF continued

Count no of cells that meet a specified criteria or condition

CountIF Function to count Cells with Conditions

How to use Sumifs and Averageifs for multiple conditions.

SumIFs and AverageIFs for Multiple Conditions

Count cells that meet multiple conditions.

CountIFs Function to count Cells with Multiple Conditions
Rounding Function
3 Lectures 10:57
Int, Trunc, Odd, Even

Round, RoundDown, RoundUp

DataBase Functions:
4 Lectures 13:25
1. Introducing Database Functions Final

2. Using Dsum For And Criteria

3. Dsum With Or Criteria

4. Dget To Extract Single Record From Table
10 More Sections
About the Instructor
Manoj Kumar
4.0 Average rating
68 Reviews
2,193 Students
4 Courses
Expert Level Training for Finance Professionals

I am Manoj Kumar. I am an MBA (Finance) from ICFAI and CFA level 3 candidate from CFA Institute, USA. I am running Investment Banking Institute, New Delhi. I teach Adv Excel, VBA, Dashboards, Financial Modeling, Valuations & M&A. I have been into consulting in Project Finance before moving to training.

Areas of Training: Excel, VBA,Dashboard Designing,Financial Modeling, Valuation & M&A —Have trained in WNS, Global Energy, Intec Capital, Creative Web Mobi, ICOFP, GNIM etc —Conducted more than 150 open workshops and trained more than 2500 participants —Developed Video based Training courses on Various SubectsMy passion for training led me to start the IB Institute in New Delhi, India. I have two years of rich experience in the fields of Investment Banking, Corporate Valuation, Equity Research and Consulting for Project appraisal.

I have conducted more then 150 workshops for various colleges and corporate houses. I am Certified Excel Specialist and Trainer from Microsoft.