Advanced Excel - Beginner to Ninja level (includes Charts)
4.6 (538 ratings)
2,380 students enrolled

# Advanced Excel - Beginner to Ninja level (includes Charts)

Learn Excel Online for MIS Reporting, Data Cleaning, Data Analysis, Charting, Time-saver tricks with Examples
4.6 (538 ratings)
2,380 students enrolled
Last updated 8/2020
English
English [Auto]
Current price: \$139.99 Original price: \$199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
• 13 hours on-demand video
• Access on mobile and TV
• Certificate of Completion
Training 5 or more people?

What you'll learn
• Formulas and Functions, MIS Reporting techniques, Data Cleaning, Dashboards, Data Analysis, Charts, Tips-n-Tricks
• Donwloadable PDF HandBook and Excel Case Studies
• HD Quality available upon single-click [bottom-right of video]
Requirements
• MS Excel 2007/2010
Description

"Advanced Excel Ninja" is a comprehensive online training program on Microsoft Excel. It is divided in 12 sections and covers exhaustive list of topics. There are 90+ tutorial videos and the duration is 13 hrs. Topics often ignored in other advanced online programs have been included. Demo Preview Lecture 89 and 90 to find the difference.

• VLOOKUP() with MATCH()
• HLOOKUP() with MATCH()
• INDEX() with multiple arrays & two MATCH()
• Using TRANSPOSE() formula
• Form Control Buttons for Dashboard
• SUMIF() vs. SUMIFS()
• SUM() vs. SUBTOTAL() while using Filter
• Hidden Text-to-Columns tricks
• Using IFERROR() vs. IF() + ISERROR()
• Applications of INDIRECT() and OFFSET()
• Pivot Table tricks on calculating automatic sum, count, percentage and generating grouped data headings
• 3-Data Consolidation
• Power of "GO TO - Special" along with Ctrl+Enter
• Using "Find and Replace" with wildcard characters
• Combo techniques for Data cleaning
• Formula-based Conditional formatting
• Charts - Thermometer, 2 axis, Trendline, Axis value settings
• Inserting Picture in Comments... and the list of such tricks is very long

So if you intend to take your Excel application skills multiple notches higher, gain from the experience of a training instructor who -

• Is a qualified Chartered Accountant
• Has worked with KPMG and J.P. Morgan
• Has trained 12,500+ professionals (Apr 2010 - June 2016) in face-to-face training programs
• Has incorporated in this online program 100s of tip-n-tricks, which he has acquired over the years through various forums, experimentation, problem solving challenges and training interaction

And by the way he has conducted more than 200 (two hundred) workshops for one of the Big Four firms, where the participants are from numerous departments and domains. The insights acquired have been meticulously incorporated in to the program.

Instructor: CA. Rishabh Pugalia, Founder, Excel Next

Who this course is for:
• Working Professionals, Students of Professional Courses
• Aspiring Corporate Trainers (Advanced Excel)
Course content
Expand all 90 lectures 13:01:40
+ Level 1 (A) - Warm Up, Case Study Files + eBook (zipped folder)
9 lectures 01:23:09

Participants can download the accompanying Zipped file that contains PDF HandBook + Excel Case Studies

Preview 07:26

Hand-Picked Super-Essential Shortcuts

Preview 10:06
• Mathematical formulas - SUM, AVERAGE, MAX, MIN
• Introduction to GO TO - SPECIAL, CTRL+ENTER, applications of =MAX()
Formula Tricks - SUM, AUTOSUM, MAX, MIN, AVERAGE
13:55
• SUMPRODUCT() for computing weighted average
• POWER() and ^ for exponential/compounding/discounting computations
• =ROUND(A1/50,0)*50 will yield nearest 50 (multiple of 50) and similar technique can be used to solve for nearest 5, 10, 25 and any such combinations. It is more useful since =MROUND() does not work for +ve and -ve numbers simultaneously.
Formula Tricks - SUMPRODUCT, POWER, ROUND, MROUND
12:26

=COUNTA() implies Count ALL (nos, text, characters i.e. any non-blank cell)

Formula Tricks - COUNT vs. COUNTA
01:53

Double-Click Format Painter for continuous usage and ESCAPE to deactivate, thereafter

Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc.
11:32

Auto-Fill for dates, days, months, years, numeric series, alpha-numeric series etc.

Auto fill options - Variations & Settings
07:48

=TRANSPOSE() with Ctrl+Shift+Enter

