|
Course Tracker
|
|
|
|
Excel Course Tracker
|
Preview
|
|
Section 01 - Excel Shortcuts
|
|
|
|
Best Excel Shortcuts - Part 1 of 4
5:00
|
Preview
|
|
|
Best Excel Shortcuts - Part 2 of 4
|
|
|
|
Best Excel Shortcuts - Part 3 of 4
|
|
|
|
Best Excel Shortcuts - Part 4 of 4
4:00
|
|
|
|
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
|
Preview
|
|
|
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)
2:00
|
|
|
|
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?
|
Preview
|
|
|
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?
|
Preview
|
|
|
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
1:00
|
|
|
|
0906 3 Hidden Pivot Table Option & Settings
4:00
|
|
|
|
0907 Worst Features of Pivot Table
5:00
|
|
|
|
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
11:00
|
|
|
|
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
25:00
|
|
|
|
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
67:00
|
|
|
|
Interactive Excel Dashboard with Option Buttons
10:00
|
|
|
|
Interactive Line chart for EXCEL DASHBOARD
10:00
|
|
|
|
Create Dynamics Dashboard in Excel using Sales Data
32:00
|
|
|
Section 23 - Advanced Tricks using Power Query & Power BI
|
|
|
|
Unpivot Columns (Multiple Columns to Rows)
5:00
|
|
|
|
Multiple LINES into Separate Cells (Split to Rows - Automation)
5:00
|
|
|
|
Pivot Table with Multiple Text Values (Don't Aggregate)
6:00
|
|
|
|
Fuzzy Matching for Multiple Excel Table (Reconciliation)
9:00
|
|
|
|
Merge Queries in Power Query (WHY - Handle Speed & Volume)
37:00
|
|
|
|
Excel Power Query - Custom Column Formula
9:00
|
|
|
|
Power Query for Beginners - Project #1
41:00
|
|
|
Section 24 - Combination Formula for Advanced users
|
|
|
|
VLOOKUP with TRUE and IF Function [Calculate Commissions $]
3:00
|
|
|
|
VLOOKUP with TRUE and $, SUMIFS (Multiple Criteria)
7:00
|
|
|
Excel Mastery eBooks
|
|
|
|
Excel Mastery eBook 1
|
|
|
|
Excel Mastery eBook 2
|
|
|
|
Excel Mastery eBook 3
|
|
|
|
Excel Mastery eBook 4
|
|
|
|
Excel Mastery eBook 5
|
|