Excel Essential Formulas And Functions
By Jobaaj Learnings
₹1,499/-₹2,999/-
186,800 Enrolled Students
🎉
Expand Your Knowledge
Flat 80% off + Extra 10% Cart Discount
Flat 80% off + Extra 10% Cart Discount
🎉
🚀 FREE AI WORKSHOP! 🚀
Join us this weekend and unlock the power of AI to stay ahead in your career!
"One person using AI will replace 100 professionals not using AI" – Are you ready?
Reserve Your Spot Now!Microsoft Excel Complete Mastery
Excel Complete Mastery Course is a comprehensive course that equips students with the skills to work with Microsoft Excel from beginner to advanced level. In this course, you will learn the fundamental features of Excel, such as creating spreadsheets, entering and formatting data, and basic calculations. This course is suitable for anyone who wants to enhance their Excel skills, whether for personal or professional use.
27 Hours 19 Mins
₹6,999/-₹9,999/-
Join the course
Created by
Jobaaj Learnings
Course description
Microsoft
Excel Complete Mastery Course is a comprehensive program that covers everything from the basics to advanced-level Excel features. In this course, students will learn how to create and manipulate spreadsheets, enter and format data, and perform basic calculations. The course also covers advanced techniques such as data analysis, pivot tables, macros, and advanced formulas.
The course is designed for individuals who want to improve their Excel skills, regardless of their current level of expertise. Beginners will start with basic Excel concepts and gradually progress to more complex topics, while those who are already familiar with Excel can skip the introductory material and dive straight into advanced techniques.
Throughout the course, students will have access to expert instructors who will guide them through each lesson, ensuring that they have a firm understanding of the material. The instructors will provide clear explanations, practical examples, and step-by-step demonstrations, making it easy for students to follow along.
By the end of this course, students will have mastered all the essential Excel skills needed for personal or professional use. They will be able to create, edit, and format spreadsheets, perform basic and advanced calculations, analyze data, and automate repetitive tasks with macros. Additionally, students will receive a certificate of completion, which they can add to their resume or LinkedIn profile to showcase their newly acquired skills.
Prerequisite
-
No prerequisites
What you will Learn ?
-
Greater confidence in Excel usage
-
Improved data analysis abilities
-
Increased productivity through automation
-
Enhanced career opportunities and prospects
Lesson List
1
Getting Started
Resources
The Ribbon
00:06:43
Work surface
00:03:57
Work Surface
00:03:50
The Navigations
00:05:41
First File
00:04:51
Data Presentable
00:05:34
Basic match Operator
00:07:40
BODMAS Rule
00:06:45
Break data into multiple sheets
00:07:29
Worksheet Operations
00:09:39
One Drive
00:05:13
Autofill the series of Numbers
00:05:50
Autofill the series of Dates
00:06:16
Autofill the series of Text
00:07:25
AutoFile by Keyboard
00:06:58
The Fill Series
00:07:10
Columns & Rows Manupilation
00:04:46
Columns & Rows Manupilation Keyboard
00:04:46
Highlight the Data Smartly
00:10:01
Cell Formats
00:05:07
Cell Formats-Shortcuts
00:02:53
Custom Format Ctrl+1
00:07:40
Style Operation
00:05:51
Own Styles
00:08:17
Reusing & manipulating Styles
00:07:17
Assignment 1 - Problem
00:02:08
Ass 1 - Solution
00:04:06
Ass 2 - Problem
00:01:17
Ass 2 - Solution
00:05:14
Ass 3 - Problem
00:01:27
Ass 3 - Solution
00:05:14
Ass 4 - Problem
00:01:48
Ass 4 - Solution
00:02:42
2
Essential Formula Knowledge
Function and Formulas Anatomy
00:08:07
Parts of Functions
00:10:07
Maths Function Part 1
00:08:00
Maths Function Part 2
00:07:40
Maths Function Part 3
00:09:25
Type of Refrence-Part1
00:06:17
Type of Refrence-Part2
00:07:10
Type of Refrence-Part3
00:09:14
Type of Refrence-Part4
00:05:38
Type of Refrence-Part5
00:08:06
Logical Operators
00:05:49
Statical Function-Part1
00:05:28
statistical Function-Part2
00:08:00
statistical Function-Part3
00:05:02
Logical Function-IF (Text)
00:08:41
Logical Function-IF (Calculation)
00:04:09
Logical Function-AND/OR
00:06:36
Logical Function-AND/OR with IF
00:07:46
Logical Function-Not with IFError
00:07:05
Logical Function-Nested IF
00:08:03
Logical Function-IFS
00:07:38
Ass 1 - Problem
00:03:06
Ass 1 - Solution
00:06:30
Ass 2 - Problem
00:01:56
Ass 2 - Solution
00:04:40
Ass 3 - Problem
00:03:46
Ass 3 - Solution
00:10:46
3
Intermediate Formula Knowledge
Function to change case
00:09:15
Combine
00:12:56
LEN to check valid data
00:04:22
Find & Search
00:06:58
Replacing the text
00:09:09
Find with Replace implementation
00:08:53
Get desire number of characters
00:06:33
Introduction to Date category
00:04:52
Basic of Date
00:06:50
Text function to chage the Date Pattern
00:10:09
Edate/EOMOnth
00:05:37
Workdays/Networkdays
00:09:56
DatedIF-Hidden Formula
00:05:55
Conditional Function Intro
00:10:12
CountIF to count records based on condition
00:08:23
SumIF to add based on condition
00:07:06
SumIF make it more dynamic
00:06:16
AverageIf
00:04:58
COUNTIFS counting on multiple criteria
00:09:44
SUMIFS to addup values on multiple criteria
00:07:53
NAME range-Way to give name to data
00:08:04
Naming rules we should know
00:06:04
Name Manager-Quick way to modify
00:09:00
Applying Name's in Formuls
00:12:41
Ass 1 - Problem
00:03:35
Ass 1 - Solution
00:08:17
Ass 2 - Problem
00:03:28
Ass 2 - Solution
00:09:36
Ass 3 - Problem
00:04:01
Ass 3 - Solution Part 1
00:03:22
Ass 3 - Solution Part 2
00:07:14
Ass 3 - Solution Part 3
00:06:13
4
All about Lookup's In Excel
Introduction to Lookup
00:03:51
What is Vlookup
00:07:40
Syntax of Vlookup
00:08:18
Vlookup Example
00:06:17
Match function for Exact search
00:06:03
Match with 1,0,-1
00:06:22
Vlookup Match-Two Way Lookup
00:06:37
Vlookup With Match and Mix Ref
00:06:35
Introduction to Double Vlookup
00:06:24
An Example of Double Vlookup
00:04:10
Introduction to INDEX Function
00:07:19
Index with One Match
00:06:35
Index with Two Match
00:06:26
Problem with Vlookup
00:03:28
Index to overcome Vlookup Problem
00:06:44
What is Hlookup
00:05:30
Example of Hlookup
00:06:22
Vlookup with Approximate
00:09:55
Vlookup with Approximate-Criteria base-Way1
00:05:33
Vlookup with Approximate-Criteria base-Way2
00:05:10
Introduction to INDIRECT
00:10:37
Indirect with Name Range
00:08:28
Indirect for multiple sheets
00:06:32
Example Indirect for multiple sheets
00:08:44
New Function XLOOKUP
00:07:16
Example of Xlookup
00:08:34
Ass 1 - Problem
00:01:23
Ass 1 - Solution
00:03:02
Ass 2 - Problem
00:01:24
Ass 2 - Solution
00:02:48
Ass 3 - Problem
00:02:05
Ass 3 - Solution
00:03:08
Ass 4 - Problem
00:01:29
Ass 4 - Solution
00:04:46
Ass 5 - Problem
00:01:37
Ass 5 - Solution
00:04:23
5
Data Analysis
Intro to Tables
00:05:50
How to create Table
00:08:40
Benefits of Table-part1
00:08:39
Benefits of Table-part2
00:12:56
Simple Sorting
00:07:00
Multilevel Sorting
00:06:00
Custom Sort
00:07:51
Sorting to Rearrange Data
00:06:32
Sorting New Formula in 365
00:05:22
Remove Duplicates
00:06:15
New Function of 365-Unique
00:06:42
Filter in Excel
00:06:43
Filter based on DataType
00:10:03
Advance Filter Setup
00:06:13
Advance Filter for Unique and Rearrangement
00:06:05
Advance filter with AND
00:07:50
Advance filter with OR and Wildcard
00:09:38
Advance Filter Vs Auto Filter
00:06:17
Text to Column with delimit
00:05:03
Text to Column with fixed width
00:07:58
Conditional Formatting (CF) Intro
00;04:43
Conditional Formatting (CF) Highlight Cell Rules
00:09:25
Conditional Formatting (CF) Top/Bottom Rule
00:05:39
Conditional Formatting (CF) DataBar ColorScale iconSet Rule
00:06:20
Conditional Formatting (CF) new Rule
00:07:44
Conditional Formatting (CF) manage Rule
00:09:23
Conditional Formatting (CF) Highlight whole record
00:09:15
Ass 1 - Problem
00:04:10
Ass 1 - Solution
00:09:16
6
Data Visualization
What are Charts
00:07:02
All about Column Chart
00:10:42
What is Bar chart and when to use Bar over Column
00:08:52
When to use Line Chart
00:11;05
Area chart over Line Chart
00:07:48
Pie Chart-Things to know
00:08:27
Column with 2 Legend
00:08:27
Stacked Bar Chart with Dynamic Title
00:05:13
Line and Area with 2 legend
00:08:26
Doughnut Chart-Substitute for Pie with 2 Legend
00:07:50
Combination Chart with 2 y-axis
00:07:50
Tree Map for hierarchical data
00:07:59
Sunbrust for Sub group
00:05:04
Histogram
00:05:11
Intro to Sparkline
00:04:25
Line an Sparkline chart
00;04:25
Sparkline-Column and Winloss
00:11:06
Ass 1 - Problem
00:01:38
Ass 1 - Solution
00:04:03
Ass 2 - Problem
00:00:57
Ass 2 - Solution
00:01:30
Ass 3 - Problem
00:01:38
Ass 3 - Solution
00:02:45
Ass 4 - Problem
00:00:46
Ass 4 - Solution
00:03:14
Ass 5 - Problem
00:01:03
Ass 5 - Solution
00:05:12
7
All about Pivot in Excel
Introduction to PivotTable
00:08:57
The Pivot Area
00:08:46
Building the 1st Pivot
00:07:14
Multilpe fields at Value Part
00:04:51
The Value Field Setting of Pivot
00:06:29
The Column part in Pivot
00:06:57
Applying conditional formatting on Pivot
00:04:54
Category and Sub-Category Data
00:08:37
The layout option for Pivot
00:06:24
Make Multiple Sheets on Click-Filter Pages
00:08:07
Grouping and Ungroupingt the Date data
00:05:34
Number Grouping in Pivot
00:10:53
Group the Text data in Pivot
00:05:51
Creating the Calculated Field
00:07:20
Sorting and Filter the area
00:07:33
Expand/Collapse and Filter with Slicer
00:08:06
Impact of Pivot with and without table
00:09:43
Ass 1 - Problem
00:03:13
Ass 1 - Solution Part 1
00:06:20
Ass 1 - Solution - Part 2
00:04:01
Ass 1 - Solution - Part 3
00:03:48
8
The Data Tab
What-if-Analysis-Goal Seek
00:07:19
Application of Goal Seek
0:09:58
Application of Goal Seek-2
0:08:23
What-if-Analysis-Intro to Scenario manager
0:05:08
Scenarion Manager-Example
0:06:57
Scenarion Manager-Example 2
0:07:31
Intro to Data Table and it's type
0:05:09
Data table 1 Variable
0:07:36
Data table 2 Variables
0:05:43
Loan analysis Example
0:06:14
Loan Analysis-One and Two Variable Data Table
0:09:38
Intro to Solved
0:05:15
Solver Activation and Case study
0:13:25
Solver-An Example
0:07:21
What is Consolidation in Excel
0:04:46
An Example of Consolidation
0:09:35
Data Validation-Introduction
0:04:29
The Setting option for Data Validation
0:05:38
Allow Number/Decimal with custom message
0:09:35
Creating Dependaent List
0:08:50
Date Validations
0:04:06
Protecting the duplicates in range
0:06:09
Writing Custom Formulas
0:07:34
Validating phone numbers
0:03:40
Using Vlookup for Validation
0:07:14
Ass 1 - Problem
00:02:44
Ass 1 - Solution Part 1
00:03:13
Ass 1 - Solution - Part 2
00:03:21
Ass 2 - Problem
00:03:48
Ass 2 - Solution - Part 1
00:03:20
Ass 2 - Solution Part 2
00:04:30
Ass 2 - Solution Part 3
00:10:14
9
Dashbaord-Power Query-Power Pivot
Intro to Dashbaord
00:05:41
Understanding the data set
00:08:18
Understanding Power Query
00:06:25
Importing data for folder in PQ
00:07:41
Understanding the PQ screen
00:06:02
Data Cleaning with Power Query
00:07:46
Appending data with PQ
00:06:21
Merging data with PQ
00:06:18
Detect Datatype and Loding in PQ
00:09:04
Introduction to Power Pivot and Activation Process
00:06:40
Building relatiopnship using PowerPivot
00:08:40
Dashboard-Part1
00:05:03
Dashboard-Part2
00:02:59
Dashboard-Part3
00:03:00
Dashboard-Part4
00:05:46
Dashboard-Part5
00:05:27
Dashboard-Part6
00:09:26
Dashboard-Part7
00:05:03
Dashboard-Part8
00:10:32
Dashboard-Part9
00:09:48
- Greater confidence in Excel usage
- Improved data analysis abilities
- Increased productivity through automation
- Enhanced career opportunities and prospects
Lesson List
1
Getting Started
Resources
The Ribbon
00:06:43Work surface
00:03:57Work Surface
00:03:50The Navigations
00:05:41First File
00:04:51Data Presentable
00:05:34Basic match Operator
00:07:40BODMAS Rule
00:06:45Break data into multiple sheets
00:07:29Worksheet Operations
00:09:39One Drive
00:05:13Autofill the series of Numbers
00:05:50Autofill the series of Dates
00:06:16Autofill the series of Text
00:07:25AutoFile by Keyboard
00:06:58The Fill Series
00:07:10Columns & Rows Manupilation
00:04:46Columns & Rows Manupilation Keyboard
00:04:46Highlight the Data Smartly
00:10:01Cell Formats
00:05:07Cell Formats-Shortcuts
00:02:53Custom Format Ctrl+1
00:07:40Style Operation
00:05:51Own Styles
00:08:17Reusing & manipulating Styles
00:07:17Assignment 1 - Problem
00:02:08Ass 1 - Solution
00:04:06Ass 2 - Problem
00:01:17Ass 2 - Solution
00:05:14Ass 3 - Problem
00:01:27Ass 3 - Solution
00:05:14Ass 4 - Problem
00:01:48Ass 4 - Solution
00:02:422
Essential Formula Knowledge
Function and Formulas Anatomy
00:08:07Parts of Functions
00:10:07Maths Function Part 1
00:08:00Maths Function Part 2
00:07:40Maths Function Part 3
00:09:25Type of Refrence-Part1
00:06:17Type of Refrence-Part2
00:07:10Type of Refrence-Part3
00:09:14Type of Refrence-Part4
00:05:38Type of Refrence-Part5
00:08:06Logical Operators
00:05:49Statical Function-Part1
00:05:28statistical Function-Part2
00:08:00statistical Function-Part3
00:05:02Logical Function-IF (Text)
00:08:41Logical Function-IF (Calculation)
00:04:09Logical Function-AND/OR
00:06:36Logical Function-AND/OR with IF
00:07:46Logical Function-Not with IFError
00:07:05Logical Function-Nested IF
00:08:03Logical Function-IFS
00:07:38Ass 1 - Problem
00:03:06Ass 1 - Solution
00:06:30Ass 2 - Problem
00:01:56Ass 2 - Solution
00:04:40Ass 3 - Problem
00:03:46Ass 3 - Solution
00:10:463
Intermediate Formula Knowledge
Function to change case
00:09:15Combine
00:12:56LEN to check valid data
00:04:22Find & Search
00:06:58Replacing the text
00:09:09Find with Replace implementation
00:08:53Get desire number of characters
00:06:33Introduction to Date category
00:04:52Basic of Date
00:06:50Text function to chage the Date Pattern
00:10:09Edate/EOMOnth
00:05:37Workdays/Networkdays
00:09:56DatedIF-Hidden Formula
00:05:55Conditional Function Intro
00:10:12CountIF to count records based on condition
00:08:23SumIF to add based on condition
00:07:06SumIF make it more dynamic
00:06:16AverageIf
00:04:58COUNTIFS counting on multiple criteria
00:09:44SUMIFS to addup values on multiple criteria
00:07:53NAME range-Way to give name to data
00:08:04Naming rules we should know
00:06:04Name Manager-Quick way to modify
00:09:00Applying Name's in Formuls
00:12:41Ass 1 - Problem
00:03:35Ass 1 - Solution
00:08:17Ass 2 - Problem
00:03:28Ass 2 - Solution
00:09:36Ass 3 - Problem
00:04:01Ass 3 - Solution Part 1
00:03:22Ass 3 - Solution Part 2
00:07:14Ass 3 - Solution Part 3
00:06:134
All about Lookup's In Excel
Introduction to Lookup
00:03:51What is Vlookup
00:07:40Syntax of Vlookup
00:08:18Vlookup Example
00:06:17Match function for Exact search
00:06:03Match with 1,0,-1
00:06:22Vlookup Match-Two Way Lookup
00:06:37Vlookup With Match and Mix Ref
00:06:35Introduction to Double Vlookup
00:06:24An Example of Double Vlookup
00:04:10Introduction to INDEX Function
00:07:19Index with One Match
00:06:35Index with Two Match
00:06:26Problem with Vlookup
00:03:28Index to overcome Vlookup Problem
00:06:44What is Hlookup
00:05:30Example of Hlookup
00:06:22Vlookup with Approximate
00:09:55Vlookup with Approximate-Criteria base-Way1
00:05:33Vlookup with Approximate-Criteria base-Way2
00:05:10Introduction to INDIRECT
00:10:37Indirect with Name Range
00:08:28Indirect for multiple sheets
00:06:32Example Indirect for multiple sheets
00:08:44New Function XLOOKUP
00:07:16Example of Xlookup
00:08:34Ass 1 - Problem
00:01:23Ass 1 - Solution
00:03:02Ass 2 - Problem
00:01:24Ass 2 - Solution
00:02:48Ass 3 - Problem
00:02:05Ass 3 - Solution
00:03:08Ass 4 - Problem
00:01:29Ass 4 - Solution
00:04:46Ass 5 - Problem
00:01:37Ass 5 - Solution
00:04:235
Data Analysis
Intro to Tables
00:05:50How to create Table
00:08:40Benefits of Table-part1
00:08:39Benefits of Table-part2
00:12:56Simple Sorting
00:07:00Multilevel Sorting
00:06:00Custom Sort
00:07:51Sorting to Rearrange Data
00:06:32Sorting New Formula in 365
00:05:22Remove Duplicates
00:06:15New Function of 365-Unique
00:06:42Filter in Excel
00:06:43Filter based on DataType
00:10:03Advance Filter Setup
00:06:13Advance Filter for Unique and Rearrangement
00:06:05Advance filter with AND
00:07:50Advance filter with OR and Wildcard
00:09:38Advance Filter Vs Auto Filter
00:06:17Text to Column with delimit
00:05:03Text to Column with fixed width
00:07:58Conditional Formatting (CF) Intro
00;04:43Conditional Formatting (CF) Highlight Cell Rules
00:09:25Conditional Formatting (CF) Top/Bottom Rule
00:05:39Conditional Formatting (CF) DataBar ColorScale iconSet Rule
00:06:20Conditional Formatting (CF) new Rule
00:07:44Conditional Formatting (CF) manage Rule
00:09:23Conditional Formatting (CF) Highlight whole record
00:09:15Ass 1 - Problem
00:04:10Ass 1 - Solution
00:09:166
Data Visualization
What are Charts
00:07:02All about Column Chart
00:10:42What is Bar chart and when to use Bar over Column
00:08:52When to use Line Chart
00:11;05Area chart over Line Chart
00:07:48Pie Chart-Things to know
00:08:27Column with 2 Legend
00:08:27Stacked Bar Chart with Dynamic Title
00:05:13Line and Area with 2 legend
00:08:26Doughnut Chart-Substitute for Pie with 2 Legend
00:07:50Combination Chart with 2 y-axis
00:07:50Tree Map for hierarchical data
00:07:59Sunbrust for Sub group
00:05:04Histogram
00:05:11Intro to Sparkline
00:04:25Line an Sparkline chart
00;04:25Sparkline-Column and Winloss
00:11:06Ass 1 - Problem
00:01:38Ass 1 - Solution
00:04:03Ass 2 - Problem
00:00:57Ass 2 - Solution
00:01:30Ass 3 - Problem
00:01:38Ass 3 - Solution
00:02:45Ass 4 - Problem
00:00:46Ass 4 - Solution
00:03:14Ass 5 - Problem
00:01:03Ass 5 - Solution
00:05:127
All about Pivot in Excel
Introduction to PivotTable
00:08:57The Pivot Area
00:08:46Building the 1st Pivot
00:07:14Multilpe fields at Value Part
00:04:51The Value Field Setting of Pivot
00:06:29The Column part in Pivot
00:06:57Applying conditional formatting on Pivot
00:04:54Category and Sub-Category Data
00:08:37The layout option for Pivot
00:06:24Make Multiple Sheets on Click-Filter Pages
00:08:07Grouping and Ungroupingt the Date data
00:05:34Number Grouping in Pivot
00:10:53Group the Text data in Pivot
00:05:51Creating the Calculated Field
00:07:20Sorting and Filter the area
00:07:33Expand/Collapse and Filter with Slicer
00:08:06Impact of Pivot with and without table
00:09:43Ass 1 - Problem
00:03:13Ass 1 - Solution Part 1
00:06:20Ass 1 - Solution - Part 2
00:04:01Ass 1 - Solution - Part 3
00:03:488
The Data Tab
What-if-Analysis-Goal Seek
00:07:19Application of Goal Seek
0:09:58Application of Goal Seek-2
0:08:23What-if-Analysis-Intro to Scenario manager
0:05:08Scenarion Manager-Example
0:06:57Scenarion Manager-Example 2
0:07:31Intro to Data Table and it's type
0:05:09Data table 1 Variable
0:07:36Data table 2 Variables
0:05:43Loan analysis Example
0:06:14Loan Analysis-One and Two Variable Data Table
0:09:38Intro to Solved
0:05:15Solver Activation and Case study
0:13:25Solver-An Example
0:07:21What is Consolidation in Excel
0:04:46An Example of Consolidation
0:09:35Data Validation-Introduction
0:04:29The Setting option for Data Validation
0:05:38Allow Number/Decimal with custom message
0:09:35Creating Dependaent List
0:08:50Date Validations
0:04:06Protecting the duplicates in range
0:06:09Writing Custom Formulas
0:07:34Validating phone numbers
0:03:40Using Vlookup for Validation
0:07:14Ass 1 - Problem
00:02:44Ass 1 - Solution Part 1
00:03:13Ass 1 - Solution - Part 2
00:03:21Ass 2 - Problem
00:03:48Ass 2 - Solution - Part 1
00:03:20Ass 2 - Solution Part 2
00:04:30Ass 2 - Solution Part 3
00:10:149
Dashbaord-Power Query-Power Pivot
Intro to Dashbaord
00:05:41Understanding the data set
00:08:18Understanding Power Query
00:06:25Importing data for folder in PQ
00:07:41Understanding the PQ screen
00:06:02Data Cleaning with Power Query
00:07:46Appending data with PQ
00:06:21Merging data with PQ
00:06:18Detect Datatype and Loding in PQ
00:09:04Introduction to Power Pivot and Activation Process
00:06:40Building relatiopnship using PowerPivot
00:08:40Dashboard-Part1
00:05:03Dashboard-Part2
00:02:59Dashboard-Part3
00:03:00Dashboard-Part4
00:05:46Dashboard-Part5
00:05:27Dashboard-Part6
00:09:26Dashboard-Part7
00:05:03Dashboard-Part8
00:10:32Dashboard-Part9
00:09:48More courses like this
Build Dashboard using Excel
By Jobaaj Learnings
₹2,999/-₹4,999/-
6,830 Enrolled Students