Paste Special - Value, Transpose; Formula TRANSPOSE()
06:54
• GO TO Special - Formula with/without errors, Constants, Visible cells, Blank cells etc.
• Ctrl+Enter
Preview 11:09
+ Level 1 (B) - Sort, Filter, Dates, Dashboard tricks
15 lectures 02:13:42
• Multi-Level Sorting
• Custom Sorting (e.g. North, East, West, South)
• Adding blank rows between every row using a trick
Multi-level Sort, Custom Sort, Color Sort
10:52

Always use =SUBTOTAL() formula while working with Filter. The raw formulas of SUM(), MAX(), AVERAGE() will pick up all values (whether hidden/visible as per the filtered list)

Preview 17:04

Advanced Filter I (Differential Filter Criteria)
09:28

Advanced Filter II (Differential Filter Criteria)
05:03

Keep pressing to toggle between different combinations of \$ referencing for cell(s) locking

Cell Referencing using \$ - Introduction
03:23

Keep pressing to toggle between different combinations of \$ referencing for cell(s) locking

Cell Referencing using \$ - Practice #1
05:39

Keep pressing to toggle between different combinations of \$ referencing for cell(s) locking

Cell Referencing using \$ - Practice #2
04:22

Keep pressing to toggle between different combinations of \$ referencing for cell(s) locking

Cell Referencing using \$ - Practice #3
04:34

=SUM(\$A\$1:A1) for cumulative total

Cell Referencing using \$ - Special Trick
02:49

Date Concept - Every valid date is a NUMBER from Excel's point of view. "FORMAT CELLS" is just the "skin" - how to display the output.

Date Concepts and Format
10:13

Date formulas -

DAY, MONTH, YEAR, DATE

TEXT, WORKDAY, NETWORKDAYS

EDATE, EOMONTH

NB: In Excel 2010/2013, =WORKDAY.INTL() has been introduced that helps find net working days between two dates and more importantly, lets you choose which day(s) of the week should be considered as weekends. E.g. lot of countries keep Friday as weekend.

Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS
17:43

Ever worked with dates such as 29.12.2009 or 20130530 and found yourself ill-equipped to prepare MIS reports based on such data? Text-to-Columns help clean dates.

Date Cleaning techniques using Text-to-Columns
10:13
• Group and Auto Outline
• Drop-Down List with Naming
Dashboard I: Grouping, Data Validation (List), Cell-Range Naming
19:31

Activating "DEVELOPER" Tab in 2007. Refer "PDF HandBook" or the "Supplementary Material" to see how to do so in 2010.

Preview 07:17
Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes
05:31
+ Level 1 (C) - Lookups
12 lectures 01:36:42

VLOOKUP() - Where - Why - How

Preview 05:49
VLOOKUP - Pre-requisites and Common Pitfalls
06:45
VLOOKUP - True (1) vs. False (0)
10:37
VLOOKUP Practice with IFERROR
10:41
HLOOKUP
03:29
Using MATCH with VLOOKUP/HLOOKUP - 2x2 matrix lookup
12:43
VLOOKUP with MATCH - Practice #1
05:31
VLOOKUP with MATCH - Practice #2
05:58
HLOOKUP with MATCH - Practice #3
07:42

This video will show you how to use =SUMIFS() to add up the numbers based on specified criteria.

=VLOOKUP() can pull the answer only for the first occurrence/instance of the specified criteria, whereas, =SUMIFS() can add up the corresponding nos. of all occurrences of the specified criteria.

Preview 05:33
SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS
15:12
COUNTIFS for duplicate detection; Remove Duplicate
06:42
+ Level 2 (A) - Reverse Lookup & Pivot Table
11 lectures 01:48:10
Data Validation (Numbers, Dates, Text length)
10:23
INDEX with MATCH – Reverse 2-way Lookup
14:56
INDEX with MATCH - Practice #1
03:34
INDEX with MATCH - Version 2
05:26
Pivot Table #1 (Payroll)
13:25
Pivot Table #2 (Payroll)
06:44
Pivot Table #3 (Payroll)
13:35
SubTotal - Automatic row-wise subtotal #1
13:57
SubTotal - Automatic row-wise subtotal #2
07:13
SubTotal - Automatic row-wise subtotal #3
07:01
+ Level 2 (B) - Data Cleansing functions
9 lectures 01:15:05
Using CONCATENATE, & - to join data strings
11:29
Text-to-Columns - Delimited
06:01
Text-to-Columns - Fixed Width
06:32
Text-to-Columns - Tricks
04:08
10:14
Text Formulas I – UPPER, PROPER, LOWER, TRIM, T, N, REPT
13:01
Text Formulas II – LEFT-RIGHT-MID, LEN, SEARCH
10:40
Text Formulas III – SEARCH vs. FIND
05:47
Text Formulas IV – REPLACE, SUBSTITUTE
07:13
+ Level 2 (C) - Logical Formulas and Conditional Formatting
7 lectures 01:03:39
Logical formulas I - IF, Nested IFs
06:48
Logical formulas II - AND, OR with IF
09:01
Logical formulas III - more case studies
09:30
ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR
09:01
Conditional Formatting I (Blanks, Errors, Values, Duplicates)
10:50

