Advanced Excel Course

For Beginner - Expert Excel Users
Course Instructor: Rishabh Pugalia (ex-KPMG, ex-J.P. Morgan) is a Chartered Accountant. He regularly teaches Advanced Excel Analytics at two of the Big Four firms  – PwC & EY. His aim is to make you an Excel Pro.
• Video Lessons = 177+ (11 Hrs)
• Lifetime Access
• Lifetime Support
• 4.8/5 Rating

Levels Of Excel Online Course

Intermediate Skills

Able to implement techniques in your office
• Build simple models (Combo Formulas, 3-dimensional lookups, Date formulas)
• Error-handling to avoid humiliation (data validation, auditing, track variables)
• Learn Data Cleaning Techniques (Go-to-Special, Text formulas, Flashfill)

Advanced Skills

Ninja-Level Skills
• Slide-ready output (Charts and Graphs, Printing techniques)
• Dynamic Dashboards (Get Pivotdata, Learn Reverse lookups, conditional calculations)
• Managing Data Security (Passwords, sheet protection)

Excel Course Preview Video

3 reasons how this course is different than the others?

#1 Course Instructor with experience of three of the Big Four firms

The course instructor is a Chartered Accountant. He has worked with KPMG & J.P. Morgan. He regularly teaches Advanced Excel Analytics at two of the Big Four firms  – PwC & EY. Your learning will be the sum of all his experience. Some of his students have become Excel instructors themselves.

#2 We have Pro-level tricks which are often ignored by others.

Few examples:
(a) Complex combo functions: HLOOKUP with MATCH; OFFSET with MATCH
(b) Difference between MATCH function with 0 vs. 1 vs. -1
(d) SUMIFS with date ranges and ID-based running total
(d) Using Data Tables for Sensitivity Analysis along with INDIRECT function
(e) New updates: Fuzzy Lookup, FlashFill, Slicers
… and the list goes on (check the Index below)

#3 Our Video avoid “death by boredom”

(a) All videos have zoom in / zoom out, annotations, markers
(b) The narration is not a script reading
(c) Real-life stories connected to the learning
(d) Picture based eBook so that you don’t have to watch the video again

What do TOP Excel Bloggers think about my course…

Learner Testimonials

I would like to tell you that I am very impressed with your courses so far – I particularly like the short videos that allow me to get to a topic quickly and then move on (your competitors mostly offer 20-30 min videos which is a bit much to keep one’s attention span). The other gem in your training are the shortcuts, I found no other course with the same insights.

Peter Klugsberger
ex-McKinsey & Co. served various senior roles (CEO, COO) in various companies

Because I use spreadsheets daily, I’m often using the Shift+down arrow to highlight and then manipulate data. Of course, this takes FOREVER! As I am advancing in my role at work, it is very important for me to learn more advanced Excel skills. I chose this video b/c it was short and condensed. I could literally watch it on my breaks at work and then use it right away. I really like that. Well done!

Barbara Jones
Finance Professional

I’ve been using Pivot Tables for a couple of years, so was expecting most of this to be familiar. But this course proved me wrong. From simple tips and keyboard shortcuts, to whole features I never knew existed, I constantly found myself muttering things like “oh wow, I had no clue about that”. Things that I can actually put into practice in my job. It’s a great feeling. Kudos to the team on a great course

Peter Thomas
Accounting Professional

We have helped these Companies

COURSE CURRICULUM

Ninja Shortcuts (40 minutes)

• Introduction
• How to learn the best shortcuts in this section?
• What is faster than the regular Copy and Paste shortcuts?
• What is the shortcut to automatically fill selected cells?
• Impress your co-workers with these 6 [data selection and navigating] shortcuts
• Impress your co-workers with these 6 [rows and column] shortcuts
• Impress your co-workers with these 9 [formula writing & auditing] shortcuts
• Impress your co-workers with these 7 [formatting] shortcuts
• How to create your own custom shortcut key in 2 clicks? E.g. Paste Special Value
• How to audit a Financial Model or a Budget Sheet?

