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.6 (35 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.
1,986 students enrolled
$19
$25
24% off
Take This Course
  • Lectures 133
  • Length 9.5 hours
  • Skill Level Expert Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 5/2015 English

Course Description

  • 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.

What are the requirements?

  • Students dont need any prior knowledge for doing this course. They just need desire to advance thier Excel skills

What am I going to get from this course?

  • 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

What 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Getting Stated with Excel:
Introduction to the Course:
Preview
05:36
Section 2: Basic Excel Calculations
02:04

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

03:55

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

Define Name Ranges to Perform Calculations
01:28
02:23

Use of Relative lining vs Absolute referencing.

03:05

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

03:38

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

Section 3: Quick Excel Short Cut Keys
02:49

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

02:09

Repeat previous task with Ctrl Y and F4

01:10

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

01:56

Learn how to insert and Delete multiple rows and columns.

01:29

Hide and collapse Ribbon with Quick Short Cut Keys

02:55

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

04:47

Customize and access the tools on Ribbon more quickly.

03:50

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

Section 4: Conditional Function: IF
02:28

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

IF for Multiple Criterias Using and Function
03:55
04:17

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

03:52

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

05:08

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

Section 5: Paste Special
03:53

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

02:04

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

02:04

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

01:49

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

Section 6: Conditional Formatting
04:09

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

02:41

Format top and bottom nos with Conditional Formatting.

02:12

Magical display of values.

04:58

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

Section 7: IS and Lookup Functions:
Introducing IS Functions: IsError, IsNumber, IsBlank Covered
06:52
Identify Common Items with Match, IF, IsError
05:30
Use of Choose Function based on Index No:
03:22
Use of Choose for Scenario Analysis:
04:15
Vlookup to Locate data from right from Table
04:40
Dynamic use of Vlookup with IF, IsError and Match
05:31
Hlookup to locate data from Table
03:04
Combined use of Vlookup & Hlookup with IFerror
06:51
Index Function: Far better then Vlookup and Hlookup
05:43
Indirect Function to create output sheet and summarize data
04:43
Section 8: Basic Statistical Functions
02:59

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

01:43

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

02:18

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

02:37

Find a certain largest or smallest no in range.

Inroducing Sumif, Sumifs, Averagif, Averageifs functions
02:40
04:56

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

SumIF continued
03:06
AverageIF continued
04:18
04:15

Count no of cells that meet a specified criteria or condition

03:20

How to use Sumifs and Averageifs for multiple conditions.

02:12

Count cells that meet multiple conditions.

Section 9: Rounding Function
Int, Trunc, Odd, Even
05:22
Round, RoundDown, RoundUp
02:33
Mround, Ceiling, Floor
Preview
03:02
Section 10: DataBase Functions:
1. Introducing Database Functions Final
02:16
2. Using Dsum For And Criteria
05:39
3. Dsum With Or Criteria
03:25
4. Dget To Extract Single Record From Table
02:05
Section 11: Text Functions:
Right, Left, Mid
05:32
Find, Search, Len
04:35
Trim, Proper, Upper
03:55
4. Use Of & And Concatenate to Join Different Values
04:15
Replace & Substitute Functions
05:32
Rept & Len Function for Area Code
03:56
Practical Exercise For Text Functions
06:50
Second Practical Exercise For Text Functions
09:08
Extract The Name Of The Worksheet In Excel
05:55
Extract The Name Of The Workbook In Excel
03:03
Section 12: Financial Functions
Analyse Investment with FV & PV Functions
04:16
Fvschedule for Variable Interest Rates
01:50
Effect, Nominal Functions
03:20
PMT, IPMT, PPMT Functions for Loan Repayment Schedule
05:16
CUMPRINC & CUMIPMT for Cum Int & Principle Payment
03:01
Section 13: Date & Time Functions
Introducing Date & Time Functions
05:34
Change The Default Settings
02:21
Custom Date Formatting
02:57
Today & Now Functions
02:58
Weekday Function
03:28
Workday & Netwrokdays: Related to working days between two dates
02:36
Datedif: To Calcuate Exact age of a Person
03:50
Edate & Eomonth:
03:19
Year, Month, Day and Date Functions
01:52
AutoFill
03:27
Section 14: What If Analysis, SubTotal
Goal Seek
02:36
SubTotal to Summarise large Data
04:03
Data Table For Sensitivity Analysis
04:12
Scenario Manager
04:08
Section 15: Pivot Table

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Manoj Kumar, 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.

Ready to start learning?
Take This Course