MS Excel Advance Training: Solve Difficult Problems
4.0 (50 ratings)
2,017 students enrolled
Wishlisted 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)
2,017 students enrolled
Created by Manoj Kumar
Last updated 9/2016
English
Price: \$25
30-Day Money-Back Guarantee
Includes:
• 9.5 hours on-demand video
• 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
Requirements
• Students dont need any prior knowledge for doing this course. They just need desire to advance thier Excel skills
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.

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
09:35:55
+
Getting Stated with Excel:
1 Lecture 05:36
Preview 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
02:04

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

Basic Excel Calculations
03:55

Define Name Ranges to Perform Calculations
01:28

Use of Relative lining vs Absolute referencing.

Relative Referencing
02:23

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

Mixed Referencing
03:05

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
01:10

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
02:28

IF for Multiple Criterias Using and Function
03:55

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

IF for Multiple Criterias Using And & Sum Functions
04:17

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

IF for Multiple Criterias Using And, Or & Sum Functions
03:52

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

Nested if For Multiple Grades and Outcomes
05:08
+
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
03:53

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

Update Value Without Formulas
02:04

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

Transpose The From Col To Row
02:04

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

01:49
+
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
04:09

Format top and bottom nos with Conditional Formatting.

Identify Bottom And Top No's Using Conditional Formatting
02:41

Magical display of values.

Display data with Bar, Color Scales and Icon sets
02:12

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
04:58
+
IS and Lookup Functions:
10 Lectures 50:31
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
+
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
02:59

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

Count, Counta, Countblank
01:43

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

Rank Function
02:18

Find a certain largest or smallest no in range.

Large & Small Functions
02:37

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

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

SumIF and AverageIF
04:56

SumIF continued
03:06

AverageIF continued
04:18

Count no of cells that meet a specified criteria or condition

CountIF Function to count Cells with Conditions
04:15

How to use Sumifs and Averageifs for multiple conditions.

SumIFs and AverageIFs for Multiple Conditions
03:20

Count cells that meet multiple conditions.

CountIFs Function to count Cells with Multiple Conditions
02:12
+
Rounding Function
3 Lectures 10:57
Int, Trunc, Odd, Even
05:22

Round, RoundDown, RoundUp
02:33

Preview 03:02
+
DataBase Functions:
4 Lectures 13:25
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
10 More Sections