Business Math Calculations (25 minutes)

• 2 ways MAX() function can help build Financial Models & Budget
• Which two formula you must know for calculating Bidding & Auction pricing?
• Learn the basics of ROUND(), ROUNDUP() and ROUNDDOWN()
• Learn the advanced level of ROUND(), ROUNDUP() and ROUNDDOWN()
• Learn the 4 most important COUNT formula types
• Why is SUMPRODUCT so important for calculating weighted average?
• How do you perform compounding and discounting calculations in Excel?

Formatting Tricks (22 minutes)

• Learn 3 hidden “Custom Format” tricks for formatting the data
• Learn 2 fastest tricks to copy-paste cell formats 3-5x times faster
• How to automate worksheet formatting? – Part 1
• How to automate worksheet formatting? – Part 2
• What is AutoFill and how to activate it?
• 5 tricks of AutoFill options that will save you time in data entry
• Date AutoFill vs. EOMONTH() formula – a Comparison
• What is the difference between Paste Special Transpose & TRANSPOSE() formula?

Formula Pre-Requisite (7 minutes)

• What is “locking” the cell reference – Basics of using \$
• Learn the two advanced cases of using \$ while locking the cell reference

Special Cell selections (17 minutes)

• How to delete 1000s of errors in an Excel sheet fast?
• How to check for cells with formulas amongst 1000s of cells?
• How to fill occasional blank cells with a specific text or number?
• How to fill in blanks in Excel with the value above?
• How to fill in blanks in Excel with the value below?
• How to color the visible cells in a filtered list?

Sort & Filter (45 minutes)

• Learn the basics of Sorting a list
• Why is Custom Sorting important for Expert level Sorting?
• How to add one blank row between every row of a list?
• How to activate the hidden option of horizontal (left to right) Sorting?
• Learn the basics of Filtering a data
• Avoid this #1 common mistake while applying Filter
• How to analyse the data using Shortcuts after applying Filter?
• Why you should never apply SUM or AVERAGE function on a Filtered list?
• What is the difference between two versions of SUBTOTAL function?
• How to apply Filters on two different data sets of the same worksheet?
• How to apply Color Filter and Custom Filter?
• What is the difference between Filter & Advanced Filter?
• How to apply Advanced Filter with multiple criteria?
• 16 ways to set up advanced criteria for Advanced Filter

Working with Dates (33 minutes)

• Do you know how Excel stores a Date value to avoid MDY vs DMY confusion?
• How to change Control Panel settings to change acceptable format for date input?
• 4 “must-know” date formulas for every Excel user – YEAR, MONTH, DAY, DATE
• How to get the day name of a date – Sun, Mon etc.
• 3 date formulas for Project Management – WEEKDAY(), WORKDAY(), NETWORKDAYS()
• What is the difference between WORKDAY.INTL() vs. WORKDAY()
• What is the difference between NETWORKDAYS.INTL() vs. NETWORKDAYS()
• 4 Tricks of Current Date and Time for Excel Modeling
• How to calculate 5th of next month?
• How to calculate expiry date for agreements and warranty period?

Data Input (16 minutes)

• How to create a basic drop down list?
• How to allow the source of drop down list to include future additions?
• How to add an error alert and instruction message to avoid invalid data input?
• What is the biggest bug in Data Validation and how can it be avoided?
• How to restrict user input to a fixed length of characters?
• How to write a custom formula to avoid invalid data entry in a cell?

Organising Data (12 minutes)

• Why is Grouping columns better than Hiding them?
• How to use Column Grouping effectively?
• How to activate a hidden trick of Grouping?
• How to make your Excel sheet look like a white page?
• How to activate two way Freeze panes?

Pivot Table (1 hours 13 minutes)

