| Introduction |
|
| | Program Introduction |
Preview |
| Section 01 - Excel Shortcuts |
|
| | Best Excel Shortcuts - Part 1 of 4 |
Preview |
| | Best Excel Shortcuts - Part 2 of 4 |
|
| | Best Excel Shortcuts - Part 3 of 4 |
|
| | Best Excel Shortcuts - Part 4 of 4 |
|
| | How to create custom shortcuts using Quick Access Toolbar (QAT)? |
|
| Section 02 - Essentials for Beginners |
|
| | Three easy ways to do SUM or total of numbers |
Preview |
| | Basics of MAX & MIN formula |
|
| | Use of MAX formula in Finance (Tax & Billing) |
|
| | Use of SMALL & LARGE formula in Finance |
|
| | Using MEDIAN formula to remove outliers |
|
| | Using Paste Special (Value, Transpose) for daily work |
|
| | Paste Special (Transpose) vs TRANSPOSE Formula |
|
| | Using COUNTBLANK to count blank cells |
|
| | Difference between COUNT vs. COUNTA |
|
| | Using Status Bar for Quick Calculations |
|
| | Using SUMPRODUCT Function in Finance & Costing |
|
| | Easiest way to calculate YoY Growth Percentage |
|
| | What is POWER formula |
|
| | How to calculate the maturity amount of Fixed Deposit |
|
| | Basics of ROUND formula |
|
| | Basics of ROUNDUP & ROUNDDOWN formula |
|
| | Uses of ROUNDUP & ROUNDDOWN formula in Finance |
|
| | Basics of MROUND formula |
|
| | Advanced Trick of Round Formula in Finance |
|
| | Using Autofill feature to create automatic list of Dates |
|
| | Using Autofill feature to create automatic list of Numbers |
|
| | Course Feedback |
|
| Section 03 - Essential Formatting Tricks |
|
| | 7 ways to use Format Cells |
Preview |
| | How to hide a text without coloring the cell white? |
|
| | What is - Numbers stored as Text? |
|
| | How to write full Cheque numbers with missing zeroes? |
|
| | What is Superscript inside Format Cells? |
|
| | Basics of formatting tabular datasets |
|
| | How to format lengthy headings using Wrap Text & Alignment? |
|
| | How is Format Painter used to copy & paste formats? |
|
| | How to use Format Painter multiple times? |
|
| | How to remove all formatting in one click? |
|
| | Working with Tables |
|
| | How to color multiple sheet tabs? |
|
| | How to use Freeze Panes for Rows & Columns at the same time? |
|
| | Grouping |
|
| | Gridlines |
|
| | How to create a simple drop-down list with Data Validation? |
|
| | How to remove a drop-down list? |
|
| | How to activate the Developer Tab in 2007-2019 in Excel? |
|
| | How to create an interactive Spin Button for an Excel Dashboard? |
|
| | How to create an interactive Scroll Bar for an Excel Dashboard? |
|
| | 7 Tricks of Comments - Basic to Expert |
|
| | 4 Tricks of Hyperlinking - Basic to Expert |
|
| | How to hide a sheet so that no one can Unhide easily? |
|
| | Course Feedback |
|
| Section 04 - Sort & Filter |
|
| | How to Sort numbers from largest to smallest? |
|
| | How to Sort text alphabetically (A-Z)? |
|
| | How to Sort dates oldest to newest? |
|
| | How to Sort rows by cell color? |
|
| | Best Practice before you sort any dataset |
|
| | How to do 2-level Sorting? |
|
| | The hidden trick of Horizontal Sorting |
|
| | The hidden trick of Sorting using a custom list |
|
| | The hidden trick to add a blank row between every row using Sort |
|
| | Basics of Filter & Calculating SUM on the filter list |
|
| | The most common mistake Filter users make while choosing datasets |
|
| | Importance of SUBTOTAL formula in a Filtered dataset |
|
| | SUBTOTAL formula's confusing parameters - Mystery Solved |
|
| | Number Filter option in Filter |
|
| | How to navigate a Filtered dataset using Shortcuts? |
|
| | How is Advanced Filter different than General Filter? |
|
| | The hidden trick to apply separate Filters on two datasets of the same worksheet? |
|
| | The hidden trick to paste values on a Filtered dataset |
|
| | How to use Slicer on a Filtered dataset? |
|
| | Course Feedback |
|
| Section 05 - Cell Reference ($) |
|
| | Use of Cell Referencing ($) in formulas - Explained |
|
| Section 06 - Dates |
|
| | Why does a Date in Excel looks like a random number? |
|
| | How to calculate the duration using two dates with timestamp? |
|
| | Date formulas - DAY, MONTH, YEAR & DATE - Part 1 |
|
| | Date formulas - DAY, MONTH, YEAR & DATE - Part 2 |
|
| | Date formulas - TEXT & WEEKDAY - Part 1 |
|
| | Date formulas - TEXT & WEEKDAY - Part 2 |
|
| | Why is EDATE referred as Expiry Date? |
|
| | Why is EOMONTH referred as End of Month? |
|
| | Date formulas - TODAY & NOW with Shortcuts |
|
| | The hidden trick to rectify dates format using Text to Columns |
|
| | How to use WORKDAY.INTL formula to calculate project deadline? |
|
| | How to use NETWORKDAYS.INTL to calculate working days between two dates? |
|
| | The hidden formula of DATEDIF |
|
| | How to find the 1st, 3rd and 5th Saturday of any month |
|
| | Course Feedback |
|
| Section 07 - Logical Functions |
|
| | Basics of Logical Formula - Part 1 |
|
| | Basics of Logical Formula - Part 2 |
|
| | Basics of Logical Formula - Part 3 |
|
| | Quick Recap of Logical Formulas with advanced exercises |
|
| | Using multiple Logical formulas - IF, AND, OR in an advanced exercise |
|
| | Logical Check Formulas for Text, Numbers, & Errors including IFERROR |
|
| | Course Feedback |
|
| Section 08 - Data Cleaning |
|
| | How to remove duplicate values? |
|
| | Using UPPER, PROPER, & LOWER formulas to change the case of the text |
|
| | Using TRIM formula to remove excess spaces in a sentence |
|
| | Using VALUE formula to convert numbers stored as text to a number format |
|
| | Using LEN formula to calculate the number of characters in a cell |
|
| | Basics of RIGHT, LEFT & MID formulas |
|
| | SEARCH vs FIND |
|
| | SEARCH & MID |
|
| | How to find and delete all the error values at once? |
|
| | Shortcut to make best use of Go To (Special) technique |
|
| | How to fill intermittent blank cells using Go To (Special) technique? |
|
| | Other uses of Ctrl G - Formula, Constants, Visible cells |
|
| | Using Text to Column feature to split data values - Part 1 (Delimited) |
|
| | Using Text to Column feature to split data values - Part 2 (Fixed Width) |
|
| | When is Text to Column feature used to change the Data Format (General, Text)? |
|
| | How to convert a series of email IDs to a vertical list for Attendance? |
|
| | How can Text to Columns help in writing correct SUM & VLOOKUP formulas? |
|
| | Using CONCATENATE and Ampersand (&) to join values |
|
| | Find & Replace - Part 1 (with Wildcard Character) |
|
| | Find & Replace - Part 2 (MS Word vs MS Excel) |
|
| | Quick Recap of Advanced Find & Replace |
|
| | SUBSTITUTE - the formula version of Find & Replace |
|
| | FlashFill - Basics to Advanced |
|
| | Project - FlashFill, T2C, SUBSTITUTE - Bank Data |
|
| | Project - Electricity Consumption - Method #1 |
|
| | Project - Electricity Consumption - Method #2 |
|
| | Course Feedback |
|
| Section 09 - Pivot Table |
|
| | Pivot Table (A-Z) Part 1 |
|
| | Pivot Table (A-Z) Part 2 |
|
| | Expert-level Pivot Table Trick using Power Query - Split to Rows |
|
| | Expert-level Pivot Table Trick using Power Query - Unpivot Column |
|
| | Extra Files |
|
| Section 10 - Lookup Formulas (A-Z) |
|
| | Basics of VLOOKUP for first time users - Why do we need it? |
|
| | Basics of VLOOKUP for first time users - fix Table Array using $ |
|
| | Basics of VLOOKUP for first time users - TRUE vs FALSE - Part 1 |
|
| | Basics of VLOOKUP for first time users - TRUE vs FALSE - Part 2 |
|
| | Common mistakes of VLOOKUP users - Why do we get N/A? |
|
| | How to do inter-worksheet VLOOKUP? |
|
| | Vlookup vs. Hlookup - Simplified |
|
| | Using VLOOKUp with TRUE for doing Debtors Ageing Analysis |
|
| | Using VLOOKUp with TRUE for doing Score Grading |
|
| | Using VLOOKUp with TRUE for doing Food Quality Grading |
|
| | Using VLOOKUp with TRUE for calculating correct tax rate as per applicable date |
|
| | Basics of MATCH formula - Part 1 |
|
| | Basics of MATCH formula - Part 2 |
|
| | How to do 2D Lookup using VLOOKUP & MATCH - Example 1 |
|
| | How to do 2D Lookup using VLOOKUP & MATCH - Example 2 |
|
| | How to do inter-worksheet 2D Lookup using VLOOKUP & MATCH? - Example 1 |
|
| | How to do inter-worksheet 2D Lookup using VLOOKUP & MATCH? - Example 2 |
|
| | LOOKUP vs. VLOOKUP vs. HLOOKUP vs. MATCH |
|
| | How to do Reverse Lookup using INDEX & MATCH formula - Part 1 |
|
| | How to do Reverse Lookup using INDEX & MATCH formula - Part 2 |
|
| | Exercise | Using INDEX & MATCH formula to do Reverse Lookup |
|
| | Fuzzy Lookup can do what VLOOKUP can't - Lookup for data with different spelling |
|
| | Basics of INDIRECT formula |
|
| | Create a Dashboard from 25+ worksheets using INDIRECT formula |
|
| | Use VLOOKUP & INDIRECT formula to pull data from multiple sheets |
|
| | How to do 3D Lookup using VLOOKUP, MATCH, INDIRECT & name ranges? |
|
| | Basics - Using OFFSET formula to create Dynamic Ranges |
|
| | Using OFFSET to create dynamic calculations - Part 1 (Intermediate) |
|
| | Using OFFSET to create dynamic calculations - Part 2 (Advanced) |
|
| Section 11 - Conditional Aggregation |
|
| | Basics of COUNTIF formula |
|
| | Basics of SUMIF formula |
|
| | Basics of AVERAGEIF formula |
|
| | COUNTIFS & SUMIFS - Part 1 (Intermediate) |
|
| | COUNTIFS & SUMIFS - Part 2 (Advanced) |
|
| | Exercise - Using SUMIFS & COUNTIFS with 3 criteria |
|
| | Exercise - Using SUMIFS to find sum of values between two dates |
|
| | Using COUNTIFS to find duplicates and reconcile two lists |
|
| | The hidden trick of COUNTIFS to calculate running count based on the ID value |
|
| | The hidden trick of COUNTIFS to do VLOOKUP for duplicate values |
|
| | The hidden trick of SUMIFS to calculate running sum total based on the ID value |
|
| Section 12 - Conditional Formatting |
|
| | Conditional Formatting - Color cells based on errors, blanks, duplicates, user-defined value |
|
| | Conditional Formatting - Color cells using Data Bars, Icon Sets, Color Scales |
|
| | Create a Dashboard for comparing Actual vs Budget using Conditional Formatting |
|
| | Using Conditional Formatting for changing the cell color based on list value chosen |
|
| | Formula-based Conditional Formatting - color the entire row based on user-selected value |
|
| | Formula-based Conditional Formatting - color the cell that matches two user criterias |
|
| | Formula-based Conditional Formatting - Highlight cells that have prohibited values |
|
| Section 13 - What If Analysis |
|
| | Basics of Data Table feature of What-If Analysis |
|
| | Using Data Table feature of What-If Analysis with INDIRECT formula |
|
| | Advanced use case of Data Table feature for Loan EMI payments |
|
| | Limitations of using Scroll Bar & Spin Button |
|
| | Basics - Using Goal Seek to back-calculate |
|
| | Basics - Scenario Manager for Best, Basics - Scenario Manager for Best, Worst & Base Case |
|
| Section 14 - Report Consolidation |
|
| | The hidden trick of Consolidation using SUM for multiple sheets |
|
| | How to use CONSOLIDATE feature of Excel? - Part 1 |
|
| | How to use CONSOLIDATE feature of Excel? - Part 2 |
|
| | How to use SUBTOTAL feature of Excel using one criteria? |
|
| | How to use SUBTOTAL feature of Excel using two criteria? |
|
| | The hidden trick of SUBTOTAL feature & Go To (Special) to merge cells into blocks |
|
| Section 15 - Printing |
|
| | Top 10 Printing Tricks & Settings |
|
| Section 16 - Workbook & Worksheet Security |
|
| | Level of Password Security - File vs. Sheet vs. Workbook Structure |
|
| | How to hide a sheet so that no one can Unhide easily? |
|
| | How to remove “Protect sheet” Password with Notepad? |
|
| | How to remove remove “Protect workbook” Password with Notepad? |
|
| Section 17 - Special Tricks |
|
| | The hidden trick to Compare two Excel files |
|
| | The hidden trick to join cell values without using CONCATENATE |
|
| | The hidden trick to activate Data Entry Form |
|
| | How to Copy Text from Image | OneNote Tutorial |
|
| | PDF to Word Converter - Without Software |
|
| Section 18 - Advanced Excel Projects for Finance |
|
| | Convert "Dr" & "Cr" suffix of Tally dayasets into positive & negative values |
|
| | Clean-up complex Bank Statement sheet of Borrowers |
|
| | Analyse payments made to Suppliers using FlashFill, VLOOKUP & Pivot Table |
|
| | Extract multiple voucher nos. from a cell to create a list |
|
| | Create a Frequency Distribution of sales ticket size using Pivot Table? |
|
| | Calculate Stock Revaluation write-off amount using VLOOKUP with TRUE and MATCH |
|
| | Form 26AS - TDS | What is Form 26AS? |
|
| | PAN Assessee Status - using MID & VLOOKUP |
|
| Section 19 - Advanced Excel Projects for Sales |
|
| | Calculate Sales Commission using advanced SUMIFS and VLOOKUP with TRUE |
|
| | Allocate sales value of a project to the individual team members |
|
| | Sales Visualization - without Charts |
|
| Section 20 - Office 365 Updates |
|
| | UNIQUE |
|
| | Concat & TextJoin |
|
| | Project - TextJoin & IF |
|
| | IFS & SWITCH |
|
| | MAXIFS & MINIFS |
|
| | Filter |
|
| | SORT |
|
| | SORTBY |
|
| | Sequence |
|
| | LET |
|
| Section 21 - Basics of Macro |
|
| | Why do we learn Excel VBA Macros? |
|
| | How to learn Excel VBA Macros? |
|
| | How to enable Developer Tab in Excel? |
|
| | Basics of Developer Tab in Excel |
|
| | Important Macro Security Settings in Excel |
|
| | How to save Macros in Excel for future use? |
|
| | How to create a Macros in Excel? |
|
| | How to run a Macro in all open Workbooks? |
|
| | Using a Button to run a Macros in Excel |
|
| | Running a Macro in different ways in Excel |
|
| | Understanding VBA Workspace in Excel (Basics) |
|
| | Watch a Macro being recorded |
|
| | Searching Google for VBA Codes - using the 3 magic words |
|
| | Searching Google for VBA Codes - in Blogs vs Forum |
|
| Excel Mastery eBooks |
|
| | Excel Mastery eBook 1 |
|
| | Excel Mastery eBook 2 |
|
| | Excel Mastery eBook 3 |
|
| | Excel Mastery eBook 4 |
|
| | Excel Mastery eBook 5 |
|