Write a formula such that answer should either be TRUE or FALSE. E.g. = \$B10>100000.

-Cell Reference B10 vs \$B10

-Starting point of range selection in line with formula cell selection

Conditional Formatting II (Formula-based)
11:37
Conditional Formatting III (Data Bars, Color Scales, Icon Sets)
06:52
+ Level 2 (D) - What If Analysis
4 lectures 35:57

Generated report is neither aesthetic in looks nor is dynamic in nature [static]

What IF Analysis – Scenario Manager
06:59

Back Calculation

What IF Analysis – Goal Seek
08:11

VC:Vertical data - Column Input Cell

HR:Horizontal data - Row Input Cell

What IF Analysis – Data Tables
12:28
What IF Analysis – Using Form Control Buttons
08:19
+ Level 3 - 3D Data Consolidation, Print tricks, Password, Formula Audit etc
10 lectures 01:19:18

Introduced from v. 2007

Key Features:

• Color Formatting
• Sort
• Filter
• Auto-copy of Formulas
• Table Header visible as table scrolled down
• Remove duplicates
• Pivot Table tab
Tables - Concept and Applications
05:08
3-D Data Consolidation from same/different Workbooks #1
07:51
3-D Data Consolidation from same/different Workbooks #2
08:12
File Security & Password Protection #1
06:57

Note: By default, ALL cells are "Locked" (identified for protection). Ensure that ALL cells in the sheet are "Unlocked" and only chosen ones are "Locked". Else ALL cells will be locked and no changes can be made.

File Security & Password Protection #2
07:49
Printing
17:17
08:35
Split Windows, Viewing multiple Windows
05:02
04:56
+ Level 4 - Charts
7 lectures 46:57

Chart Elements - Chart Area, Plot Area, Axis Values, Legends, Changing Chart Type, Chart Shortcuts etc.

Preview 08:13

Axis Value Settings, Data Label Formats

Basic Charts – Bar, Column, Pie
05:44

Application: Comparing 2 parameters of similar scale:

• 2011 vs. 2012 sales
• Budget vs. Actual
• Me vs. My Competitor

Techniques:

• Series Overlap
• Fill -> No Fill
• Border Color (Solid Line) & Border Styles (Width)
Special Charts: Thermometer Charts
06:07

Applications: Comparing 2 parameters of different scale:

• GDP (\$) vs. Growth or Inflation (%)
• Sales Amount (\$) vs. Profit Margins (%)

Techniques:

• Plot Series on
• Change Series Chart Type (Line with Markers)
Special Charts: Multi-axis charts
06:57

Applications: Components of a Category (%):

• Source of Funds
• Sales Origination

Techniques:

• Format Data Series – Rotation and Pie-explosion
Special Charts: Exploded Pie charts
07:21

Applications: Trend:

• Profit
• Sales
• Clients’ / Subscribers’ acquisition (e.g. Telecom)

Techniques:

• Chart Tools -> Layout -> Trend line -> Two-period Moving Average
• Format Axis -> Axis Labels -> High
• Format Labels -> Numbers (e.g. no. of decimals – 0, 1, 2)
Chart tips-n-tricks #1
06:41

Applications: Relation (80:20 comparison):

• Sales and Profit
• Input and Output

Techniques:

• Switch Row/Column
• Lines -> Series Lines
• Linking Excel Chart to PowerPoint
Chart tips-n-tricks #2
05:54
+ Level 5 - Macros Basics
2 lectures 21:01
• Activating Developer Tab from Excel Options
• Files that can store macros- .XLS (97-2003 format) or .XLSM (Macro-enabled workbook)
• A Macros once run, cannot be undone by Ctrl+Z

Macros – Overview, Developer tab, Settings
10:39
Macros – Recording, Running; Using Buttons to run Macros
10:22