• What are the Top 3 tricks of Pivot Table for day to day use?
• What are the two basic things to avoid for creating Pivot Tables successfully?
• Step 1 – How to create a Pivot Table?
• Step 2 – Understanding the 4 grids of a Pivot Table report
• Step 3 – How to calculate SUM, AVERAGE and COUNT in a Pivot Table
• Step 4 – How to calculate PERCENTAGE in a Pivot Table
• Step 5 – What are the different PERCENTAGE calculations types in a Pivot Table?
• Step 6 – Why is “Grouping” numbers important for analysis?
• Step 7 – Why is “Grouping” dates important for analysis?
• Step 8 – Why is “Grouping” text important for analysis?
• Step 9 – What are the 2 things to do if your Pivot Table’s source data changes?
• Step 10 – How to activate “Auto-Refresh” Pivot Table setting?
• Step 11 – How to add Sparkline and Pivot charts to visualise your data?
• Step 12 – How to drill into the details of Pivot Table data?
• Step 13 – How to create 100s of Pivot Table reports in just 3 clicks?
• Step 14 – What is Pivot Table Slicer and how is it different from Report Filter?
• Pivot Table Project for Practice – Sales Data Analysis – Part 1 of 2
• Pivot Table Project for Practice – Sales Data Analysis – Part 2 of 2
• Pivot Table Project for Practice – Inventory Analysis – Part 1 of 1

Lookup & Conditional Calculations (2 hours 18 minutes)

• Learn the basics the most important formula of Excel – VLOOKUP
• When do I need to a apply TRUE instead of FALSE in VLOOKUP?
• What are the real-life applications VLOOKUP with TRUE?
• What is the difference between VLOOKUP and HLOOKUP?
• Learn the basics of VLOOKUP’s best friend – MATCH function
• Learn how MATCH can be used for slabs based data?
• What is 2D Lookup and how can it avoid complex Nested IF statements?
• Learnt the trick to remember the 2D Lookup structure using VLOOKUP and MATCH
• #1 Common mistake when writing 2D Lookup using VLOOKUP and MATCH
• #2 Common mistake when writing 2D Lookup using VLOOKUP and MATCH
• 2D Lookup Project for Practice – using VLOOKUP and MATCH
• Learn to use 2D Lookup with HLOOKUP and MATCH
• Learn the basics of INDIRECT formula
• Learn how the write dynamic formulas using INDIRECT function
• What is 3D Lookup?
• Learn to write 3D Lookup using VLOOKUP, MATCH and INDIRECT functions
• What is “reverse” lookup, and why VLOOKUP fails you sometimes?
• How to write a “reverse” lookup using INDEX with MATCH?
• “Reverse” Lookup Project for Practice – using INDEX with MATCH
• How is VLOOKUP different from SUMIFS?
• How can you add multiple criteria while writing SUMIFS?
• How to use SUMIFS with date range?
• How to use a hidden trick of SUMIFS to calculate running total by ID column?
• Three most important tricks of writing COUNTIFS function
• What is OFFSET function and why experts use it with MATCH function?
• on based Selective Cumulative Running Total

• COUNTIFS() – Single/Multiple Criteria: Duplicate Count, Instance No.
• OFFSET() with MATCH()

Data Cleaning (1 hours 14 minutes)

• How to change case of the text to Upper case, Lower case and Proper case?
• How to remove extra spaces in a sentence using TRIM function?
• How to find the length of a text using LEN function?
• How to convert numbers stored as text back to number using VALUE function?
• How can you add a chart inside a cell using REPT function?
• How to add missing zeroes in Cheque numbers using LEN and REPT?
• How to join text from different cells using CONCATENATE and Ampersand?
• How to use wildcard character asterisk with Find and Replace?
• How to use wildcard character question mark with Find and Replace?
• How to delete delete wildcard character asterisk using Find & Replace?
• Why should you use MS Word’s Find and Replace over MS Excel’s?
• How to replace once cell color format with another in the entire worksheet?
• How to apply basic Text to Column to splitthe text of first name and last name?
• How to keep the zeroes when applying Text to Column on data with preceeding zeroes?
• How to clean numbers which have minus sign at the end instead of at the start?
• How to convert numbers with Dr. and Cr. to positive and negative numbers?
• How to rectify incorrectly formatted date such as 24.05.2007 using Text to Column?
• How to rectify incorrectly formatted date such as 20080109 using Text to Column?
• How to use LEFT and RIGHT functions to extract data from a cell?
• How can you increase the power of LEFT and RIGHT formulas using SEARCH function?
• How is MID more powerful than LEFT and RIGHT functions?
• What is the difference between REPLACE and SUBSTITUTE functions?

