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