Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Advanced Excel Tutorials - Become an Excel Ninja
Downloadable Files
Download "Excel Shortcuts eBook (Win vs. Mac)"
Excel Case Studies - Please Download
eBook - Become an Excel Ninja.
001 Ninja Shortcuts
0101 Introduction (5:12)
0102 How to learn the best shortcuts in this section? (1:17)
0103 What is faster than the regular Copy and Paste shortcuts? (3:23)
0104 - What is the shortcut to automatically fill selected cells? (1:16)
0105 - Impress your co-workers with these 6 [data selection and navigating] shortcuts (4:34)
0106 - Impress your co-workers with these 6 [rows and column] shortcuts (3:07)
0107 - Impress your co-workers with these 9 [formula writing & auditing] shortcuts (6:20)
0108 - Impress your co-workers with these 7 [formatting] shortcuts (4:39)
0109 - How to create your own custom shortcut key in 2 clicks? E.g. Paste Special Value (4:45)
0110 - How to audit a Financial Model or a Budget Sheet? (4:51)
Excel Shortcut Quiz #1
Advanced Excel Tutorials Feedback
002 Business Math Calculations
0201 - 2 ways MAX() function can help build Financial Models & Budget (2:44)
0202 Which two formula you must know for calculating Bidding & Auction pricing? (2:38)
0203 - Learn the basics of ROUND(), ROUNDUP() and ROUNDDOWN() (3:43)
0204 - Learn the advanced level of ROUND(), ROUNDUP() and ROUNDDOWN() (6:38)
0205 - Learn the 4 most important COUNT formula types (3:20)
0206 - Why is SUMPRODUCT so important for calculating weighted average? (3:34)
0207 - How do you perform compounding and discounting calculations in Excel? (2:39)
Excel Formula Warm-Up Quiz #2
Advanced Excel Tutorials Feedback
003 Formatting Tricks
0301 - Learn 3 hidden "Custom Format" tricks for formatting the data (5:18)
0302 - Learn 2 fastest tricks to copy-paste cell formats 3-5x times faster (1:56)
0303 - How to automate worksheet formatting using Cell Style? - Part 1 (4:02)
0304 - How to automate worksheet formatting using Cell Style? - Part 1 (1:15)
0305 - What is AutoFill and how to activate it? (1:19)
0306 - 5 tricks of AutoFill options that will save you time in data entry (2:47)
0307 - Date AutoFill vs. EOMONTH() formula - a Comparison (1:11)
0308 - What is the difference between Paste Special Transpose & TRANSPOSE() formula? (4:19)
Excel Formatting Tricks Quiz #3
Advanced Excel Tutorials Feedback
004 Formula Pre-Requisite
Case Study - Practice File
0401 - What is "locking" the cell reference - Basics of using $ (3:02)
0402 - Learn the two advanced cases of using $ while locking the cell reference (3:20)
Cell Referencing using - Practice 1 (4:22)
Cell Referencing using - Practice 2 (4:34)
Cell Referencing using - Special Trick (2:49)
Excel Cell Referencing Quiz #4
Advanced Excel Tutorials Feedback
005 Special Cell Selections
0501 - How to delete 1000s of errors in an Excel sheet fast? (2:40)
0502 - How to check for cells with formulas amongst 1000s of cells? (2:02)
0503 - How to fill occasional blank cells with a specific text or number? (2:08)
0504 - How to fill in blanks in Excel with the value above? (6:00)
0505 - How to fill in blanks in Excel with the value below? (2:04)
0506 - How to color the visible cells in a filtered list? (2:24)
Short Excel Go To - Special Quiz #5
Advanced Excel Tutorials Feedback
006 Sort & Filter
0601 - Learn the basics of Sorting a list (2:24)
0602 - Why is Custom Sorting important for Expert level Sorting? (5:18)
0603 - How to add one blank row between every row of a list? (2:58)
0604 - How to activate the hidden option of horizontal (left to right) Sorting? (3:29)
0605 - Learn the basics of Filtering a data (3:37)
0606 - Avoid this #1 common mistake while applying Filter (1:25)
0607 - How to analyse the data using Shortcuts after applying Filter? (2:52)
0608 - Why you should never apply SUM or AVERAGE function on a Filtered list? (5:06)
0609 - What is the difference between two versions of SUBTOTAL function? (2:40)
0610 - How to apply Filters on two different data sets of the same worksheet? (2:19)
0611 - How to apply Color Filter and Custom Filter? (1:13)
0612 - What is the difference between Filter & Advanced Filter? (4:30)
0613 - How to apply Advanced Filter with multiple criteria? (4:11)
0614 - 16 ways to set up advanced criteria for Advanced Filter (3:09)
Excel Sort, Filter & Advanced Filter Quiz #6
Advanced Excel Tutorials Feedback
007 Working with Dates
0701 - Do you know how Excel stores a Date value to avoid MDY vs DMY confusion? (3:16)
0702 - How to change Control Panel settings to change acceptable format for date input? (4:06)
0703 - 4 "must-know" date formulas for every Excel user - YEAR, MONTH, DAY, DATE (2:15)
0704 - How to get the day name of a date - Sun, Mon etc. (3:06)
0705 - 3 date formulas for Project Management - WEEKDAY(), WORKDAY(), NETWORKDAYS() (3:52)
0706 - What is the difference between WORKDAY.INTL() vs. WORKDAY() (4:53)
0707 - What is the difference between NETWORKDAYS.INTL() vs. NETWORKDAYS() (1:44)
Excel Date Quiz #7_1
0708 - 4 Tricks of Current Date and Time for Excel Modeling (1:29)
0709 - How to calculate 5th of next month? (3:41)
0710 - How to calculate expiry date for agreements and warranty period? (3:55)
Excel Date Quiz #7_2
Advanced Excel Tutorials Feedback
008 Data Input
0801 - How to create a basic drop down list? (2:37)
0802 - How to allow the source of drop down list to include future additions? (3:51)
0803 - How to add an error alert and instruction message to avoid invalid data input? (1:36)
0804 - What is the biggest bug in Data Validation and how can it be avoided? (3:52)
0805 - How to restrict user input to a fixed length of characters? (1:35)
0806 - How to write a custom formula to avoid invalid data entry in a cell? (2:01)
Excel Data Validation Quiz #8
Advanced Excel Tutorials Feedback
009 Organizing Data
0901 - Why is Grouping columns better than Hiding them? (2:58)
0902 - How to use Column Grouping effectively? (0:40)
0903 - How to activate a hidden trick of Grouping? (1:40)
0904 - How to make your Excel sheet look like a white page? (0:49)
0905 - How to activate two way Freeze panes? (5:31)
Excel View (Freeze Panes) Quiz #9
Advanced Excel Tutorials Feedback
010 Pivot Table
1001 - What are the Top 3 tricks of Pivot Table for day to day use? (3:58)
1002 - What are the two basic things to avoid for creating Pivot Tables successfully? (2:25)
1003 - Step 1 - How to create a Pivot Table? (2:40)
1004 - Step 2 - Understanding the 4 grids of a Pivot Table report (3:21)
1005 - Step 3 - How to calculate SUM, AVERAGE and COUNT in a Pivot Table (5:06)
1006 - Step 4 - How to calculate PERCENTAGE in a Pivot Table (2:22)
1007 - Step 5 - What are the different PERCENTAGE calculations types in a Pivot Table? (4:50)
Excel Pivot Table Quiz #10_1
Excel Pivot Table Quiz #10_2
1008 - Step 6 - Why is "Grouping" numbers important for analysis? (3:38)
1009 - Step 7 - Why is "Grouping" dates important for analysis? (4:09)
1010 - Step 8 - Why is "Grouping" text important for analysis? (3:36)
1011 - Step 9 - What are the 2 things to do if your Pivot Table's source data changes? (6:19)
1012 - Step 10 - How to activate "Auto-Refresh" Pivot Table setting? (0:41)
1013 - Step 11 - How to add Sparkline and Pivot charts to visualise your data? (2:13)
1014 - Step 12 - How to drill into the details of Pivot Table data? (0:59)
1015 - Step 13 - How to create 100s of Pivot Table reports in just 3 clicks? (3:39)
Excel Pivot Table Quiz #10_3
1016 - Step 14 - What is Pivot Table Slicer and how is it different from Report Filter? (2:33)
1017 - Pivot Table Project for Practice - Sales Data Analysis - Part 1 of 2 (7:57)
1018 - Pivot Table Project for Practice - Sales Data Analysis - Part 2 of 2 (3:09)
1019 - Pivot Table Project for Practice - Inventory Analysis - Part 1 of 1 (7:01)
Excel Pivot Table Quiz #10_4
Advanced Excel Tutorials Feedback
011 Lookup & Conditional Calculations
1101 - Learn the basics the most important formula of Excel - VLOOKUP (7:59)
1102 - When do I need to a apply TRUE instead of FALSE in VLOOKUP? (7:17)
1103 - What are the real-life applications VLOOKUP with TRUE? (6:01)
1104 - What is the difference between VLOOKUP and HLOOKUP? (2:45)
1105 - Learn the basics of VLOOKUP's best friend - MATCH function (3:38)
1106 - Learn how MATCH can be used for slabs based data? (3:46)
1107 - What is 2D Lookup and how can it avoid complex Nested IF statements? (5:54)
1108 - Learnt the trick to remember the 2D Lookup structure using VLOOKUP and MATCH (1:18)
1109 - #1 Common mistake when writing 2D Lookup using VLOOKUP and MATCH (3:51)
1110 - #2 Common mistake when writing 2D Lookup using VLOOKUP and MATCH (2:20)
1111 - 2D Lookup Project for Practice - using VLOOKUP and MATCH (6:13)
1112 - Learn to use 2D Lookup with HLOOKUP and MATCH (6:49)
1113 - Learn the basics of INDIRECT formula (4:24)
1114 - Learn how the write dynamic formulas using INDIRECT function (6:02)
1115 - What is 3D Lookup? (3:39)
1116 - Learn to write 3D Lookup using VLOOKUP, MATCH and INDIRECT functions (7:11)
1117 - What is "reverse" lookup, and why VLOOKUP fails you sometimes? (3:27)
1118 - How to write a "reverse" lookup using INDEX with MATCH? (8:12)
1119 - "Reverse" Lookup Project for Practice - using INDEX with MATCH (6:07)
1120 - How is VLOOKUP different from SUMIFS? (4:28)
1121 - How can you add multiple criteria while writing SUMIFS? (4:12)
1122 - How to use SUMIFS with date range? (5:20)
1123 - How to use a hidden trick of SUMIFS to calculate running total by ID column? (5:31)
1124 - Three most important tricks of writing COUNTIFS function (6:10)
1125 - What is OFFSET function and why experts use it with MATCH function? (15:08)
Excel Lookup Quiz #11_1
Excel Lookup Quiz #11_2
Excel Lookup Quiz #11_3
Excel Lookup Quiz #11_4
Excel Lookup Quiz #11_5
Excel Lookup Quiz #11_6
Excel Lookup Quiz #11_7
Fuzzy Lookup (1:29)
Advanced Excel Tutorials Feedback
012 Data Cleaning
1201 - How to change case of the text to Upper case, Lower case and Proper case? (1:42)
1202 - How to remove extra spaces in a sentence using TRIM function? (1:43)
1203 - How to find the length of a text using LEN function? (1:33)
1204 - How to convert numbers stored as text back to number using VALUE function? (3:16)
1205 - How can you add a chart inside a cell using REPT function? (1:14)
1206 - How to add missing zeroes in Cheque numbers using LEN and REPT? (4:30)
1207 - How to join text from different cells using CONCATENATE and Ampersand? (4:55)
1208 - How to use wildcard character asterisk with Find and Replace? (3:08)
1209 - How to use wildcard character question mark with Find and Replace? (2:23)
1210 - How to delete delete wildcard character asterisk using Find & Replace? (2:38)
1211 - Why should you use MS Word's Find and Replace over MS Excel's? (6:06)
1212 - How to replace once cell color format with another in the entire worksheet? (4:31)
1213 - How to apply basic Text to Column to splitthe text of first name and last name? (2:50)
1214 - How to keep the zeroes when applying Text to Column on data with preceeding zeroes? (4:06)
1215 - How to clean numbers which have minus sign at the end instead of at the start? (2:33)
1216 - How to convert numbers with Dr. and Cr. to positive and negative numbers? (2:02)
1217 - How to rectify incorrectly formatted date such as 24.05.2007 using Text to Column? (3:24)
1218 - How to rectify incorrectly formatted date such as 20080009 using Text to Column? (3:18)
1219 - How to use LEFT and RIGHT functions to extract data from a cell? (4:19)
1220 - How can you increase the power of LEFT and RIGHT formulas using SEARCH function? (2:26)
1221 - How is MID more powerful than LEFT and RIGHT functions? (3:42)
1222 - What is the difference between REPLACE and SUBSTITUTE functions? (7:13)
1223 FlashFill for Data Cleaning in Excel (7:21)
Excel Data Cleaning Quiz #12_1
Excel Data Cleaning Quiz #12_2
Excel Data Cleaning Quiz #12_3
Excel Data Cleaning Quiz #12_4
1224 SEARCH vs FIND (5:47)
Excel Data Cleaning Quiz #12_5
Advanced Excel Tutorials Feedback
013 Logical Formulas
1301 - What are the Top 7 error and data validity checking formulas? (4:18)
1302 - How to write IF formulas - Basic to Intermediate level? (5:19)
1303 - The two must under-used logical formulas - AND & OR (5:15)
1304 - How to combine IF with AND & OR formulas to make the logic 5x times powerful? (3:35)
Excel Logical Formula Quiz #12_1
Excel Logical Formula Quiz #12_2
Advanced Excel Tutorials Feedback
014 Conditional Formatting
1401 - How to use Conditional Formatting to make a Dashboard showing Growth vs Decline? (5:47)
1402 - How to change cell colors automatically based on data entered in a cell? (3:15)
1403 - Three popular Conditional Formatting Tricks - Heat Map, Data Bars, Icon Sets (2:30)
1404 - Four tricks to color cells with Errors, Specific Values, Duplicates and Blank (5:03)
1405 - How to use Conditional Formatting to color the rows based on a cell value? (4:12)
1406 Conditional Formatting: Formula based w. AND() - 2-way input Coloring (5:22)
1407 Conditional Formatting: Formula based w. COUNTIFS() - Highlighting inputs if from restricted list (4:14)
Excel Conditional Formatting Quiz #14_1
Excel Conditional Formatting Quiz #14_2
Advanced Excel Tutorials Feedback
015 What-If Analysis
1501 - How to add Spin buttons to make your financial model interactive? (5:09)
1502 - How to work around a major bug of Spin Button? (4:03)
Excel What-If Analysis Quiz #15_1
1503 - How to use Scenario Manager to set up three scenarios - Base, Best & Worst? (6:59)
1504 - How to use Goal Seek to calculate the optimal EMI for repaying a Loan? (4:33)
Excel What-If Analysis Quiz #15_2
1505 - How to do Sensitivity Analysis using Data Tables feature of What-If Analysis? (8:22)
1506 - How to do Sensitivity Analysis of Loan EMI using Data Tables? (5:22)
1507 - Part 1: How to do use Data Tables with more than one output variable? (2:39)
1508 - Part 2: How to do use Data Tables with more than one output variable? (10:08)
Advanced Excel Tutorials Feedback
016 Subtotal and Consolidation
1601 - How to insert Subtotals in a list of data in a worksheet? (5:34)
1602 - How to remove Subtotals in a list of data in a worksheet? (1:16)
1603 - How to create 2-level Subtotals in a list of data in a worksheet? (4:28)
1604 - How to apply different color to the row totals in a 2-level Subtotals? (1:44)
1605 - How to Consolidate data tables from multiple worksheets in 1 minute? (4:07)
1606 - How to add a 3rd dimension while consolidating tables from multiple sources? (7:55)
Review our Course
Excel Subtotal and Data Consolidation Quiz #16
017 Security & Password
1701 - How to password protect selected cells of a Sheet? (6:41)
1702 - How to select the cells with formulas for locking them? (3:02)
1703 - Using Workbook Structure protection and Very Hidden property (5:38)
1704 - How to password protect an Excel file? (2:46)
Excel Workbook & Worksheet Security Quiz #17
Advanced Excel Tutorials Feedback
018 Printing & Comments - Part 1
1801 - Printing Tricks - Repeat Header (2:19)
1802 - Printing Tricks - Enable dashed gridlines (1:14)
Excel Printing Quiz #18_1
1803 - Printing Tricks - Custom Header & Footer (2:53)
1804 - Printing Tricks - Page Order - Vertical vs. Horizontal (2:12)
Excel Printing Quiz #18_2
1805 - Printing Tricks for Financial Analysts - Part 1 (1:48)
1806 - Printing Tricks for Financial Analysts - Part 2 (1:47)
1807 - Printing Tricks - Print Entire Workbook (0:59)
Excel Printing Quiz #18_3
Advanced Excel Tutorials Feedback
019 Printing & Comments - Part 2
1901 - How to insert a picture in a cell comment? (4:23)
1902 - How to work with different open workbooks and multiple worksheets? (7:08)
1903 - How to create Hyperlink to different sheets? (3:34)
Review our Course
Excel Multiple Windows, Comments, Hyperlinking Quiz #19
20 Charts
Download Practice Files for Advanced Charts
2001 - Essential Settings of Charts - Part 1 (5:18)
2002 - Essential Settings of Charts - Part 2 (6:23)
2003 - Trendline (3:18)
2004 - Thermometer Chart for comparing Actual vs Target - Basic (5:36)
2005 - Thermometer Chart for comparing Actual vs Target - Advanced (6:20)
2006 - 2 axis Chart and "Ghostlines" for forecasts (4:23)
2007 - Horizontal Bar Chart (4:33)
2008 - 100 percent Stacked Bar Chart (4:47)
2009 - Who uses Waterfall Chart (Demo)? (5:09)
2010 - What is the logic behind Waterfall Chart? (6:52)
2011 - Waterfall Chart till Excel v. 2003 - Part 1 (6:28)
2012 - Waterfall Chart till Excel v. 2003 - Part 2 (6:04)
2013 - Combining Thermometer Chart with Stacked Column Chart (7:20)
2014 - 100 percent Stacked Column Chart (3:17)
2015 - Ringed Doughnut (3:47)
2016 - Radar or Spider Chart (4:57)
2017 - Gantt Chart for Project Management (5:04)
2018 - 7 design tricks for Charts (7:32)
2019 - Project - Lending Company - Part 1 (7:57)
2020 - Project - Lending Company - Part 2 (3:52)
2021 - Tornado or Butterfly Chart (7:57)
Advanced Excel Tutorials Feedback
21 Basics of VBA Macros
1_Why do we learn Excel VBA Macros (4:17)
2_How to enable Developer Tab in Excel (2:02)
3_Saving a Macro-enabled WorkBook (XLSM) (2:53)
4_Important Macro Security Settings in Excel (2:13)
5_Developer tab - Essential Buttons (0:54)
6_Head & Tail of a Macros (Sub Procedure & User Defined Function) (4:00)
7_Click Here button to run a Macro (1:46)
8_Macros - Record & Run (5:07)
9_Macros - Viewing the code (2:41)
10_Macro-free vs. Macro-enabled Workbook (0:55)
11_Visual Basic Editor (VBE) (3:18)
12_Personal Macro WB (5:20)
13_Running a Macro in different ways in Excel (2:46)
14_Excel Macros for Non-Programmers (11:48)
15_Using Google to locate useful VBA codes - 1 (7:42)
16_Using VBE and Macro Recorder to customize Macro code (4:46)
17_Using Google to locate useful VBA codes - 2 (6:29)
Advanced Excel Tutorials Feedback
1103 - What are the real-life applications VLOOKUP with TRUE?
Complete and Continue