Logical Formulas (19 minutes)

• What are the Top 7 error and data validity checking formulas?
• How to write IF formulas – Basic to Intermediate level?
• The two must under-used logical formulas – AND & OR
• How to combine IF with AND & OR formulas to make the logic 5x times powerful?

Conditional Formatting (30 minutes)

• Conditional Formatting: Actual vs. Budget Comparison through Arrows
• Conditional Formatting: w. Drop-down list based Input
• Conditional Formatting: Data Bars, Color Scales, Icon Sets
• Conditional Formatting: Blanks, Errors, Values, Duplicates
• Conditional Formatting: Formula-based (Colored rows based on user input)
• Conditional Formatting: Formula-based w. AND() – 2-way input Coloring
• Conditional Formatting: Formula-based w. COUNTIFS() – Highlighting inputs if from restricted list

What-If Analysis (51 minutes)

• How to add Spin buttons to make your financial model interactive?
• How to work around a major bug of Spin Button?
• How to use Scenario Manager to set up three scenarios – Base, Best & Worst?
• How to use Goal Seek to calculate the optimal EMI for repaying a Loan?
• How to do Sensitivity Analysis using Data Tables feature of What-If Analysis?
• How to do Sensitivity Analysis of Loan EMI using Data Tables?
• Part 1: How to do use Data Tables with more than one output variable?
• Part 2: How to do use Data Tables with more than one output variable?

Subtotal (30 minutes)

• How to insert Subtotals in a list of data in a worksheet?
• How to remove Subtotals in a list of data in a worksheet?
• How to create 2-level Subtotals in a list of data in a worksheet?
• How to apply different color to the row totals in a 2-level Subtotals?
• How to merge cell blocks with same names automatically?
• How to Consolidate data tables from multiple worksheets in 1 minute?
• How to add a 3rd dimension while consolidating tables from multiple sources?

Workbook & Worksheet Security (18 minutes)

• Cell level Security
• Cell level Security Challenge
• Sheet level Security
• File-level Security

Printing (13 minutes)

• Printing – Rows to Repeat at Top (Print Titles)
• Printing – Gridlines
• Printing – Custom Header & Footer
• Printing – Page Order – Vertical vs. Horizontal
• Printing – Tricks for Financial Analysts – Check underlying formulas
• Printing – Tricks for Financial Analysts – Cell Comments
• Printing – Print Entire Workbook

Multiple Windows, Comments, Hyperlinking (15 minutes)

• Comments – Shortcuts, Inserting Picture in Comment Box
• Split Windows, Viewing multiple Windows – For working with different workbooks, worksheets & scattered cell ranges simultaneously
• Hyperlinking (Ctrl+K)

Excel Arrays (Added on 5th Jan 2017) (48 minutes)

• Basics of Array Formula (Ctrl Shift Enter) – Example 1
• Basics of Array Formula (Ctrl Shift Enter) – Example 2
• Building MAX IF and MIN IF with one condition – Example 1
• Building MAX IF and MIN IF with one condition – Example 2
• Array Formula using MAX() MIN() and IF() – 2 criteria
• Find the four earliest dates using SMALL() and IF()
• Calculating SUM of Top 3 numbers
• SUMPRODUCT – Add numbers from next column
• Finding Overlapping Dates using SUMPRODUCT – Part 1 of 2
• Finding Overlapping Dates using SUMPRODUCT – Part 2 of 2
• Extract unique values from a range in excel

