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:33)
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:17)
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:23)
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:48)
Excel Pivot Table Quiz #10_1
Excel Pivot Table Quiz #10_2
1008 - Step 6 - Why is "Grouping" numbers important for analysis? (3:37)
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:29)
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:41)
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:48)
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:20)
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:05)
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
1213 - How to apply basic Text to Column to splitthe text of first name and last name?
Complete and Continue
Lesson Content Locked
Your session expired. Please sign in again to continue.
Login