Advanced Excel - Beginner to Ninja level (includes Charts)

Using Excel for MIS Reporting, Data Cleaning, Data Analysis, Charting, Time-saver tricks
4.7 (82 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.
923 students enrolled
$100
Take This Course
  • Lectures 90
  • Contents Video: 13 hours
  • Skill Level All Levels
  • 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 9/2013 English

Course 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).

What are the requirements?

  • MS Excel 2007/2010

What am I going to get from this course?

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

What is the target audience?

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

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: Level 1 (A) - Warm Up, Case Study Files + eBook (zipped folder)
07:26

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

10:06

Hand-Picked Super-Essential Shortcuts

13:55
  • Mathematical formulas - SUM, AVERAGE, MAX, MIN
  • Introduction to GO TO - SPECIAL, CTRL+ENTER, applications of =MAX()
12: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.
01:53

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

11:32

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

07:48

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

06:54

=TRANSPOSE() with Ctrl+Shift+Enter

11:09
  • GO TO Special - Formula with/without errors, Constants, Visible cells, Blank cells etc.
  • Ctrl+Enter
Section 2: Level 1 (B) - Sort, Filter, Dates, Dashboard tricks
10:52
  • Multi-Level Sorting
  • Custom Sorting (e.g. North, East, West, South)
  • Adding blank rows between every row using a trick
17: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)

09:28

Advanced Filter for Differential Criteria

05:03

Advanced Filter for Differential Criteria

03:23

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

05:39

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

04:22

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

04:34

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

02:49

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

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

17: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.

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

19:31
  • Group and Auto Outline
  • Drop-Down List with Naming
07: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 Panes
05:31
Section 3: Level 1 (C) - Lookups
05:49

VLOOKUP() - Where - Why - How

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
05: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, COUNTIFS
15:12
COUNTIFS for duplicate detection; Remove Duplicate
06:42
Section 4: Level 2 (A) - Reverse Lookup & Pivot Table
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
Pivot Table - Practice (Inventory)
Preview
11:56
SubTotal - Automatic row-wise subtotal #1
13:57
SubTotal - Automatic row-wise subtotal #2
07:13
SubTotal - Automatic row-wise subtotal #3
07:01
Section 5: Level 2 (B) - Data Cleansing functions
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
Find & Replace – Advanced
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
Section 6: Level 2 (C) - Logical Formulas and Conditional Formatting
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
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)
06:52
Section 7: Level 2 (D) - What If Analysis
06:59

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

08:11

Back Calculation

12:28

VC:Vertical data - Column Input Cell

HR:Horizontal data - Row Input Cell

What IF Analysis – Using Form Control Buttons
08:19
Section 8: Level 3 - 3D Data Consolidation, Print tricks, Password, Formula Audit etc
05: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 #1
07:51
3-D Data Consolidation from same/different Workbooks #2
08:12
Formula Auditing techniques
Preview
07:31
File Security & Password Protection #1
06:57
07: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.

Printing
17:17
Comments - Picture and Text
08:35
Split Windows, Viewing multiple Windows
05:02
Hyperlinking
04:56
Section 9: Level 4 - Charts
08:13

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

05:44

Axis Value Settings, Data Label Formats

06: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)
06: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)
07:21

Applications: Components of a Category (%):

  • Headcount
  • Source of Funds
  • Sales Origination

Techniques:

  • Format Data Series – Rotation and Pie-explosion
06: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)
05:54

Applications: Relation (80:20 comparison):

  • Sales and Profit
  • Input and Output


Techniques:

  • Switch Row/Column
  • Lines -> Series Lines
  • Linking Excel Chart to PowerPoint
Section 10: Level 5 - Macros Basics
10:39
  • 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 – Recording, Running; Using Buttons to run Macros
10:22
Section 11: Level 6 - INDIRECT, OFFSET, MATCH
08:12

Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Named Cell/Range can be used as an input for INDIRECT

15:08

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. E.g. A4 or A1:A4. The output can be input for formulas such as SUM (cell range), VLOOKUP (table_array) etc.

MATCH() can be used to automatically compute Rows, Cols, Height, Width, based on user input

Section 12: Bonus - Advanced Tricks

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Rishabh Pugalia, Excel & P.Point Ninja | Taught 12,000+ professionals (LIVE)

Rishabh Pugalia is a Chartered Accountant from India. He has worked with KPMG and J.P. Morgan before venturing into Corporate Training. He founded ExcelNext in December 2009 and has since trained 12,000+ professionals across various multi-national companies and professionals' forums.

He has delivered training programs on "Advanced Excel" in top Corporate houses. He has co-authored a Technical Guide issued by the Institute of Chartered Accountants of India (ICAI) - "Using Excel for Continuous Controls Monitoring" .

He is a visiting faculty at various premier B-Schools and has shared his knowledge on numerous Professionals' public forums.

Ready to start learning?
Take This Course