Lifetime Access

177+ Course Lessons
\$99
• 177+ Course Lessons
• Lifetime Support
• LinkedIn-verifiable Certificate
• Exclusive Webinars
• 1200+ Office Productivity Tricks

All Courses

Unlimited Learning [All YL Courses]
\$1500
• 177+ Course Lessons
• Lifetime Support
• LinkedIn-verifiable Certificate
• Exclusive Webinars
• 1200+ Office Productivity Tricks

Looking for subscription plan? Get All Courses in \$39/mo: Enroll Now

Unlock your completion certificate

• Complete Advanced Excel premium course at Yoda Learning Solutions
• You are awarded a completion diploma
• The diploma can be verified via URL and exported to LinkedIn to boost your career and impress potential employers.

For Enquiry About Courses? Contact Us:
+1- 203-349-9909

Feel free to call us for any query related to courses. Our support team will resolve your issue.

Advanced Excel Course is for you if...

• You use ERP downloaded Excel files as a base to generate custom MIS reports
• You want to reduce your TAT significantly (turnaround time)
• You want to prepare Financial Models with combo-logical statements
• You prepare Management Reporting dashboards
• You are involved in Performing CAAT & Forensic Audit techniques
• You are looking for basic excel course which make you advanced in excel course

Advanced Excel Course is not for you if...

• You use Excel rarely like if you’re on sales field all the time or work as a salesman or cashier facing the consumer [I love you guys, but this model will not help you]
• You have a personal assistant who is an Excel expert and can do all the number crunching for you [May be, this course will be helpful to your assistant]
• Anyone who wants to instant results just because they have joined without taking any real action or practicing the working files

Frequently Ask Questions

What do you get in Excel course?

• An accelerated, Practical approached Advanced Excel Tutorials to teach you Skill needed to compete in competitive work environment
• All Advanced Excel tutorials case studies related to videos
• A picture based PDF handbook on Excel Tips, Functions & Techniques
• Access to all excel video tutorials 24 x 7 – learn excel online from anywhere
• A 30-day money-back guarantee (we want you to be 100%)

How do I enroll for the Excel training online?

•  Visa/Master Card credit card
• Through PayPal

What will I be able to do after completing this Excel tutorials?

• Create reports with sheets across 10+ excel files
• Build interactive Pivot table with key business metrics like sales, monthly variations, top 10 customers
• Transform messy data within minutes
• Wow your boss and get noticed by the top management

What if I’m unhappy with my online excel course? Do I get a refund?

Customer happiness is our ultimate goal. These Excel videos are created after spending a lot of time in research and recording. We would never want you to be unsatisfied with your purchase and if you’re, then contact us in the first 30 days and we will give you a full refund. To know more, please read our Refund Policy.

How much time required to complete Excel Training?

This is a fast-paced, lean excel training program of about 11:30 hours with videos from basics to Advanced Excel Tutorials. Watch the preview of Excel videos to get a flavor of the program’s uniqueness and relevance. Online Excel course may not be your first preference but I promise that – after watching 3 videos, this course will give a tough fight to your usual classroom Excel Training.

How can you say that my speed will improve?

Well, I want to share something personal. My feedback from each and every client from Corporate gave me a rating between 4.8 to 5 out of 5. So, do not worry. I will empower you with all the excel tips and tricks based on the real issues faced by 1000s of students I teach. I created this advanced excel tutorials after so many years of research and hundreds of hours of excel training. You can also try the Outlook Tutorial to improve your speed further.

What do you get in the course?

In our program, we will train you with our advanced Excel skills which will help you to be the supreme ninja of excel in your field. Data Lookup & Calculations such as Vlookup and Hlookup, Pivot Table for multi-variable analysis, Logical Statements, MIS Reporting & Dashboard Techniques and the Application of Macros.