Preview Excel Mastery Program

Back

Course Tracker

Excel Course Tracker

Section 01 - Excel Shortcuts

Best Excel Shortcuts - Part 1 of 4

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

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

Hanging Formula (Excel Formula doesn't work)

Course Feedback

Section 03 - Essential Formatting Tricks

7 ways to use Format Cells

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

0905 Pivot Table Frequency Distribution

0906 3 Hidden Pivot Table Option & Settings

0907 Worst Features of Pivot Table

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

7 reasons of NA in VLOOKUP

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

XLOOKUP - Basic to Advanced

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

Section 22 - Excel Charts for Business Dashboards

15 Excel CHARTS recommended by Rishabh

Interactive Excel Dashboard with Option Buttons

Interactive Line chart for EXCEL DASHBOARD

Create Dynamics Dashboard in Excel using Sales Data

Section 23 - Advanced Tricks using Power Query & Power BI

Unpivot Columns (Multiple Columns to Rows)

Multiple LINES into Separate Cells (Split to Rows - Automation)

Pivot Table with Multiple Text Values (Don't Aggregate)

Fuzzy Matching for Multiple Excel Table (Reconciliation)

Merge Queries in Power Query (WHY - Handle Speed & Volume)

Excel Power Query - Custom Column Formula

Power Query for Beginners - Project #1

Section 24 - Combination Formula for Advanced users

VLOOKUP with TRUE and IF Function [Calculate Commissions $]

VLOOKUP with TRUE and $, SUMIFS (Multiple Criteria)

Excel Mastery eBooks

Excel Mastery eBook 1

Excel Mastery eBook 2

Excel Mastery eBook 3

Excel Mastery eBook 4

Excel Mastery eBook 5

Preview - Excel Mastery Program