Advanced Excel - Beginner to Ninja level (includes Charts)

Learn Excel Online for MIS Reporting, Data Cleaning, Data Analysis, Charting, Time-saver tricks with Examples

Created byRishabh Pugalia, Yoda Learning

Last updated 8/2020

English

What you'll learn

Formulas and Functions, MIS Reporting techniques, Data Cleaning, Dashboards, Data Analysis, Charts, Tips-n-Tricks
Lifetime access to the Videos
Donwloadable PDF HandBook and Excel Case Studies
HD Quality available upon single-click [bottom-right of video]

Course content

12 sections • 90 lectures • 13h 1m total length

Introduction + Downloadable files7:26
Participants can download the accompanying Zipped file that contains PDF HandBook + Excel Case Studies
Warm-up - Key Shortcuts10:06
Hand-Picked Super-Essential Shortcuts
Formula Tricks - SUM, AUTOSUM, MAX, MIN, AVERAGE13:55
Mathematical formulas - SUM, AVERAGE, MAX, MIN

Introduction to GO TO - SPECIAL, CTRL+ENTER, applications of =MAX()
Formula Tricks - SUMPRODUCT, POWER, ROUND, MROUND12:26
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 - COUNT vs. COUNTA1:53
=COUNTA() implies Count ALL (nos, text, characters i.e. any non-blank cell)
Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc.11:32
Double-Click Format Painter for continuous usage and ESCAPE to deactivate, thereafter
Auto fill options - Variations & Settings7:48
Auto-Fill for dates, days, months, years, numeric series, alpha-numeric series etc.
Paste Special - Value, Transpose; Formula TRANSPOSE()6:54
=TRANSPOSE() with Ctrl+Shift+Enter
Range Selection; GO TO - Special11:09
GO TO Special - Formula with/without errors, Constants, Visible cells, Blank cells etc.

Ctrl+Enter

Multi-level Sort, Custom Sort, Color Sort10:52
Multi-Level Sorting

Custom Sorting (e.g. North, East, West, South)

Adding blank rows between every row using a trick
Filter & SUBTOTAL() formula17:04
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)
Advanced Filter I (Differential Filter Criteria)9:28
Advanced Filter for Differential Criteria
Advanced Filter II (Differential Filter Criteria)5:03
Advanced Filter for Differential Criteria
Cell Referencing using $ - Introduction3:23
Keep pressing to toggle between different combinations of $ referencing for cell(s) locking
Cell Referencing using $ - Practice #15:39
Keep pressing to toggle between different combinations of $ referencing for cell(s) locking
Cell Referencing using $ - Practice #24:22
Keep pressing to toggle between different combinations of $ referencing for cell(s) locking
Cell Referencing using $ - Practice #34:34
Keep pressing to toggle between different combinations of $ referencing for cell(s) locking
Cell Referencing using $ - Special Trick2:49
=SUM($A$1:A1) for cumulative total
Date Concepts and Format10:13
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 Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS17:43
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 Cleaning techniques using Text-to-Columns10:13
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.
Dashboard I: Grouping, Data Validation (List), Cell-Range Naming19:31
Group and Auto Outline

Drop-Down List with Naming
Dashboard II: Form Controls Buttons7:17
Activating "DEVELOPER" Tab in 2007. Refer "PDF HandBook" or the "Supplementary Material" to see how to do so in 2010.
Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes5:31

VLOOKUP Introduction - Where/Why/How to use5:49
VLOOKUP() - Where - Why - How
VLOOKUP - Pre-requisites and Common Pitfalls6:45
VLOOKUP - True (1) vs. False (0)10:37
VLOOKUP Practice with IFERROR10:41
HLOOKUP3:29
Using MATCH with VLOOKUP/HLOOKUP - 2x2 matrix lookup12:43
VLOOKUP with MATCH - Practice #15:31
VLOOKUP with MATCH - Practice #25:58
HLOOKUP with MATCH - Practice #37:42
SUMIFS - Introduction5:33
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.
SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS15:12
COUNTIFS for duplicate detection; Remove Duplicate6:42

Data Validation (Numbers, Dates, Text length)10:23
INDEX with MATCH – Reverse 2-way Lookup14:56
INDEX with MATCH - Practice #13:34
INDEX with MATCH - Version 25:26
Pivot Table #1 (Payroll)13:25
Pivot Table #2 (Payroll)6:44
Pivot Table #3 (Payroll)13:35
Pivot Table - Practice (Inventory)11:56
SubTotal - Automatic row-wise subtotal #113:57
SubTotal - Automatic row-wise subtotal #27:13
SubTotal - Automatic row-wise subtotal #37:01

Logical formulas I - IF, Nested IFs6:48
Logical formulas II - AND, OR with IF9:01
Logical formulas III - more case studies9:30
ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR9:01
Conditional Formatting I (Blanks, Errors, Values, Duplicates)10:50
Conditional Formatting II (Formula-based)11:37
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 III (Data Bars, Color Scales, Icon Sets)6:52

Tables - Concept and Applications5:08
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
3-D Data Consolidation from same/different Workbooks #17:51
3-D Data Consolidation from same/different Workbooks #28:12
Formula Auditing techniques7:31
File Security & Password Protection #16:57
File Security & Password Protection #27:49
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.
Printing17:17
Comments - Picture and Text8:35
Split Windows, Viewing multiple Windows5:02
Hyperlinking4:56

Charts – Basic Concepts and Elements8:13
Chart Elements - Chart Area, Plot Area, Axis Values, Legends, Changing Chart Type, Chart Shortcuts etc.
Basic Charts – Bar, Column, Pie5:44
Axis Value Settings, Data Label Formats
Special Charts: Thermometer Charts6:07
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: Multi-axis charts6:57
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: Exploded Pie charts7:21
Applications: Components of a Category (%):

Headcount

Source of Funds

Sales Origination

Techniques:

Format Data Series – Rotation and Pie-explosion
Chart tips-n-tricks #16:41
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 #25:54
Applications: Relation (80:20 comparison):

Sales and Profit

Input and Output

Techniques:

Switch Row/Column

Lines -> Series Lines

Linking Excel Chart to PowerPoint

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

The participants will be provided lifetime access to videos plus downloadable Excel Case Studies and picture-based HandBook (PDF).

Who this course is for:

Working Professionals, Students of Professional Courses
Aspiring Corporate Trainers (Advanced Excel)

Advanced Excel - Beginner to Ninja level (includes Charts)

What you'll learn

Explore related topics

Course content

Level 1 (A) - Warm Up, Case Study Files + eBook (zipped folder)9 lectures • 1hr 23min

Level 1 (B) - Sort, Filter, Dates, Dashboard tricks15 lectures • 2hr 14min

Level 1 (C) - Lookups12 lectures • 1hr 37min

Level 2 (A) - Reverse Lookup & Pivot Table11 lectures • 1hr 48min

Level 2 (B) - Data Cleansing functions9 lectures • 1hr 15min

Level 2 (C) - Logical Formulas and Conditional Formatting7 lectures • 1hr 4min

Level 2 (D) - What If Analysis4 lectures • 36min

Level 3 - 3D Data Consolidation, Print tricks, Password, Formula Audit etc10 lectures • 1hr 19min

Level 4 - Charts7 lectures • 47min

Level 5 - Macros Basics2 lectures • 21min

Requirements

Description

Who this course is for: