ADVANCED EXCEL NINJA

Learn Everything in Excel from building Excel Models to creating Dynamic Dashboards

This Course includes:

Nip_Fab_testimonial_page_1.png
“I am very impressed with Yoda Learning courses – I particularly like the short videos that allow me to get to a topic quickly and the other gem is the shortcuts.”

What’s covered in the Advanced Excel Tutorials Course

All the sections have been divided into 3 levels: Beginner | Intermediate | Advanced

✓ Data basics (Best usage of Shortcuts, Formatting tricks, cell referencing)
✓ Simple problem solving (Sort and Filter basics)
✓ Learn Essential Business Math (Formula Warmup, Pivot table overview

✓ 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, Flash fill)
✓ Summarize your data with Pivot Tables (Slicers, Sparklines, Calcs, Differential Criteria Filters).

✓ Modeling Uncertainty (Scenario, Sensitivity Analysis, Data Tables, Goal Seek)
✓ Slide-ready output (Charts and Graphs, Printing techniques)
✓ Dynamic Dashboards (Get Pivotdata, Learn Reverse lookups, conditional calculations)
✓ Managing Data Security (Passwords, sheet protection)

COURSE INSTRUCTOR

Rishabh Pugalia

Course Narrator

Worked with KPMG, and became a Chartered Accountant
Traded Interest Rate Futures, and then worked with J.P. Morgan (Fixed Income)

We’ve helped over 7,800 learners overcome their learning “plateau”...

Don’t take my word for it – check out this feedback from some of the professionals representing virtually every business domain, background and experience level…
“I am very impressed with Yoda Learning courses – I particularly like the short videos that allow me to get to a topic quickly and the other gem is the shortcuts.”
Peter Klugsberger - Yodalearning Student
Peter Klugsberger
Mckinsey & Company
“The courses are not only technically very knowledgeable, but also genuinely helpful for professionals who want to work smarter, not harder.”
Karnika Bhalla - Yoda Learning Student
Karnika Bhalla
Delloite
“The training curriculum and course was extremely well designed, thorough and catered to use cases relevant to our domain of work.”
Yoshika Chhabra
Yoshika Chhabra
E & Y

Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

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

Download Course Curriculum

1101 – Learn the basics of the most important formula of Excel – VLOOKUP
1102 – When do I need to apply TRUE instead of FALSE in VLOOKUP?
1103 – What are the real-life applications of VLOOKUP with TRUE?
1104 – What is the difference between VLOOKUP and HLOOKUP?
1105 – Learn the basics of VLOOKUP’s best friend – MATCH function
1106 – Learn how MATCH can be used for slabs based data?
1107 – What is 2D Lookup and how can it avoid complexly Nested IF statements?
1108 – Learnt the trick to remember the 2D Lookup structure using VLOOKUP and MATCH
1109 – #1 Common mistake when writing 2D Lookup using VLOOKUP and MATCH
1110 – #2 Common mistake when writing 2D Lookup using VLOOKUP and MATCH
1111 – 2D Lookup Project for Practice – using VLOOKUP and MATCH
1112 – Learn to use 2D Lookup with HLOOKUP and MATCH
1113 – Learn the basics of the INDIRECT formula
1114 – Learn how the write dynamic formulas using INDIRECT function
1115 – What is 3D Lookup?
1116 – Learn to write 3D Lookup using VLOOKUP, MATCH, and INDIRECT functions
1117 – What is the “reverse” lookup, and why VLOOKUP fails you sometimes?
1118 – How to write a “reverse” lookup using INDEX with MATCH?
1119 – “Reverse” Lookup Project for Practice – using INDEX with MATCH
1120 – How is VLOOKUP different from SUMIFS?
1121 – How can you add multiple criteria while writing SUMIFS?
1122 – How to use SUMIFS with date range?
1123 – How to use a hidden trick of SUMIFS to calculate running total by ID column?
1124 – Three most important tricks of writing COUNTIFS function
1125 – What is the OFFSET function and why experts use it with the MATCH function?

Download Course Curriculum

1201 – How to change the case of the text to Upper case, Lower case, and Proper case?
1202 – How to remove extra spaces in a sentence using the TRIM function?
1203 – How to find the length of a text using the LEN function?
1204 – How to convert numbers stored as text back to a number using the VALUE function?
1205 – How can you add a chart inside a cell using the REPT function?
1206 – How to add missing zeroes in Cheque numbers using LEN and REPT?
1207 – How to join text from different cells using CONCATENATE and Ampersand?
1208 – How to use wildcard character asterisk with Find and Replace?
1209 – How to use wildcard character question mark with Find and Replace?
1210 – How to delete wildcard character asterisk using Find & Replace?
1211 – Why should you use MS Word’s Find and Replace over MS Excel’s?
1212 – How to replace one cell color format with another in the entire worksheet?
1213 – How to apply basic Text to Columns to split the text of the first name and last name?
1214 – How to keep the zeroes when applying Text to Column on data with preceding zeroes?
1215 – How to clean numbers that have a minus sign at the end instead of at the start?
1216 – How to convert numbers with Dr. and Cr. to positive and negative numbers?
1217 – How to rectify incorrectly formatted date such as 24.05.2007 using Text to Column?
1218 – How to rectify incorrectly formatted dates such as 20080009 using Text to Column?
1219 – How to use LEFT and RIGHT functions to extract data from a cell?
1220 – How can you increase the power of LEFT and RIGHT formulas using the SEARCH function?
1221 – How is MID more powerful than LEFT and RIGHT functions?
1222 – What is the difference between REPLACE and SUBSTITUTE functions?

Download Course Curriculum

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

Download Course Curriculum

1401 – How to use Conditional Formatting to make a Dashboard showing Growth vs Decline?
1402 – How to change cell colors automatically based on data entered in a cell?
1403 – Three popular Conditional Formatting Tricks – Heat Map, Data Bars, Icon Sets
1404 – Four tricks to color cells with Errors, Specific Values, Duplicates, and Blank
1405 – How to use Conditional Formatting to color the rows based on a cell value?
1406 – How to use
1407 – How to color the cells where input is from

Download Course Curriculum

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

Download Course Curriculum

1601 – How to insert Subtotals in a list of data in a worksheet?
1602 – How to remove Subtotals in a list of data in a worksheet?
1603 – How to create 2-level Subtotals in a list of data in a worksheet?
1604 – How to apply different colors to the row totals in a 2-level Subtotals?
1605 – How to merge cell blocks with the same names automatically?
1606 – How to Consolidate data tables from multiple worksheets in 1 minute?
1607 – How to add the 3rd dimension while consolidating tables from multiple sources?

Download Course Curriculum

1701 – How to password protect selected cells of a Sheet?
1702 – How to select the cells with formulas for locking them?
1703 – Using Workbook Structure protection and Very Hidden property
1704 – How to password protect an Excel file?

Download Course Curriculum

1801 – Printing Tricks – Repeat Header
1802 – Printing Tricks – Enable dashed gridlines
1803 – Printing Tricks – Custom Header & Footer
1804 – Printing Tricks – Page Order – Vertical vs. Horizontal
1805 – Printing Tricks for Financial Analysts – Part 1
1806 – Printing Tricks for Financial Analysts – Part 2
1807 – Printing Tricks – Print Entire Workbook

Download Course Curriculum

1901 – How to insert a picture in a cell comment?
1902 – How to work with different open workbooks and multiple worksheets?
1903 – How to create Hyperlink to different sheets?

Download Course Curriculum

2001 – Essential Settings of Charts – Part 1
2002 – Essential Settings of Charts – Part 2
2003 – Trendline
2004 – Thermometer Chart for comparing Actual vs Target – Basic
2005 – Thermometer Chart for comparing Actual vs Target – Advanced
2006 – 2 axis Chart and “Ghostlines” for forecasts
2007 – Horizontal Bar Chart
2008 – 100 percent Stacked Bar Chart
2009 – Who uses Waterfall Chart (Demo)?
2010 – What is the logic behind Waterfall Chart?
2011 – Waterfall Chart till Excel v. 2003 – Part 1
2012 – Waterfall Chart till Excel v. 2003 – Part 2
2013 – Combining Thermometer Chart with Stacked Column Chart
2014 – 100 percent Stacked Column Chart
2015 – Ringed Doughnut
2016 – Radar or Spider Chart
2017 – Gantt Chart for Project Management
2018 – 7 design tricks for Charts
2019 – Project – Lending Company – Part 1
2020 – Project – Lending Company – Part 1
2021 – Tornado or Butterfly Chart

Download Course Curriculum

2101 – Why do we learn Excel VBA Macros?
2102 – How to learn Excel VBA Macros?
2103 – How to enable Developer Tab in Excel?
2104 – Basics of Developer Tab in Excel
2105 – Important Macro Security Settings in Excel
2106 – How to save Macros in Excel for future use?
2107 – How to create a Macros in Excel – Part 1
2108 – How to run a Macro in all open Workbooks – Part 2
2109 – Using a Button to run a Macros in Excel – Part 3
2110 – Running a Macro in different ways in Excel – Part 4
2111 – Understanding VBA Workspace in Excel (Basics) – Part 5
2112 – Watch a Macro being recorded – Part 6
2113 – Searching Google for VBA Codes – using the 3 magic words
2114 – Searching Google for VBA Codes – in Blogs vs Forum

Download Course Curriculum

2201 – Basics of Array Formula (Ctrl + Shift + Enter) – Example 1
2202 – Basics of Array Formula (Ctrl + Shift + Enter) – Example 2
2203 – Building MAX IF and MIN IF with one condition – Example 1
2204 – Building MAX IF and MIN IF with one condition – Example 2
2205 – Array Formula using MAX() MIN() and IF() – 2 criteria
2206 – Find the four earliest dates using SMALL() and IF()
2207 – Calculating SUM of Top 3 numbers
2208 – SUMPRODUCT – Add numbers from the next column
2209 – Finding Overlapping Dates using SUMPRODUCT – Part 1 of 2
2210 – Finding Overlapping Dates using SUMPRODUCT – Part 2 of 2

Download Course Curriculum

Lifetime Access

Become an Excel Ninja
$ 99
  • 227 HD Videos
  • 15.3 Hours of Video
  • Lifetime Support

30 days Money-back Guarantee

FAQ

DO YOU PROVIDE SUPPORT DURING THE COURSE?

We have a chat panel in every lecture window. You can take screenshots, upload file, and send queries to our support desk. QnA should be related to the case study files ONLY.

WHAT VERSION OF EXCEL DO YOU TEACH?

We use Excel 2013 and we have added all new features up to Office 365.

DO I GET A CERTIFICATE AT EACH LEVEL?

No, you do not get a Certificate at each level. We only provide the course completion certificate.

CAN I DOWNLOAD THE COURSE?

No. But, you can watch the videos unlimited number of times.

CAN I ACCESS THE COURSE FROM MORE THAN ONE COMPUTER?

Yes, you can access the course from only 2-3 devices (computer or mobiles) with your login credentials.

EXPLAIN THE REFUND POLICY.

You can ask for refund within 30-days of enrollment. No questions asked.

FOR ENQUIRY ABOUT EXCEL NINJA COURSE? CONTACT US:

[email protected]

US: +1 203-349-9909 / IN: +91-8080042523

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