Top 50 Microsoft Excel Formulas with Examples (Beginner to Advanced) – Complete Excel Formula Guide 2026

📘 Microsoft Excel Formulas for Students

Learn Excel Formulas with Real Student Examples, Cell References, and Practical Exercises.

📖 What is an Excel Formula?

An Excel Formula is an equation that performs calculations automatically. Instead of calculating manually, Excel quickly provides accurate results using formulas.

🎯 Why Should Students Learn Excel Formulas?

• Calculate Total Marks
• Find Average Marks
• Create Mark Sheets
• Generate Report Cards
• Prepare Attendance Sheets
• Manage Fees
• Analyze Student Performance

➕ Formula 1 : SUM()

The SUM() function adds multiple numbers together automatically.

📌 Student Marksheet Example

	A	B	C	D	E
1	Student	Maths	Science	English	Total
2	Rahul	80	75	90	=SUM(B2:D2)

📝 Formula

=SUM(B2:D2)

🔍 Formula Breakdown

Cell	Meaning
B2	Maths Marks
C2	Science Marks
D2	English Marks

Calculation

80 + 75 + 90 = 245

💼 Real Life Uses

• Total Marks
• Total Fees
• Total Attendance
• Sports Score
• Monthly Expenses

⌨ Shortcut

Alt + =

💡 Pro Tip

Whenever you see B2:D2, Excel calculates every cell from B2 to D2 automatically.

📊 Formula 2 : AVERAGE()

The AVERAGE() function calculates the average (mean) of selected numbers. It is commonly used to find a student's average marks.

📌 Student Marksheet Example

	A	B	C	D	E
1	Student	Maths	Science	English	Average
2	Rahul	80	75	90	=AVERAGE(B2:D2)

📝 Formula

=AVERAGE(B2:D2)

🔍 Formula Breakdown

Cell	Meaning
B2	Maths Marks
C2	Science Marks
D2	English Marks

Calculation

(80 + 75 + 90) ÷ 3

Average = 81.67

💼 Real Life Uses

• Student Average Marks
• Class Average
• Average Attendance
• Average Monthly Sales
• Average Expenses

❌ Common Mistakes

• Selecting the wrong cell range.
• Including blank cells accidentally.
• Using text values instead of numbers.

📝 Practice Exercise

Student	Maths	Science	English	Average
Neha	90	85	80	?

💡 Pro Tip

Use AVERAGE() instead of calculating manually. It automatically updates whenever marks change, saving time and reducing mistakes.

🏆 Formula 3 : MAX()

The MAX() function returns the largest number from a selected range of cells. It is commonly used to identify the highest marks, highest sales, highest salary, or top performance.

📌 Student Marksheet Example

	A	B	C	D	E
1	Student	Maths	Science	English	Highest Marks
2	Rahul	80	75	90	=MAX(B2:D2)

📝 Formula

=MAX(B2:D2)

🔍 Formula Breakdown

Cell	Subject
B2	Maths = 80
C2	Science = 75
D2	English = 90

Calculation

80, 75, 90

Highest Marks = 90

💼 Real-Life Uses

• Find Highest Marks.
• Identify the Top Scorer.
• Find Maximum Monthly Sales.
• Find Highest Salary.
• Find Best Performance.

❌ Common Mistakes

• Selecting the wrong cell range.
• Including empty cells unnecessarily.
• Typing text instead of numbers.

📝 Practice Exercise

Student	Maths	Science	English	Highest Marks
Neha	88	95	82	?

💡 Pro Tip

Use MAX() to quickly identify the highest value from hundreds or even thousands of records. It is widely used in school reports, business dashboards, and data analysis.

📉 Formula 4 : MIN()

The MIN() function returns the smallest number from a selected range of cells. It is commonly used to identify the lowest marks, minimum sales, lowest salary, or the minimum value in a dataset.

📌 Student Marksheet Example

	A	B	C	D	E
1	Student	Maths	Science	English	Lowest Marks
2	Rahul	80	75	90	=MIN(B2:D2)

📝 Formula

=MIN(B2:D2)

🔍 Formula Breakdown

Cell	Subject
B2	Maths = 80
C2	Science = 75
D2	English = 90

Calculation

80, 75, 90

Lowest Marks = 75

💼 Real-Life Uses

• Find the Lowest Marks.
• Identify the Weakest Subject.
• Find Minimum Sales.
• Find Lowest Salary.
• Analyze the Minimum Value in Reports.

❌ Common Mistakes

• Selecting an incorrect cell range.
• Including unwanted cells.
• Using text values instead of numbers.

📝 Practice Exercise

Student	Maths	Science	English	Lowest Marks
Neha	88	95	82	?

💡 Pro Tip

Use MIN() to quickly identify the lowest score or minimum value in large datasets. Teachers often use it to find the lowest marks in a class, while businesses use it to analyze minimum sales and expenses.

🔢 Formula 5 : COUNT()

The COUNT() function counts only the cells that contain numeric values. It ignores text, blank cells, and special characters. This function is commonly used to count marks, fees, attendance, and other numerical records.

📌 Student Marksheet Example

	A	B	C	D	E
1	Student	Maths	Science	English	No. of Subjects
2	Rahul	80	75	90	=COUNT(B2:D2)

📝 Formula

=COUNT(B2:D2)

🔍 Formula Breakdown

Cell	Value
B2	80 ✅
C2	75 ✅
D2	90 ✅

Calculation

80, 75 and 90 are all numbers.

COUNT Result = 3

💼 Real-Life Uses

• Count the number of subjects.
• Count students with marks entered.
• Count attendance days.
• Count fee payments.
• Count numeric records in reports.

❌ Common Mistakes

• COUNT() does not count text values.
• Blank cells are ignored.
• Names like "Rahul" are not counted.

📝 Practice Exercise

Student	Maths	Science	English	COUNT Result
Neha	88	95	82	?

📚 COUNT() vs COUNTA()

• COUNT() → Counts only numbers.
• COUNTA() → Counts numbers, text, dates, and all non-empty cells.

💡 Pro Tip

Use COUNT() when you only want to count numerical values. If your data contains names, text, or dates, use COUNTA() instead.

📝 Formula 6 : COUNTA()

The COUNTA() function counts all non-empty cells in a selected range. Unlike COUNT(), it counts numbers, text, dates, logical values, and formulas. It ignores only blank cells.

📌 Student Attendance Example

	A	B	C	D	E
1	Student	Maths	Science	English	Filled Cells
2	Rahul	80	75	90	=COUNTA(A2:D2)

📝 Formula

=COUNTA(A2:D2)

🔍 Formula Breakdown

Cell	Value
A2	Rahul ✅ (Text)
B2	80 ✅ (Number)
C2	75 ✅ (Number)
D2	90 ✅ (Number)

Calculation

Rahul + 80 + 75 + 90

COUNTA Result = 4

💼 Real-Life Uses

• Count student records.
• Count filled attendance entries.
• Count completed assignments.
• Count filled registration forms.
• Count non-empty data in reports.

❌ Common Mistakes

• Thinking COUNTA() counts only numbers.
• Forgetting that text values are also counted.
• Assuming blank cells are included.

📝 Practice Exercise

Student	Maths	Science	English	COUNTA Result
Neha	88		82	?

📚 COUNT() vs COUNTA()

Formula	Counts
COUNT()	Only Numbers
COUNTA()	Numbers + Text + Dates + Formulas (All Non-Empty Cells)

💡 Pro Tip

Use COUNTA() when you need to know how many cells contain data, regardless of whether the data is text or numbers. It is widely used in attendance sheets, registration forms, surveys, and student databases.

✅ Formula 7 : IF()

The IF() function checks whether a condition is TRUE or FALSE. Based on the result, Excel returns one value if the condition is true and another value if the condition is false.

📌 Student Pass / Fail Example

	A	B	C
1	Student	Total Marks	Result
2	Rahul	245	=IF(B2>=105,"Pass","Fail")

📝 Formula

=IF(B2>=105,"Pass","Fail")

📖 Syntax

=IF(Logical_Test, Value_if_True, Value_if_False)

🔍 Formula Breakdown

Part	Meaning
B2>=105	Checks whether Total Marks are 105 or more.
"Pass"	Displayed if the condition is TRUE.
"Fail"	Displayed if the condition is FALSE.

Result

Total Marks = 245
245 ≥ 105 ✅

Output = Pass

💼 Real-Life Uses

• Student Pass / Fail
• Grade System
• Bonus Eligibility
• Attendance Status
• Salary Approval
• Stock Available / Out of Stock

❌ Common Mistakes

• Missing quotation marks ("Pass").
• Using incorrect comparison operators.
• Missing commas between arguments.

📝 Practice Exercise

Student	Total Marks	Result
Neha	98	?
Amit	180	?

Try using:

=IF(B2>=105,"Pass","Fail")

💡 Pro Tip

The IF() function is one of the most powerful Excel formulas. It is used in school mark sheets, office reports, payroll systems, dashboards, and business automation. Learning IF() is the first step toward mastering advanced Excel formulas.

🏅 Formula 8 : IFS()

The IFS() function checks multiple conditions and returns the value for the first condition that is TRUE. It is mainly used for grading systems, performance evaluation, and result analysis.

📌 Student Grade Example

	A	B	C
1	Student	Percentage	Grade
2	Rahul	88%	=IFS(B2>=90,"A+",B2>=80,"A",B2>=70,"B",B2>=60,"C",TRUE,"Fail")

📝 Formula

=IFS(B2>=90,"A+",B2>=80,"A",B2>=70,"B",B2>=60,"C",TRUE,"Fail")

📖 Grade Rules

Percentage	Grade
90% and Above	A+
80% - 89%	A
70% - 79%	B
60% - 69%	C
Below 60%	Fail

Example Result

Rahul = 88%

Excel checks: 90? ❌ 80? ✅

Grade = A

💼 Real-Life Uses

• Student Grade System
• Employee Performance Rating
• Bonus Categories
• Sales Performance Levels
• Exam Result Analysis

❌ Common Mistakes

• Writing conditions in the wrong order.
• Forgetting the final TRUE condition.
• Missing commas between arguments.

📝 Practice Exercise

Student	Percentage	Grade
Neha	94%	?
Amit	67%	?

💡 Pro Tip

Use IFS() instead of multiple nested IF() functions when checking several conditions. Your formulas become cleaner, easier to read, and easier to maintain.

🏆 Formula 9 : RANK()

The RANK() function returns the position of a number within a list. It is commonly used to calculate student ranks, sales rankings, employee performance rankings, and competition results.

📌 Student Rank Example

	A	B	C
1	Student	Total Marks	Rank
2	Rahul	245	=RANK(B2,$B$2:$B$6,0)
3	Neha	280	1
4	Amit	210	4
5	Priya	265	2
6	Karan	255	3

📝 Formula

=RANK(B2,$B$2:$B$6,0)

📖 Formula Breakdown

Part	Meaning
B2	Current student's total marks.
$B$2:$B$6	Complete list of total marks.
0	Highest marks receive Rank 1.

Example Result

Highest Marks = 280
Rank = 1

Rahul's Marks = 245
Rank = 5

💼 Real-Life Uses

• Student Rank List
• Top 10 Students
• School Merit List
• Sales Ranking
• Employee Performance Ranking
• Sports Competition Results

❌ Common Mistakes

• Not using $ to lock the range.
• Selecting an incorrect range.
• Using 1 instead of 0 when highest marks should get Rank 1.

📝 Practice Exercise

Student	Total Marks	Rank
Jay	275	?
Riya	260	?

💡 Pro Tip

Always use an absolute reference ($B$2:$B$6) when copying the RANK() formula to other rows. This keeps the comparison range fixed and gives correct rankings for every student.

🔍 Formula 10 : VLOOKUP()

The VLOOKUP() function searches for a value in the first column of a table and returns information from another column in the same row. It is one of the most widely used Excel functions in schools, colleges, offices, banks, hospitals, and businesses.

📌 Student Database Example

	A	B	C	D	E
1	Roll No	Name	Class	Total Marks	Result
2	101	Rahul	10-A	245	Pass
3	102	Neha	10-A	280	Pass

🎯 Search Example

G	H
Roll No	102
Student Name	=VLOOKUP(H2,A2:E3,2,FALSE)

📝 Formula

=VLOOKUP(H2,A2:E3,2,FALSE)

📖 Formula Breakdown

Argument	Meaning
H2	Lookup Value (Roll Number)
A2:E3	Student Database
2	Return Student Name (2nd Column)
FALSE	Exact Match

Result

Roll Number = 102

Excel finds Roll No 102
Returns Student Name = Neha

💼 Real-Life Uses

• Find Student Name using Roll Number.
• Employee Details using Employee ID.
• Product Price using Product Code.
• Customer Information.
• Inventory Lookup.
• Hospital Patient Records.
• Bank Customer Details.

❌ Common Mistakes

• Lookup value must be in the first column.
• Selecting the wrong table range.
• Using TRUE instead of FALSE for exact matching.
• Not locking the table range with $.

📝 Practice Exercise

Create a student database with 10 students. Enter a Roll Number in cell H2 and use VLOOKUP() to display the student's name automatically.

⭐ VLOOKUP vs XLOOKUP

• VLOOKUP() → Works in Excel 2007, 2010, 2013, 2016, 2019, Microsoft 365.
• XLOOKUP() → Available only in Microsoft 365 and Excel 2021+.
• XLOOKUP() is more flexible and powerful than VLOOKUP().

💡 Pro Tip

Always use an absolute table reference, such as $A$2:$E$100, so the lookup range does not change when you copy the formula to other cells.

🚀 Formula 11 : XLOOKUP()

The XLOOKUP() function is the modern replacement for VLOOKUP(). It searches for a value in one column and returns the corresponding value from another column. It can search both left-to-right and right-to-left, making it more flexible and powerful.

📌 Student Database Example

	A	B	C	D
1	Roll No	Student Name	Class	Total Marks
2	101	Rahul	10-A	245
3	102	Neha	10-A	280

🎯 Search Example

F	G
Roll No	102
Student Name	=XLOOKUP(G2,A2:A3,B2:B3)

📝 Formula

=XLOOKUP(G2,A2:A3,B2:B3)

📖 Formula Breakdown

Argument	Meaning
G2	Lookup Value (Roll Number)
A2:A3	Lookup Array
B2:B3	Return Array (Student Name)

Result

Roll Number = 102

Student Name = Neha

💼 Real-Life Uses

• Find Student Name using Roll Number.
• Find Marks using Student ID.
• Employee Lookup.
• Invoice Search.
• Inventory Management.
• Hospital Patient Records.
• Bank Customer Search.

❌ Common Mistakes

• Lookup Array and Return Array must have the same number of rows.
• XLOOKUP() is not available in older Excel versions (Excel 2019 and earlier).
• Selecting incorrect lookup ranges.

⭐ VLOOKUP() vs XLOOKUP()

Feature	VLOOKUP	XLOOKUP
Search Direction	Left → Right Only	Both Directions
Default Match	Needs FALSE	Exact Match by Default
Ease of Use	Good	Excellent

💡 Pro Tip

If you use Microsoft 365 or Excel 2021+, prefer XLOOKUP() over VLOOKUP(). It is easier to write, more reliable, and much more flexible for real-world Excel projects.

📊 Formula 12 : COUNTIF()

The COUNTIF() function counts the number of cells that meet a specific condition. It is one of the most useful formulas for students, teachers, and office users because it helps analyze data quickly.

📌 Student Result Example

	A	B	C
1	Student	Total Marks	Result
2	Rahul	245	Pass
3	Neha	280	Pass
4	Amit	95	Fail
5	Priya	210	Pass

📝 Formula

=COUNTIF(C2:C5,"Pass")

📖 Formula Breakdown

Argument	Meaning
C2:C5	Result Column
"Pass"	Condition to Count

Calculation

Pass
Pass
Fail
Pass

Total Pass Students = 3

📌 More COUNTIF Examples

Formula	Purpose
=COUNTIF(C2:C5,"Pass")	Count Pass Students
=COUNTIF(C2:C5,"Fail")	Count Fail Students
=COUNTIF(B2:B5,">=200")	Students Scoring 200 or More
=COUNTIF(B2:B5,"<100")	Students Below 100 Marks

💼 Real-Life Uses

• Count Pass / Fail Students.
• Students scoring above a target.
• Attendance Count.
• Count Paid Fees.
• Inventory Stock Count.
• Sales Target Achievement.

❌ Common Mistakes

• Using the wrong cell range.
• Typing incorrect text (e.g., "pass" instead of "Pass").
• Forgetting quotation marks around text conditions.

📝 Practice Exercise

Using the table above, answer these questions:

• How many students passed?
• How many students failed?
• How many students scored 200 or more?
• How many students scored below 100?

⭐ COUNT() vs COUNTIF()

Formula	Purpose
COUNT()	Counts Numeric Cells Only
COUNTIF()	Counts Cells Matching a Condition

💡 Pro Tip

COUNTIF() is one of the most frequently asked formulas in Excel interviews. Mastering it will help you build attendance sheets, result systems, dashboards, and reports with ease.

📊 Formula 13 : COUNTIFS()

The COUNTIFS() function counts the number of cells that satisfy two or more conditions. It is an advanced version of COUNTIF() and is widely used in school reports, attendance systems, HR reports, and business dashboards.

📌 Student Result Example

	A	B	C	D
1	Student	Class	Total Marks	Result
2	Rahul	10-A	245	Pass
3	Neha	10-A	280	Pass
4	Amit	10-B	95	Fail
5	Priya	10-A	210	Pass

📝 Formula

=COUNTIFS(B2:B5,"10-A",D2:D5,"Pass")

📖 Formula Breakdown

Argument	Meaning
B2:B5	Class Range
"10-A"	First Condition
D2:D5	Result Range
"Pass"	Second Condition

Calculation

✔ Class = 10-A
✔ Result = Pass

Matching Students: Rahul ✅ Neha ✅ Priya ✅

Total Result = 3 Students

💼 Real-Life Uses

• Count Pass Students in Class 10-A.
• Count Girls who Passed.
• Count Students Scoring Above 200 Marks.
• Count Present Students in a Specific Class.
• Count Employees in a Particular Department.
• Count Products Available in a Category.

📌 More COUNTIFS Examples

Formula	Purpose
=COUNTIFS(B2:B5,"10-A",D2:D5,"Pass")	Pass Students in Class 10-A
=COUNTIFS(B2:B5,"10-B",D2:D5,"Fail")	Fail Students in Class 10-B
=COUNTIFS(C2:C5,">=200",D2:D5,"Pass")	Students with 200+ Marks and Pass

❌ Common Mistakes

• Criteria ranges must have the same number of rows.
• Typing the wrong text condition.
• Using different-sized ranges.
• Forgetting quotation marks around text.

📝 Practice Exercise

Using the table above, answer these questions:

• How many students from 10-A passed?
• How many students from 10-B failed?
• How many students scored 200 or more and passed?

⭐ COUNTIF() vs COUNTIFS()

Formula	Conditions Supported
COUNTIF()	One Condition
COUNTIFS()	Multiple Conditions

💡 Pro Tip

Use COUNTIFS() whenever your report requires multiple conditions. It is one of the most valuable formulas for dashboards, school reports, HR reports, and business analytics.

➕ Formula 14 : SUMIF()

The SUMIF() function adds values that match a single condition. It is widely used to calculate total marks, total fees, total sales, and total salary based on a specific condition.

📌 Student Marks Example

	A	B	C
1	Student	Class	Total Marks
2	Rahul	10-A	245
3	Neha	10-A	280
4	Amit	10-B	190
5	Priya	10-A	210

📝 Formula

=SUMIF(B2:B5,"10-A",C2:C5)

📖 Formula Breakdown

Argument	Meaning
B2:B5	Criteria Range (Class)
"10-A"	Condition
C2:C5	Values to Add

Calculation

Rahul = 245
Neha = 280
Priya = 210

Total = 735 Marks

💼 Real-Life Uses

• Total Marks for Class 10-A.
• Total Fees Paid by One Class.
• Total Sales by One Employee.
• Total Salary by Department.
• Total Products in One Category.
• Total Expenses by Month.

📌 More SUMIF() Examples

Formula	Purpose
=SUMIF(B2:B5,"10-A",C2:C5)	Total Marks of Class 10-A
=SUMIF(C2:C5,">=200")	Add Marks Greater Than 200
=SUMIF(B2:B5,"10-B",C2:C5)	Total Marks of Class 10-B

❌ Common Mistakes

• Criteria Range and Sum Range should have the same number of rows.
• Typing incorrect text conditions.
• Forgetting quotation marks around text.
• Selecting the wrong Sum Range.

📝 Practice Exercise

Using the table above, calculate:

• Total Marks of Class 10-A.
• Total Marks of Class 10-B.
• Total Marks greater than 200.

⭐ SUM() vs SUMIF()

Formula	Purpose
SUM()	Adds All Numbers
SUMIF()	Adds Numbers Matching One Condition

💡 Pro Tip

Use SUMIF() whenever you need totals based on a single condition. It is commonly used in student reports, fee management systems, sales reports, payroll, and inventory tracking.

📊 Formula 15 : SUMIFS()

The SUMIFS() function adds numbers that meet multiple conditions. It is an advanced version of SUMIF() and is commonly used in student reports, payroll, sales analysis, and business dashboards.

📌 Student Marksheet Example

	A	B	C	D
1	Student	Class	Total Marks	Result
2	Rahul	10-A	245	Pass
3	Neha	10-A	280	Pass
4	Amit	10-B	190	Fail
5	Priya	10-A	210	Pass

📝 Formula

=SUMIFS(C2:C5,B2:B5,"10-A",D2:D5,"Pass")

📖 Formula Breakdown

Argument	Meaning
C2:C5	Sum Range (Marks)
B2:B5	Class Range
"10-A"	Condition 1
D2:D5	Result Range
"Pass"	Condition 2

Calculation

Rahul = 245 ✅
Neha = 280 ✅
Priya = 210 ✅
Amit = Not Included ❌

Total = 735 Marks

💼 Real-Life Uses

• Total Marks of Pass Students in Class 10-A.
• Total Fees Paid by One Class.
• Total Sales by One Employee in One Month.
• Total Salary of One Department.
• Total Products in One Category.
• Business Reports with Multiple Conditions.

📌 More SUMIFS() Examples

Formula	Purpose
=SUMIFS(C2:C5,B2:B5,"10-A",D2:D5,"Pass")	Marks of Pass Students in 10-A
=SUMIFS(C2:C5,B2:B5,"10-B",D2:D5,"Fail")	Marks of Fail Students in 10-B
=SUMIFS(C2:C5,D2:D5,"Pass")	Total Marks of All Pass Students

❌ Common Mistakes

• All ranges must have the same size.
• Typing incorrect criteria.
• Forgetting quotation marks around text.
• Using the wrong Sum Range.

📝 Practice Exercise

Using the table above, calculate:

• Total Marks of Pass Students in 10-A.
• Total Marks of Fail Students in 10-B.
• Total Marks of all Pass Students.

⭐ SUMIF() vs SUMIFS()

Formula	Conditions
SUMIF()	One Condition
SUMIFS()	Multiple Conditions

💡 Pro Tip

SUMIFS() is one of the most powerful reporting formulas in Excel. It is heavily used in school management systems, accounting, HR, sales reports, dashboards, and MIS reporting where totals are required based on multiple conditions.

📊 Formula 16 : AVERAGEIF()

The AVERAGEIF() function calculates the average of numbers that meet a single condition. It is commonly used in schools, colleges, offices, and businesses to analyze marks, attendance, sales, and salaries.

📌 Student Average Marks Example

	A	B	C
1	Student	Class	Total Marks
2	Rahul	10-A	245
3	Neha	10-A	280
4	Amit	10-B	190
5	Priya	10-A	210

📝 Formula

=AVERAGEIF(B2:B5,"10-A",C2:C5)

📖 Formula Breakdown

Argument	Meaning
B2:B5	Criteria Range (Class)
"10-A"	Condition
C2:C5	Average Range (Marks)

Calculation

Rahul = 245
Neha = 280
Priya = 210

Average = (245 + 280 + 210) ÷ 3

Average Marks = 245

💼 Real-Life Uses

• Average Marks of Class 10-A.
• Average Marks of Pass Students.
• Average Monthly Sales.
• Average Salary by Department.
• Average Attendance.
• Average Product Rating.

📌 More AVERAGEIF() Examples

Formula	Purpose
=AVERAGEIF(B2:B5,"10-A",C2:C5)	Average Marks of Class 10-A
=AVERAGEIF(C2:C5,">=200")	Average of Marks Greater Than 200
=AVERAGEIF(B2:B5,"10-B",C2:C5)	Average Marks of Class 10-B

❌ Common Mistakes

• Using different-sized ranges.
• Typing the wrong condition.
• Forgetting quotation marks around text.
• Selecting the wrong Average Range.

📝 Practice Exercise

Using the table above, calculate:

• Average Marks of Class 10-A.
• Average Marks of Class 10-B.
• Average of students scoring more than 200 marks.

⭐ AVERAGE() vs AVERAGEIF()

Formula	Purpose
AVERAGE()	Average of All Numbers
AVERAGEIF()	Average Based on One Condition

💡 Pro Tip

AVERAGEIF() is extremely useful for creating student reports, class-wise analysis, department-wise reports, and business dashboards. It helps you calculate meaningful averages without filtering data manually.

📊 Formula 17 : AVERAGEIFS()

The AVERAGEIFS() function calculates the average of numbers that satisfy multiple conditions. It is an advanced version of AVERAGEIF() and is widely used in school reports, HR reports, dashboards, and business analytics.

📌 Student Report Example

	A	B	C	D
1	Student	Class	Total Marks	Result
2	Rahul	10-A	245	Pass
3	Neha	10-A	280	Pass
4	Amit	10-B	190	Fail
5	Priya	10-A	210	Pass

📝 Formula

=AVERAGEIFS(C2:C5,B2:B5,"10-A",D2:D5,"Pass")

📖 Formula Breakdown

Argument	Meaning
C2:C5	Average Range (Marks)
B2:B5	Class Range
"10-A"	Condition 1
D2:D5	Result Range
"Pass"	Condition 2

Calculation

Rahul = 245
Neha = 280
Priya = 210

Average = (245 + 280 + 210) ÷ 3

Average = 245

💼 Real-Life Uses

• Average Marks of Pass Students in Class 10-A.
• Average Salary by Department.
• Average Sales by Region.
• Average Attendance of One Class.
• Business Dashboard Analysis.
• School Performance Reports.

❌ Common Mistakes

• Using different-sized ranges.
• Typing incorrect conditions.
• Forgetting quotation marks.
• Selecting the wrong Average Range.

📝 Practice Exercise

• Find the average marks of Pass Students in Class 10-A.
• Find the average marks of Fail Students in Class 10-B.
• Try changing the class name and observe the result.

⭐ AVERAGEIF() vs AVERAGEIFS()

Formula	Conditions
AVERAGEIF()	One Condition
AVERAGEIFS()	Multiple Conditions

💡 Pro Tip

AVERAGEIFS() is one of the most useful formulas for creating professional Excel dashboards, MIS reports, KPI reports, school management systems, and business analytics where multiple conditions are required.

📍 Formula 18 : INDEX()

The INDEX() function returns the value from a specific row and column in a selected range. It is one of the most powerful lookup functions in Excel and is commonly used with MATCH().

📌 Student Database Example

	A	B	C
1	Student	Maths	Science
2	Rahul	80	75
3	Neha	90	88
4	Amit	70	65

📝 Formula

=INDEX(B2:B4,2)

📖 Formula Breakdown

Argument	Meaning
B2:B4	Maths Marks Range
2	Return 2nd Value

Result

Maths Marks List 80 90 70

2nd Value = 90

💼 Real-Life Uses

• Student Marks Lookup.
• Employee Salary Lookup.
• Product Price Lookup.
• Attendance Reports.
• Dynamic Dashboards.
• MIS Reporting.

❌ Common Mistakes

• Using an invalid row number.
• Selecting the wrong range.
• Forgetting that INDEX starts counting from 1.

📝 Practice Exercise

Use the formula below to return the 3rd student's Maths marks.

=INDEX(B2:B4,3)

⭐ Why Use INDEX()?

• Very Fast.
• Works with MATCH().
• Can replace VLOOKUP().
• Works with large datasets.

💡 Pro Tip

INDEX() becomes extremely powerful when combined with MATCH(). Together they create one of the most advanced lookup solutions in Excel, offering more flexibility than VLOOKUP().

🎯 Formula 19 : MATCH()

The MATCH() function searches for a value in a range and returns its position (row number). It is mainly used with INDEX() to create powerful lookup formulas.

📌 Student Database Example

	A	B
1	Student Name	Maths Marks
2	Rahul	80
3	Neha	90
4	Amit	70
5	Priya	85

📝 Formula

=MATCH("Neha",A2:A5,0)

📖 Formula Breakdown

Argument	Meaning
"Neha"	Value to Search
A2:A5	Search Range
0	Exact Match

Calculation

1 → Rahul
2 → Neha ✅
3 → Amit
4 → Priya

Result = 2

💼 Real-Life Uses

• Find Student Position.
• Search Employee ID.
• Locate Product Code.
• Inventory Search.
• Create Dynamic Dashboards.
• Works with INDEX().

❌ Common Mistakes

• Using 1 instead of 0 for exact matching.
• Searching outside the selected range.
• Typing incorrect text or spelling.

📝 Practice Exercise

Find the position of Priya using:

=MATCH("Priya",A2:A5,0)

⭐ MATCH() Match Types

Value	Meaning
0	Exact Match (Most Used)
1	Less Than (Ascending Order)
-1	Greater Than (Descending Order)

💡 Pro Tip

MATCH() only returns the position of a value, not the value itself. Combine it with INDEX() to build powerful and flexible lookup formulas that can replace VLOOKUP() in many situations.

🚀 Formula 20 : INDEX() + MATCH()

The INDEX() + MATCH() combination is one of the most powerful lookup techniques in Excel. It searches for a value using MATCH() and returns the corresponding result using INDEX(). It is faster, more flexible, and more reliable than VLOOKUP().

📌 Student Database Example

	A	B	C	D
1	Roll No	Student	Class	Total Marks
2	101	Rahul	10-A	245
3	102	Neha	10-A	280
4	103	Amit	10-B	190

🎯 Search Example

F	G
Roll No	102
Student Name	=INDEX(B2:B4,MATCH(G2,A2:A4,0))

📝 Formula

=INDEX(B2:B4,MATCH(G2,A2:A4,0))

📖 Formula Breakdown

Function	Purpose
MATCH(G2,A2:A4,0)	Finds the position of Roll No 102.
INDEX(B2:B4,...)	Returns the Student Name from that position.

Calculation

MATCH(102) → Position = 2

INDEX(B2:B4,2) → Neha

Final Result = Neha

💼 Real-Life Uses

• Student Record Lookup.
• Employee Information Search.
• Product Price Finder.
• Inventory Management.
• HR & Payroll Systems.
• Professional MIS Reports.
• Business Dashboards.

❌ Common Mistakes

• INDEX and MATCH ranges must have the same number of rows.
• Using incorrect lookup values.
• Forgetting to use 0 for an exact match.
• Selecting the wrong return range.

📝 Practice Exercise

Find the total marks of Roll No 103 using:

=INDEX(D2:D4,MATCH(103,A2:A4,0))

⭐ VLOOKUP() vs INDEX()+MATCH()

Feature	VLOOKUP()	INDEX()+MATCH()
Lookup Direction	Left → Right	Any Direction
Performance	Good	Excellent
Flexibility	Limited	Very High

💡 Pro Tip

INDEX()+MATCH() is one of the most important Excel interview formulas. Most advanced Excel users prefer it over VLOOKUP() because it is faster, more flexible, and continues to work even if columns are inserted or moved.

🔍 Formula 21 : FILTER()

The FILTER() function returns all rows that match one or more conditions. Unlike Auto Filter, it creates a dynamic filtered list that updates automatically whenever the source data changes.

📌 Student Database Example

	A	B	C	D
1	Student	Class	Total Marks	Result
2	Rahul	10-A	245	Pass
3	Neha	10-A	280	Pass
4	Amit	10-B	190	Fail
5	Priya	10-A	210	Pass

📝 Formula

=FILTER(A2:D5,D2:D5="Pass","No Data")

📖 Formula Breakdown

Argument	Meaning
A2:D5	Data to Return
D2:D5="Pass"	Filter Condition
"No Data"	Displayed if no record is found

Result

✔ Rahul
✔ Neha
✔ Priya

Only Pass Students are displayed automatically.

💼 Real-Life Uses

• Display Pass Students only.
• Show Class 10-A students.
• Filter Sales Reports.
• Display Pending Orders.
• Create Dynamic Dashboards.
• Generate HR Reports.

📌 More FILTER() Examples

Formula	Purpose
=FILTER(A2:D5,D2:D5="Pass")	Show Pass Students
=FILTER(A2:D5,B2:B5="10-A")	Show Class 10-A Students
=FILTER(A2:D5,C2:C5>=250)	Show Students with 250+ Marks

❌ Common Mistakes

• Using different-sized ranges.
• Applying the condition to the wrong column.
• FILTER() works only in Excel 365 and Excel 2021+.
• Not providing an "if_empty" message.

📝 Practice Exercise

• Display only Pass Students.
• Display only Class 10-A students.
• Display students with marks above 250.

⭐ FILTER() vs Auto Filter

Feature	Auto Filter	FILTER()
Updates Automatically	No	Yes
Formula Based	No	Yes
Dynamic Results	Limited	Excellent

💡 Pro Tip

FILTER() is one of the most powerful Dynamic Array formulas in modern Excel. It automatically updates results when your data changes, making it perfect for dashboards, reports, and professional Excel projects.

🔃 Formula 22 : SORT()

The SORT() function automatically sorts data in ascending or descending order. Unlike the manual Sort command, it creates a dynamic sorted list that updates automatically whenever the source data changes.

📌 Student Marks Example

	A	B	C
1	Student	Class	Total Marks
2	Rahul	10-A	245
3	Neha	10-A	280
4	Amit	10-B	190
5	Priya	210	210

📝 Formula (Highest to Lowest)

=SORT(A2:C5,3,-1)

📝 Formula (Lowest to Highest)

=SORT(A2:C5,3,1)

📖 Formula Breakdown

Argument	Meaning
A2:C5	Data Range
3	Sort by 3rd Column (Marks)
-1	Highest → Lowest
1	Lowest → Highest

Result (Highest → Lowest)

Neha → 280
Rahul → 245
Priya → 210
Amit → 190

💼 Real-Life Uses

• Create Merit Lists.
• Sort Highest Marks.
• Sort Lowest Marks.
• Sort Sales Reports.
• Sort Salary Reports.
• Create Dynamic Dashboards.

❌ Common Mistakes

• Using an incorrect column number.
• Selecting an incomplete range.
• SORT() works only in Excel 365 and Excel 2021+.

📝 Practice Exercise

• Sort students from Highest Marks to Lowest Marks.
• Sort students alphabetically by Name.
• Sort students by Class.

⭐ Sort Order

Order	Meaning
1	Ascending (A→Z / Lowest→Highest)
-1	Descending (Z→A / Highest→Lowest)

💡 Pro Tip

SORT() automatically updates whenever your source data changes. It is widely used in modern Excel dashboards, reports, and ranking systems.

🆕 Formula 23 : UNIQUE()

The UNIQUE() function returns only the unique (non-duplicate) values from a range. It automatically removes duplicate entries and creates a clean list. It is very useful for reports, dashboards, and data analysis.

📌 Student Class Example

	A	B
1	Student	Class
2	Rahul	10-A
3	Neha	10-A
4	Amit	10-B
5	Priya	10-A
6	Karan	10-B

📝 Formula

=UNIQUE(B2:B6)

📖 Formula Breakdown

Argument	Meaning
B2:B6	Range containing duplicate values.

Result

10-A
10-B

Duplicate values are removed automatically.

💼 Real-Life Uses

• Create a Unique Class List.
• Create a Product Category List.
• Show Unique Employee Departments.
• Create Customer Lists.
• Dashboard Filters.
• Data Cleaning.

📌 More UNIQUE() Examples

Formula	Purpose
=UNIQUE(A2:A20)	Unique Student Names
=UNIQUE(B2:B100)	Unique Classes
=UNIQUE(C2:C200)	Unique Product Names

❌ Common Mistakes

• UNIQUE() is available only in Excel 365 and Excel 2021+.
• Selecting the wrong data range.
• Expecting the original data to change (UNIQUE() creates a new list only).

📝 Practice Exercise

• Create a list of unique student names.
• Create a list of unique classes.
• Create a list of unique subjects.

⭐ UNIQUE() vs Remove Duplicates

Feature	Remove Duplicates	UNIQUE()
Updates Automatically	No	Yes
Original Data	Modified	Unchanged
Dynamic List	No	Yes

💡 Pro Tip

Use UNIQUE() together with SORT() to create an automatically updated, alphabetically sorted unique list:

=SORT(UNIQUE(B2:B100))

📝 Formula 24 : TEXT()

The TEXT() function converts numbers, dates, and times into a custom text format. It is widely used in invoices, reports, dashboards, salary sheets, and student management systems.

📌 Student Result Example

	A	B	C
1	Student	Result Date	Formatted Date
2	Rahul	15/07/2026	=TEXT(B2,"dd-mmm-yyyy")

📝 Formula

=TEXT(B2,"dd-mmm-yyyy")

📖 Formula Breakdown

Argument	Meaning
B2	Date / Number Cell
"dd-mmm-yyyy"	Required Display Format

Result

Original Date → 15/07/2026

Formatted Date → 15-Jul-2026

📌 More TEXT() Examples

Formula	Output
=TEXT(A1,"dd/mm/yyyy")	15/07/2026
=TEXT(A1,"dd-mmm-yyyy")	15-Jul-2026
=TEXT(A1,"dddd")	Wednesday
=TEXT(A1,"mmmm")	July
=TEXT(A1,"hh:mm AM/PM")	09:30 AM
=TEXT(A1,"₹#,##0")	₹12,500
=TEXT(A1,"0%")	85%

💼 Real-Life Uses

• Format Result Dates.
• Create Professional Invoices.
• Display Currency.
• Create Salary Reports.
• Dashboard Labels.
• Attendance Reports.

❌ Common Mistakes

• Forgetting quotation marks around the format.
• Using an incorrect date format.
• TEXT() returns text, not a numeric value.

📝 Practice Exercise

• Convert a date into 15-Jul-2026.
• Display a number as ₹12,500.
• Display time as 09:30 AM.
• Display a percentage like 85%.

⭐ Common TEXT() Formats

Format	Example
dd/mm/yyyy	15/07/2026
dd-mmm-yyyy	15-Jul-2026
₹#,##0	₹12,500
hh:mm AM/PM	09:30 AM

💡 Pro Tip

Use TEXT() when you want to display dates, times, currency, or percentages in a professional format without changing the original value. It is one of the most useful formulas for dashboards, invoices, certificates, and reports.

⬅️ Formula 25 : LEFT()

The LEFT() function returns the specified number of characters from the left side of a text string. It is commonly used to extract prefixes, state codes, employee IDs, roll numbers, and product codes.

📌 Student ID Example

	A	B
1	Student ID	State Code
2	GJ-2026-001	=LEFT(A2,2)
3	MH-2026-025
4	RJ-2026-118

📝 Formula

=LEFT(A2,2)

📖 Formula Breakdown

Argument	Meaning
A2	Cell containing text
2	Return first 2 characters

Result

GJ-2026-001 → GJ
MH-2026-025 → MH
RJ-2026-118 → RJ

📌 More LEFT() Examples

Formula	Output
=LEFT("EXCEL",2)	EX
=LEFT("COMPUTER",4)	COMP
=LEFT("987654",3)	987

💼 Real-Life Uses

• Extract State Codes.
• Get Roll Number Prefix.
• Read Product Codes.
• Extract Employee ID Prefix.
• Clean Imported Data.
• Create Reports and Dashboards.

❌ Common Mistakes

• Using the wrong number of characters.
• Applying LEFT() to the wrong cell.
• Forgetting that spaces are also counted as characters.

📝 Practice Exercise

• Extract the first 3 letters from COMPUTER.
• Extract the first 2 digits from 987654.
• Extract the first 4 letters from MICROSOFT.

⭐ Related Text Functions

Function	Purpose
LEFT()	Extract characters from the left.
RIGHT()	Extract characters from the right.
MID()	Extract characters from the middle.

💡 Pro Tip

Combine LEFT() with RIGHT(), MID(), LEN(), and FIND() to split complex IDs, invoice numbers, product codes, and employee records automatically.

➡️ Formula 26 : RIGHT()

The RIGHT() function returns the specified number of characters from the right side of a text string. It is commonly used to extract serial numbers, roll numbers, invoice numbers, and product codes.

📌 Student ID Example

	A	B
1	Student ID	Roll No
2	GJ-2026-001	=RIGHT(A2,3)
3	MH-2026-025
4	RJ-2026-118

📝 Formula

=RIGHT(A2,3)

📖 Formula Breakdown

Argument	Meaning
A2	Cell containing text
3	Return last 3 characters

Result

GJ-2026-001 → 001
MH-2026-025 → 025
RJ-2026-118 → 118

📌 More RIGHT() Examples

Formula	Output
=RIGHT("EXCEL",2)	EL
=RIGHT("COMPUTER",4)	UTER
=RIGHT("987654",3)	654

💼 Real-Life Uses

• Extract Roll Numbers.
• Get Invoice Numbers.
• Read Product Codes.
• Extract Employee IDs.
• Split Imported Data.
• Create Dynamic Reports.

❌ Common Mistakes

• Using the wrong number of characters.
• Applying RIGHT() to the wrong cell.
• Spaces are also counted as characters.

📝 Practice Exercise

• Extract the last 3 digits from 987654.
• Extract the last 4 letters from MICROSOFT.
• Extract the last 2 letters from EXCEL.

⭐ LEFT() vs RIGHT()

Function	Purpose
LEFT()	Extract characters from the Left.
RIGHT()	Extract characters from the Right.

💡 Pro Tip

Combine RIGHT() with LEFT(), MID(), LEN(), and FIND() to split IDs, invoice numbers, product codes, mobile numbers, and other structured text automatically.

🎯 Formula 27 : MID()

The MID() function extracts a specific number of characters from the middle of a text string. It is commonly used to extract years, department codes, product categories, and employee information.

📌 Student ID Example

	A	B
1	Student ID	Admission Year
2	GJ-2026-001	=MID(A2,4,4)
3	MH-2025-025
4	RJ-2024-118

📝 Formula

=MID(A2,4,4)

📖 Formula Breakdown

Argument	Meaning
A2	Cell containing text
4	Start from 4th character
4	Extract 4 characters

Result

GJ-2026-001 → 2026
MH-2025-025 → 2025
RJ-2024-118 → 2024

📌 More MID() Examples

Formula	Output
=MID("EXCEL",2,3)	XCE
=MID("MICROSOFT",6,3)	SOF
=MID("987654321",4,3)	654

💼 Real-Life Uses

• Extract Admission Year.
• Extract Department Codes.
• Read Product Categories.
• Split Employee IDs.
• Clean Imported Data.
• Create Dynamic Reports.

❌ Common Mistakes

• Using the wrong starting position.
• Using the wrong number of characters.
• Counting spaces and symbols incorrectly.
• Selecting the wrong cell reference.

📝 Practice Exercise

• Extract 2026 from GJ-2026-001.
• Extract 654 from 987654321.
• Extract SOF from MICROSOFT.

⭐ LEFT() vs MID() vs RIGHT()

Function	Purpose
LEFT()	Extract from Left
MID()	Extract from Middle
RIGHT()	Extract from Right

💡 Pro Tip

MID() becomes much more powerful when combined with FIND(), SEARCH(), LEFT(), RIGHT(), and LEN(). This combination is widely used for advanced text extraction and data cleaning in professional Excel projects.

📏 Formula 28 : LEN()

The LEN() function returns the total number of characters in a text string. It counts letters, numbers, spaces, and special characters. It is widely used for data validation, text cleaning, and checking input length.

📌 Student Name Example

	A	B
1	Student Name	Total Characters
2	Rahul	=LEN(A2)
3	Neha Patel
4	Amit123

📝 Formula

=LEN(A2)

📖 Formula Breakdown

Argument	Meaning
A2	Text to Count

Result

Rahul → 5 Characters
Neha Patel → 11 Characters
Amit123 → 7 Characters

📌 More LEN() Examples

Formula	Output
=LEN("Excel")	5
=LEN("Microsoft Excel")	17
=LEN("987654321")	9

💼 Real-Life Uses

• Validate Mobile Numbers.
• Check Aadhaar Number Length.
• Verify Employee ID.
• Clean Imported Data.
• Password Length Check.
• Text Validation in Forms.

❌ Common Mistakes

• Spaces are counted as characters.
• Numbers are also counted.
• Special symbols are included in the count.
• Extra spaces can increase the result.

📝 Practice Exercise

• Count characters in Microsoft.
• Count characters in Excel Formula.
• Count characters in 9876543210.

⭐ LEN() Examples

Text	LEN Result
Rahul	5
Neha Patel	11
1234567890	10

💡 Pro Tip

LEN() is commonly combined with LEFT(), RIGHT(), MID(), TRIM(), and FIND() for advanced text processing, data validation, and cleaning large datasets.

🧹 Formula 29 : TRIM()

The TRIM() function removes extra spaces from text. It keeps only a single space between words and removes leading and trailing spaces. It is one of the most useful formulas for data cleaning.

📌 Student Name Example

	A	B
1	Original Text	Clean Text
2	Rahul  Patel	=TRIM(A2)
3	Neha   Shah
4	Amit  Kumar

📝 Formula

=TRIM(A2)

📖 Formula Breakdown

Argument	Meaning
A2	Text containing extra spaces

Result

❌ " Rahul Patel "

✅ "Rahul Patel"

All extra spaces are removed automatically.

📌 More TRIM() Examples

Formula	Output
=TRIM(" Excel ")	Excel
=TRIM(" Microsoft Excel ")	Microsoft Excel
=TRIM(" Rahul Patel ")	Rahul Patel

💼 Real-Life Uses

• Clean Imported Excel Data.
• Remove Extra Spaces from Names.
• Prepare Data for VLOOKUP() and XLOOKUP().
• Clean Employee Records.
• Fix Product Lists.
• Prepare Data for Dashboards.

❌ Common Mistakes

• TRIM() removes only extra normal spaces.
• It does not remove line breaks or special non-breaking spaces.
• Using TRIM() on the wrong cell reference.

📝 Practice Exercise

• Remove extra spaces from student names.
• Clean imported customer names.
• Use TRIM() before applying VLOOKUP().

⭐ Before vs After

Before	After TRIM()
" Rahul Patel "	"Rahul Patel"
" Excel "	"Excel"
" Microsoft Excel "	"Microsoft Excel"

💡 Pro Tip

Use TRIM() with CLEAN(), LEFT(), RIGHT(), MID(), and LEN() to clean messy imported data from websites, PDFs, ERP software, or CSV files before analysis.

🔗 Formula 30 : CONCAT()

The CONCAT() function joins two or more text values into a single text string. It is commonly used to combine names, addresses, IDs, invoice numbers, and other text values.

📌 Student Name Example

	A	B	C
1	First Name	Last Name	Full Name
2	Rahul	Patel	=CONCAT(A2," ",B2)
3	Neha	Shah
4	Amit	Kumar

📝 Formula

=CONCAT(A2," ",B2)

📖 Formula Breakdown

Argument	Meaning
A2	First Name
" "	Adds One Space
B2	Last Name

Result

Rahul + Patel

Rahul Patel

📌 More CONCAT() Examples

Formula	Output
=CONCAT("Excel"," Formula")	Excel Formula
=CONCAT("INV-",1001)	INV-1001
=CONCAT(A2," - ",B2)	Rahul - Patel

💼 Real-Life Uses

• Create Full Names.
• Create Invoice Numbers.
• Combine City + State.
• Create Employee IDs.
• Merge Product Codes.
• Prepare Reports.

❌ Common Mistakes

• Forgetting to add spaces between words.
• Using incorrect cell references.
• Using CONCAT() instead of TEXTJOIN() when a separator is needed for many cells.

📝 Practice Exercise

• Combine First Name and Last Name.
• Create Invoice Number like INV-1001.
• Combine City and State with a comma.

⭐ CONCAT() vs CONCATENATE()

Function	Status
CONCAT()	Modern (Recommended)
CONCATENATE()	Older Function (Still Works)
TEXTJOIN()	Best for Joining Many Cells with a Separator

💡 Pro Tip

For modern Excel, use CONCAT() for simple text merging and TEXTJOIN() when you need to combine many cells with separators such as spaces, commas, or hyphens automatically.

🔗 Formula 31 : TEXTJOIN()

The TEXTJOIN() function combines multiple text values into one string using a specified separator such as a space, comma, hyphen, or slash. It is much more powerful than CONCAT() because it can automatically ignore empty cells.

📌 Student Information Example

	A	B	C	D
1	First Name	Middle Name	Last Name	Full Name
2	Rahul	Kumar	Patel	=TEXTJOIN(" ",TRUE,A2:C2)

📝 Formula

=TEXTJOIN(" ",TRUE,A2:C2)

📖 Formula Breakdown

Argument	Meaning
" "	Separator (Space)
TRUE	Ignore Empty Cells
A2:C2	Cells to Join

Result

Rahul + Kumar + Patel

Rahul Kumar Patel

📌 More TEXTJOIN() Examples

Formula	Output
=TEXTJOIN("-",TRUE,A2:C2)	Rahul-Kumar-Patel
=TEXTJOIN(", ",TRUE,A2:C2)	Rahul, Kumar, Patel
=TEXTJOIN("/",TRUE,A2:C2)	Rahul/Kumar/Patel

💼 Real-Life Uses

• Create Full Names.
• Create Full Addresses.
• Generate Invoice Numbers.
• Combine Product Codes.
• Create Email Lists.
• Prepare Reports and Dashboards.

❌ Common Mistakes

• Using FALSE when empty cells should be ignored.
• Choosing the wrong separator.
• TEXTJOIN() is available only in Excel 365 and Excel 2021+.

📝 Practice Exercise

• Combine First, Middle and Last Name.
• Create a Full Address using commas.
• Create an Invoice Number using a hyphen.

⭐ CONCAT() vs TEXTJOIN()

Feature	CONCAT()	TEXTJOIN()
Separator	Manual	Automatic
Ignore Empty Cells	No	Yes
Best For	Simple Merge	Professional Reports

💡 Pro Tip

Use TEXTJOIN() instead of CONCAT() whenever you need separators and want Excel to automatically ignore blank cells. It is the preferred choice for modern Excel reports and dashboards.

🛡️ Formula 32 : IFERROR()

The IFERROR() function checks whether a formula returns an error. If an error occurs, it displays a custom value instead of Excel's default error message. It is one of the most important formulas for creating professional reports and dashboards.

📌 Student Marks Example

	A	B	C
1	Total Marks	Subjects	Average
2	450	5	=IFERROR(A2/B2,"Error")
3	300	0

📝 Formula

=IFERROR(A2/B2,"Error")

📖 Formula Breakdown

Argument	Meaning
A2/B2	Formula to Check
"Error"	Value to Display if an Error Occurs

Result

450 ÷ 5 = 90

300 ÷ 0 = Error

Instead of showing #DIV/0!, Excel displays your custom message.

📌 More IFERROR() Examples

Formula	Output
=IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Not Found")	Not Found
=IFERROR(A2/B2,0)	0
=IFERROR(A2/B2,"Invalid Data")	Invalid Data

💼 Real-Life Uses

• Hide #DIV/0! Errors.
• Hide #N/A from VLOOKUP() or XLOOKUP().
• Create Professional Dashboards.
• Build Clean MIS Reports.
• Improve User Experience.
• Create Error-Free Financial Reports.

❌ Common Mistakes

• Using IFERROR() to hide real data problems.
• Returning blank values when an error message would be more helpful.
• Applying IFERROR() to the wrong formula.

📝 Practice Exercise

• Display "Not Found" instead of #N/A.
• Display 0 instead of #DIV/0!.
• Display "Invalid Marks" when a calculation fails.

⭐ Common Errors Handled by IFERROR()

Excel Error	Reason
#DIV/0!	Division by Zero
#N/A	Value Not Found
#VALUE!	Invalid Value
#REF!	Invalid Cell Reference
#NAME?	Unknown Formula Name

💡 Pro Tip

Wrap formulas like VLOOKUP(), XLOOKUP(), INDEX()+MATCH(), and division calculations inside IFERROR() to create clean, professional Excel reports without displaying confusing error messages.

🎯 Formula 33 : IFS()

The IFS() function checks multiple conditions in order and returns the result for the first TRUE condition. It is a modern alternative to long Nested IF formulas, making your Excel formulas cleaner and easier to understand.

📌 Student Grade Example

	A	B
1	Marks	Grade
2	95	=IFS(A2>=90,"A+",A2>=80,"A",A2>=70,"B",A2>=60,"C",TRUE,"Fail")
3	82
4	68
5	45

📝 Formula

=IFS(A2>=90,"A+",A2>=80,"A",A2>=70,"B",A2>=60,"C",TRUE,"Fail")

📖 Formula Breakdown

Condition	Result
Marks ≥ 90	A+
Marks ≥ 80	A
Marks ≥ 70	B
Marks ≥ 60	C
Otherwise	Fail

Result

95 → A+
82 → A
68 → C
45 → Fail

💼 Real-Life Uses

• Student Grade Calculation.
• Employee Performance Rating.
• Sales Commission Levels.
• Bonus Calculation.
• Attendance Status.
• Dashboard KPI Classification.

📌 More IFS() Examples

Formula	Purpose
=IFS(A2>=90,"Excellent",A2>=75,"Good",TRUE,"Needs Improvement")	Performance Rating
=IFS(B2>=18,"Eligible",TRUE,"Not Eligible")	Eligibility Check

❌ Common Mistakes

• Writing conditions in the wrong order.
• Forgetting a default condition like TRUE,"Fail".
• Using overlapping conditions incorrectly.

📝 Practice Exercise

• Create grades: A+, A, B, C, Fail.
• Create employee ratings: Excellent, Good, Average, Poor.
• Create attendance status: Present, Late, Absent.

⭐ IF() vs Nested IF() vs IFS()

Function	Best Use
IF()	Single Condition
Nested IF()	Multiple Conditions (Complex)
IFS()	Multiple Conditions (Easy & Clean)

💡 Pro Tip

Use IFS() whenever you have multiple conditions instead of creating long Nested IF formulas. It makes your formulas easier to read, edit, and maintain in professional Excel reports.

🔄 Formula 34 : SWITCH()

The SWITCH() function compares one value against multiple possible values and returns the matching result. It is much cleaner than using multiple IF statements when checking exact values.

📌 Student Grade Example

	A	B
1	Grade	Result
2	A	=SWITCH(A2,"A","Excellent","B","Very Good","C","Good","D","Average","F","Fail","Invalid Grade")
3	B
4	C
5	F

📝 Formula

=SWITCH(A2,
"A","Excellent",
"B","Very Good",
"C","Good",
"D","Average",
"F","Fail",
"Invalid Grade")

📖 Formula Breakdown

Argument	Meaning
A2	Value to Check
"A","Excellent"	If A → Excellent
"B","Very Good"	If B → Very Good
"Invalid Grade"	Default Result

Result

A → Excellent
B → Very Good
C → Good
F → Fail

💼 Real-Life Uses

• Convert Grades into Results.
• Display Department Names.
• Convert Status Codes.
• Show Month Names.
• Create Employee Categories.
• Professional Dashboards.

📌 More SWITCH() Examples

Formula	Purpose
=SWITCH(A2,"M","Male","F","Female","Other")	Gender Display
=SWITCH(A2,1,"January",2,"February",3,"March","Invalid")	Month Name
=SWITCH(A2,"HR","Human Resources","IT","Information Technology","Unknown")	Department Name

❌ Common Mistakes

• Using SWITCH() for comparison operators like > or < (use IF() or IFS() instead).
• Forgetting the default value.
• Typing incorrect matching values.

📝 Practice Exercise

• Convert Grades into Performance.
• Convert Numbers (1–12) into Month Names.
• Convert Department Codes into Full Department Names.

⭐ IF() vs IFS() vs SWITCH()

Function	Best For
IF()	One Condition
IFS()	Multiple Logical Conditions
SWITCH()	Exact Value Matching

💡 Pro Tip

Use SWITCH() when checking one value against many fixed options. It makes formulas shorter, cleaner, and easier to maintain than long Nested IF statements.

🔍 Formula 35 : FIND()

The FIND() function returns the position (character number) of a specific text inside another text. It is case-sensitive, meaning it treats uppercase and lowercase letters differently.

📌 Student Email Example

	A	B
1	Email Address	@ Position
2	rahul@gmail.com	=FIND("@",A2)
3	neha@yahoo.com
4	amit@outlook.com

📝 Formula

=FIND("@",A2)

📖 Formula Breakdown

Argument	Meaning
"@"	Text to Find
A2	Text to Search

Result

rahul@gmail.com

@ is found at Position 6

📌 More FIND() Examples

Formula	Output
=FIND("E","EXCEL")	1
=FIND("-","GJ-2026-001")	3
=FIND("2026","GJ-2026-001")	4

💼 Real-Life Uses

• Find the @ symbol in Email Addresses.
• Extract Domain Names.
• Split Product Codes.
• Find Hyphens in IDs.
• Text Cleaning.
• Professional Data Analysis.

❌ Common Mistakes

• FIND() is case-sensitive.
• If the text is not found, Excel returns #VALUE!.
• Searching for the wrong character.

📝 Practice Exercise

• Find the position of "-" in INV-1001.
• Find the position of "@" in an email address.
• Find the position of "2026" in GJ-2026-001.

⭐ FIND() vs SEARCH()

Function	Case Sensitive	Wildcards
FIND()	Yes	No
SEARCH()	No	Yes

💡 Pro Tip

Use FIND() with LEFT(), RIGHT(), MID(), and LEN() to extract usernames from emails, split product codes, and clean imported data efficiently.

🔎 Formula 36 : SEARCH()

The SEARCH() function returns the position (character number) of specific text within another text string. Unlike FIND(), SEARCH() is not case-sensitive and supports wildcards (* and ?).

📌 Student Email Example

	A	B
1	Email Address	@ Position
2	Rahul@Gmail.com	=SEARCH("@",A2)
3	Neha@Yahoo.com
4	AMIT@Outlook.com

📝 Formula

=SEARCH("@",A2)

📖 Formula Breakdown

Argument	Meaning
"@"	Text to Search
A2	Cell Containing Text

Result

Rahul@Gmail.com

@ is found at Position 6

📌 More SEARCH() Examples

Formula	Output
=SEARCH("excel","Microsoft Excel")	11
=SEARCH("2026","GJ-2026-001")	4
=SEARCH("PATEL","Rahul Patel")	7

💼 Real-Life Uses

• Find @ in Email Addresses.
• Search Product Codes.
• Extract Usernames.
• Find Keywords in Reports.
• Text Cleaning.
• Professional Data Analysis.

❌ Common Mistakes

• If the text is not found, Excel returns #VALUE!.
• SEARCH() is not case-sensitive.
• Using the wrong search text.

📝 Practice Exercise

• Find the position of @ in an email address.
• Find the word Excel in "Microsoft Excel".
• Find 2026 in GJ-2026-001.

⭐ FIND() vs SEARCH()

Feature	FIND()	SEARCH()
Case Sensitive	✅ Yes	❌ No
Wildcard Support	❌ No	✅ Yes
Best For	Exact Case Match	General Text Search

💡 Pro Tip

Use SEARCH() with MID(), LEFT(), RIGHT(), and IFERROR() to extract usernames from emails, split product codes, and clean imported data efficiently.

🔄 Formula 37 : SUBSTITUTE()

The SUBSTITUTE() function replaces one text with another text in a string. It is commonly used to replace words, symbols, spaces, special characters, and formatting without changing the original data.

📌 Student ID Example

	A	B
1	Student ID	Updated ID
2	GJ-2026-001	=SUBSTITUTE(A2,"-","/")
3	MH-2026-025
4	RJ-2026-118

📝 Formula

=SUBSTITUTE(A2,"-","/")

📖 Formula Breakdown

Argument	Meaning
A2	Original Text
"-"	Text to Replace
"/"	New Text

Result

GJ-2026-001

⬇

GJ/2026/001

📌 More SUBSTITUTE() Examples

Formula	Output
=SUBSTITUTE("Mr. Rahul","Mr.","Mr")	Mr Rahul
=SUBSTITUTE("Excel-2026","-"," ")	Excel 2026
=SUBSTITUTE("A,B,C",","," | ")	A | B | C

💼 Real-Life Uses

• Replace Symbols in Product Codes.
• Clean Imported Data.
• Replace Company Names.
• Change Date Separators.
• Format Invoice Numbers.
• Prepare Professional Reports.

❌ Common Mistakes

• SUBSTITUTE() is case-sensitive.
• It replaces text, not cell formatting.
• Using incorrect old or new text.

📝 Practice Exercise

• Replace "-" with "/".
• Replace "," with ";".
• Replace "Mr." with "Mr".

⭐ SUBSTITUTE() vs REPLACE()

Feature	SUBSTITUTE()	REPLACE()
Replace by Text	✅ Yes	❌ No
Replace by Position	❌ No	✅ Yes
Case Sensitive	✅ Yes	Yes

💡 Pro Tip

Use SUBSTITUTE() with TRIM(), SEARCH(), LEFT(), and RIGHT() to clean messy imported data, standardize product codes, and prepare professional Excel reports.

✏️ Formula 38 : REPLACE()

The REPLACE() function replaces characters in a text string based on their position. Unlike SUBSTITUTE(), which replaces matching text, REPLACE() replaces characters starting from a specific position.

📌 Student ID Example

	A	B
1	Student ID	Updated ID
2	GJ-2026-001	=REPLACE(A2,4,4,"2027")
3	MH-2026-025
4	RJ-2026-118

📝 Formula

=REPLACE(A2,4,4,"2027")

📖 Formula Breakdown

Argument	Meaning
A2	Original Text
4	Starting Position
4	Characters to Replace
"2027"	New Text

Result

GJ-2026-001

⬇

GJ-2027-001

📌 More REPLACE() Examples

Formula	Output
=REPLACE("9876543210",1,6,"******")	******3210
=REPLACE("INV-1001",5,4,"2001")	INV-2001
=REPLACE("ABC123",4,3,"999")	ABC999

💼 Real-Life Uses

• Update Invoice Numbers.
• Mask Mobile Numbers.
• Edit Product Codes.
• Update Employee IDs.
• Protect Sensitive Data.
• Generate Professional Reports.

❌ Common Mistakes

• Using the wrong starting position.
• Replacing the wrong number of characters.
• Confusing REPLACE() with SUBSTITUTE().

📝 Practice Exercise

• Change 2026 to 2027.
• Mask the first 6 digits of a mobile number.
• Update an invoice number.

⭐ REPLACE() vs SUBSTITUTE()

Feature	REPLACE()	SUBSTITUTE()
Replace by Position	✅ Yes	❌ No
Replace by Text	❌ No	✅ Yes

💡 Pro Tip

Use REPLACE() when you know the exact character position to modify. Use SUBSTITUTE() when you know the text you want to replace. Combining both functions helps automate text editing, data masking, and code formatting in Excel.

🎯 Formula 39 : OFFSET()

The OFFSET() function returns a reference to a cell or range that is a specified number of rows and columns away from a starting cell. It is widely used for dynamic reports, dashboards, charts, and advanced Excel automation.

📌 Student Marks Example

	A	B	C
1	Name	Marks	Formula Result
2	Rahul	85	=OFFSET(B2,1,0)
3	Neha	92
4	Amit	76

📝 Formula

=OFFSET(B2,1,0)

📖 Formula Breakdown

Argument	Meaning
B2	Starting Cell
1	Move 1 Row Down
0	Move 0 Columns

Result

Starting Cell → B2 = 85

Formula returns → B3 = 92

📌 More OFFSET() Examples

Formula	Result
=OFFSET(A1,2,0)	Returns A3
=OFFSET(B2,0,1)	Returns C2
=OFFSET(C5,-2,-1)	Returns B3

💼 Real-Life Uses

• Create Dynamic Dashboards.
• Create Dynamic Charts.
• Build Dynamic Named Ranges.
• Generate Financial Reports.
• Analyze Monthly Sales Data.
• Create Automated Excel Templates.

❌ Common Mistakes

• Using a reference outside the worksheet.
• Entering incorrect row or column values.
• OFFSET() returns a reference, not just a value.
• Using OFFSET() when INDEX() may be faster for large datasets.

📝 Practice Exercise

• Return the value 2 rows below A1.
• Return the value 3 columns right of B2.
• Return the value 1 row up and 2 columns left of D5.

⭐ OFFSET() Movement Examples

Formula	Moves To
=OFFSET(A1,1,0)	A2
=OFFSET(A1,0,2)	C1
=OFFSET(C3,-1,-1)	B2

💡 Pro Tip

Use OFFSET() with SUM(), COUNTA(), MATCH(), and INDEX() to create dynamic ranges, auto-updating dashboards, and advanced Excel reports.

📌 Formula 40 : INDIRECT()

The INDIRECT() function converts a text string into a valid cell reference. It allows you to create dynamic references, dynamic reports, dashboard selectors, and multi-sheet calculations without manually changing formulas.

📌 Student Marks Example

	A	B	C
1	Cell Name	Marks	Formula Result
2	B4	85	=INDIRECT(A2)
3		92
4		76

📝 Formula

=INDIRECT(A2)

📖 Formula Breakdown

Argument	Meaning
A2	Contains Cell Reference as Text (B4)

Result

A2 contains → B4

=INDIRECT(A2)

Returns the value stored in B4 = 76

💼 Real-Life Uses

• Create Dynamic Dashboards.
• Build Multi-Sheet Reports.
• Create Dynamic Drop-down Reports.
• Switch Between Monthly Data.
• Dynamic Financial Reports.
• Advanced Excel Automation.

📌 More INDIRECT() Examples

Formula	Result
=INDIRECT("A1")	Returns Value of A1
=SUM(INDIRECT("B2:B10"))	Sum of B2:B10
=INDIRECT("'January'!B2")	Returns January Sheet B2 Value

❌ Common Mistakes

• If the reference text is invalid, Excel returns #REF!.
• Deleting the referenced cell causes an error.
• INDIRECT() is a volatile function and may slow down very large workbooks.

📝 Practice Exercise

• Store C5 in cell A1 and return its value.
• Calculate the sum using a dynamic range.
• Retrieve values from different worksheets dynamically.

⭐ OFFSET() vs INDIRECT()

Function	Best Use
OFFSET()	Move from a Starting Cell
INDIRECT()	Convert Text into Cell Reference

💡 Pro Tip

Combine INDIRECT() with SUM(), MATCH(), INDEX(), OFFSET(), and Data Validation to create powerful dynamic dashboards, interactive reports, and multi-sheet Excel models.

🎯 Formula 41 : CHOOSE()

The CHOOSE() function returns a value from a list based on an index number. It is useful for selecting months, grades, departments, products, and categories without using multiple IF statements.

📌 Student Grade Example

	A	B
1	Grade No.	Grade
2	1	=CHOOSE(A2,"A","B","C","D","F")
3	2
4	5

📝 Formula

=CHOOSE(A2,"A","B","C","D","F")

📖 Formula Breakdown

Argument	Meaning
A2	Index Number
"A","B","C","D","F"	List of Values

Result

1 → A
2 → B
5 → F

📌 More CHOOSE() Examples

Formula	Output
=CHOOSE(3,"Red","Blue","Green")	Green
=CHOOSE(2,"HR","IT","Sales")	IT
=CHOOSE(4,"Jan","Feb","Mar","Apr")	Apr

💼 Real-Life Uses

• Select Month Names.
• Convert Grade Numbers.
• Choose Department Names.
• Display Product Categories.
• Create Dynamic Reports.
• Dashboard Selections.

❌ Common Mistakes

• Index number must start from 1.
• If the index exceeds the list, Excel returns #VALUE!.
• Using CHOOSE() for large datasets instead of XLOOKUP().

📝 Practice Exercise

• Convert numbers into weekdays.
• Convert numbers into month names.
• Display department names using CHOOSE().

⭐ CHOOSE() vs SWITCH()

Function	Best Use
CHOOSE()	Select by Index Number
SWITCH()	Select by Exact Value

💡 Pro Tip

Use CHOOSE() with MATCH(), INDEX(), and XLOOKUP() to create dynamic dashboards, reports, and interactive Excel models.

🔄 Formula 42 : TRANSPOSE()

The TRANSPOSE() function converts rows into columns and columns into rows. It is extremely useful for rearranging data, creating reports, dashboards, and preparing imported datasets.

📌 Student Marks Example

	A	B	C
1	Student	Marks	Grade
2	Rahul	85	A
3	Neha	92	A+

📝 Formula

=TRANSPOSE(A1:C3)

📖 Formula Breakdown

Argument	Meaning
A1:C3	Range to Convert

Result

Before:

Student   Marks   Grade
Rahul       85      A
Neha        92      A+

After TRANSPOSE():

Student   Rahul   Neha
Marks       85      92
Grade        A      A+

📌 More TRANSPOSE() Examples

Formula	Purpose
=TRANSPOSE(A1:A10)	Vertical → Horizontal
=TRANSPOSE(A1:J1)	Horizontal → Vertical
=TRANSPOSE(B2:E6)	Convert Table Layout

💼 Real-Life Uses

• Convert Rows into Columns.
• Create Professional Reports.
• Prepare Dashboard Data.
• Rearrange Imported CSV Files.
• Create Dynamic Summary Tables.
• Prepare Data for Charts.

❌ Common Mistakes

• Not selecting enough output cells before entering the formula (older Excel versions).
• Trying to edit only one cell of a spilled array.
• Overwriting the output range with other data.

📝 Practice Exercise

• Convert a vertical list of student names into a horizontal list.
• Convert a horizontal list of months into a vertical list.
• Transpose a 5×5 table.

⭐ Copy-Paste vs TRANSPOSE()

Method	Updates Automatically?
Paste Special → Transpose	❌ No
TRANSPOSE()	✅ Yes

💡 Pro Tip

Use TRANSPOSE() when building dashboards, pivot-style reports, or importing data from other systems. In Excel 365, the result updates automatically whenever the original data changes.

🎲 Formula 43 : RAND()

The RAND() function generates a random decimal number between 0 and 1. Every time the worksheet recalculates, Excel generates a new random number automatically.

📌 Student Random Score Example

	A	B
1	Student	Random Value
2	Rahul	=RAND()
3	Neha
4	Amit

📝 Formula

=RAND()

📖 Formula Breakdown

Formula	Meaning
=RAND()	Returns a random decimal between 0 and 1.

Possible Results

0.258741
0.917462
0.483920

Every calculation generates a different value.

📌 More RAND() Examples

Formula	Purpose
=RAND()	Random Number (0–1)
=RAND()*100	Random Decimal (0–100)
=ROUND(RAND()*100,2)	Random Number with 2 Decimals

💼 Real-Life Uses

• Create Sample Data.
• Random Student Selection.
• Lottery Systems.
• Testing Excel Models.
• Simulation Projects.
• Generate Random Percentages.

❌ Common Mistakes

• RAND() changes every time Excel recalculates.
• It returns decimal values, not whole numbers.
• Copy → Paste Values if you want to keep the same random numbers.

📝 Practice Exercise

• Generate 10 random decimal numbers.
• Create random percentages using RAND().
• Generate random marks between 0 and 100 using RAND() and ROUND().

⭐ RAND() vs RANDBETWEEN()

Function	Returns
RAND()	Random Decimal (0–1)
RANDBETWEEN()	Random Whole Number

💡 Pro Tip

Use RAND() with SORT() to shuffle data randomly or combine it with ROUND() to generate realistic sample values for dashboards, reports, and practice worksheets.

🎲 Formula 44 : RANDBETWEEN()

The RANDBETWEEN() function returns a random whole number (integer) between two numbers that you specify. It is useful for generating random marks, lucky draw numbers, employee IDs, roll numbers, and sample datasets.

📌 Student Marks Example

	A	B
1	Student	Random Marks
2	Rahul	=RANDBETWEEN(35,100)
3	Neha
4	Amit

📝 Formula

=RANDBETWEEN(35,100)

📖 Formula Breakdown

Argument	Meaning
35	Minimum Value
100	Maximum Value

Possible Results

78
42
96
55

A new random number is generated whenever Excel recalculates.

📌 More RANDBETWEEN() Examples

Formula	Purpose
=RANDBETWEEN(1,10)	Random Number 1–10
=RANDBETWEEN(1000,9999)	Random 4-Digit PIN
=RANDBETWEEN(100,500)	Random Value Between 100–500

💼 Real-Life Uses

• Generate Student Marks.
• Lucky Draw Winner Selection.
• Generate Sample Data.
• Create Random Employee IDs.
• Testing Excel Dashboards.
• Random Quiz Questions.

❌ Common Mistakes

• The minimum number must be less than or equal to the maximum number.
• The value changes every time Excel recalculates.
• Use Copy → Paste Special → Values to keep the generated numbers fixed.

📝 Practice Exercise

• Generate random marks between 0 and 100.
• Create random roll numbers between 1000 and 9999.
• Select a random student number between 1 and 50.

⭐ RAND() vs RANDBETWEEN()

Function	Returns	Example
RAND()	Random Decimal	0.548921
RANDBETWEEN()	Random Whole Number	78

💡 Pro Tip

Combine RANDBETWEEN() with INDEX() or CHOOSE() to randomly select names, prizes, products, or quiz questions. This is widely used in Excel-based games, lucky draws, and training exercises.

📅 Formula 45 : TODAY()

The TODAY() function returns the current date. It updates automatically whenever the workbook is recalculated or reopened. It is one of the most useful date functions for attendance, invoices, reports, dashboards, and age calculations.

📌 Student Assignment Example

	A	B	C
1	Student	Today's Date	Status
2	Rahul	=TODAY()	Present

📝 Formula

=TODAY()

📖 Formula Breakdown

Formula	Meaning
=TODAY()	Returns the current system date.

Example Result

Today's Date → 28-Jul-2026

The date updates automatically every day.

📌 More TODAY() Examples

Formula	Purpose
=TODAY()	Current Date
=TODAY()+30	30 Days After Today
=TODAY()-7	7 Days Before Today
=A2-TODAY()	Days Remaining Until Date in A2

💼 Real-Life Uses

• Attendance Sheets.
• Invoice Due Date Calculation.
• Age Calculation.
• Loan EMI Tracking.
• Salary Reports.
• Project Deadline Tracking.

❌ Common Mistakes

• TODAY() returns only the date, not the current time.
• The value changes automatically every day.
• If the workbook calculation is set to Manual, the date may not update immediately.

📝 Practice Exercise

• Display today's date.
• Calculate a due date 15 days from today.
• Calculate how many days remain until an exam date.

⭐ TODAY() vs NOW()

Function	Returns
TODAY()	Current Date Only
NOW()	Current Date and Time

💡 Pro Tip

Use TODAY() with IF(), DATEDIF(), EDATE(), and EOMONTH() to automatically track deadlines, due dates, age calculations, loan schedules, and attendance records.

⏰ Formula 46 : NOW()

The NOW() function returns the current date and current time. It updates automatically whenever the workbook recalculates. It is commonly used in attendance systems, employee tracking, dashboards, invoices, and real-time reports.

📌 Employee Login Example

	A	B	C
1	Employee	Login Time	Status
2	Rahul	=NOW()	Logged In

📝 Formula

=NOW()

📖 Formula Breakdown

Formula	Meaning
=NOW()	Returns Current Date and Current Time

Example Result

28-Jul-2026
03:45 PM

Both the date and time update automatically.

📌 More NOW() Examples

Formula	Purpose
=NOW()	Current Date & Time
=NOW()+1	Same Time Tomorrow
=NOW()-1	Same Time Yesterday
=TEXT(NOW(),"dd-mmm-yyyy hh:mm AM/PM")	Custom Date & Time Format

💼 Real-Life Uses

• Employee Login Tracking.
• Attendance Systems.
• Invoice Generation.
• Real-Time Dashboards.
• Project Monitoring.
• Activity Timestamp.

❌ Common Mistakes

• NOW() changes whenever Excel recalculates.
• It returns both date and time.
• Use Copy → Paste Special → Values if you want to save a fixed timestamp.

📝 Practice Exercise

• Display the current date and time.
• Show tomorrow's date and time.
• Format NOW() using the TEXT() function.

⭐ TODAY() vs NOW()

Function	Returns	Best For
TODAY()	Current Date Only	Attendance, Due Dates
NOW()	Current Date & Time	Login Time, Timestamps

💡 Pro Tip

Use NOW() with TEXT(), IF(), TODAY(), and Conditional Formatting to create live dashboards, employee attendance trackers, automatic timestamps, and project monitoring systems.

📆 Formula 47 : EDATE()

The EDATE() function returns a date that is a specified number of months before or after a given date. It is commonly used for calculating loan EMI dates, insurance renewal dates, subscription expiry dates, salary cycles, and contract end dates.

📌 Loan EMI Example

	A	B	C
1	Loan Date	Months	Next EMI Date
2	28-Jul-2026	1	=EDATE(A2,B2)
3	15-Aug-2026	6
4	01-Jan-2026	12

📝 Formula

=EDATE(A2,B2)

📖 Formula Breakdown

Argument	Meaning
A2	Starting Date
B2	Number of Months to Add/Subtract

Example Results

28-Jul-2026 + 1 Month → 28-Aug-2026
15-Aug-2026 + 6 Months → 15-Feb-2027
01-Jan-2026 + 12 Months → 01-Jan-2027

📌 More EDATE() Examples

Formula	Purpose
=EDATE(TODAY(),1)	One Month After Today
=EDATE(TODAY(),-3)	Three Months Before Today
=EDATE(A2,24)	Date After 24 Months

💼 Real-Life Uses

• Loan EMI Schedule.
• Insurance Policy Renewal.
• Subscription Renewal.
• Salary Increment Cycle.
• Contract Expiry Date.
• Fixed Deposit Maturity Date.

❌ Common Mistakes

• The starting value must be a valid Excel date.
• Negative months subtract months from the date.
• If the cell is not formatted as a Date, a serial number may appear.

📝 Practice Exercise

• Add 3 months to today's date.
• Subtract 6 months from today's date.
• Calculate the maturity date after 5 years (60 months).

⭐ EDATE() vs DATE()

Function	Best Use
DATE()	Create a Specific Date
EDATE()	Add or Subtract Months

💡 Pro Tip

Combine EDATE() with TODAY(), EOMONTH(), IF(), and NETWORKDAYS() to build professional EMI calculators, insurance renewal trackers, salary planners, and subscription management dashboards.

📅 Formula 48 : EOMONTH()

The EOMONTH() function returns the last day of a month based on a given date. It is widely used in accounting, payroll, GST filing, monthly reports, financial analysis, and subscription management.

📌 Salary Processing Example

	A	B	C
1	Joining Date	Months	Month End
2	15-Jul-2026	0	=EOMONTH(A2,B2)
3	10-Aug-2026	1
4	05-Jan-2026	12

📝 Formula

=EOMONTH(A2,B2)

📖 Formula Breakdown

Argument	Meaning
A2	Starting Date
B2	Months to Move

Example Results

15-Jul-2026 + 0 Months → 31-Jul-2026
10-Aug-2026 + 1 Month → 30-Sep-2026
05-Jan-2026 + 12 Months → 31-Jan-2027

📌 More EOMONTH() Examples

Formula	Purpose
=EOMONTH(TODAY(),0)	Last Day of Current Month
=EOMONTH(TODAY(),1)	Last Day of Next Month
=EOMONTH(TODAY(),-1)	Last Day of Previous Month

💼 Real-Life Uses

• Payroll Processing.
• Monthly Salary Reports.
• GST Return Preparation.
• Accounting Closing Date.
• Subscription Expiry.
• Financial Dashboards.

❌ Common Mistakes

• The first argument must be a valid Excel date.
• If the result appears as a number, change the cell format to Date.
• Negative months move to previous months.

📝 Practice Exercise

• Find the last day of the current month.
• Find the last day of next month.
• Find the last day of the previous month.

⭐ EDATE() vs EOMONTH()

Function	Returns
EDATE()	Same Day After/Before X Months
EOMONTH()	Last Day of the Month

💡 Pro Tip

Combine EOMONTH() with TODAY(), EDATE(), IF(), and NETWORKDAYS() to build payroll systems, month-end financial reports, GST schedules, and automatic due-date calculators.

📅 Formula 49 : NETWORKDAYS()

The NETWORKDAYS() function calculates the number of working days between two dates. By default, it excludes Saturdays, Sundays, and optional holiday dates. It is one of the most useful functions for HR, payroll, leave management, and project planning.

📌 Employee Working Days Example

	A	B	C
1	Start Date	End Date	Working Days
2	01-Jul-2026	31-Jul-2026	=NETWORKDAYS(A2,B2)

📝 Formula

=NETWORKDAYS(A2,B2)

📖 Formula Breakdown

Argument	Meaning
A2	Start Date
B2	End Date
Holidays (Optional)	Holiday Date Range

Example Result

Start Date → 01-Jul-2026
End Date → 31-Jul-2026

Working Days = 23 (Weekends Excluded)

📌 More NETWORKDAYS() Examples

Formula	Purpose
=NETWORKDAYS(A2,B2)	Working Days
=NETWORKDAYS(A2,B2,E2:E10)	Exclude Holidays
=NETWORKDAYS(TODAY(),A2)	Working Days Until Due Date

💼 Real-Life Uses

• Employee Attendance.
• Payroll Calculation.
• Leave Management.
• Project Timeline Planning.
• Office Working Days.
• Delivery Date Tracking.

❌ Common Mistakes

• Using text instead of valid Excel dates.
• Forgetting to include holiday dates.
• Assuming Saturday and Sunday are always working days (they are excluded by default).

📝 Practice Exercise

• Calculate working days between two dates.
• Exclude public holidays using a holiday list.
• Calculate working days remaining until a project deadline.

⭐ DAYS() vs NETWORKDAYS()

Function	Returns
DAYS()	Total Calendar Days
NETWORKDAYS()	Working Days Only

💡 Pro Tip

Combine NETWORKDAYS() with TODAY(), IF(), WORKDAY(), and Conditional Formatting to build professional HR systems, payroll calculators, leave trackers, and project management dashboards.

🏢 Formula 50 : WORKDAY()

The WORKDAY() function returns a date that is a specified number of working days before or after a given date. It automatically excludes Saturdays, Sundays, and optional holidays. It is one of the most important formulas for HR, project management, delivery scheduling, payroll, and business planning.

📌 Project Deadline Example

	A	B	C
1	Start Date	Working Days	Completion Date
2	28-Jul-2026	10	=WORKDAY(A2,B2)
3	01-Aug-2026	30

📝 Formula

=WORKDAY(A2,B2)

📖 Formula Breakdown

Argument	Meaning
A2	Starting Date
B2	Working Days to Add/Subtract
Holidays (Optional)	Holiday Date Range

Example Result

Start Date → 28-Jul-2026
Working Days → 10

Completion Date → 11-Aug-2026

Weekends are skipped automatically.

📌 More WORKDAY() Examples

Formula	Purpose
=WORKDAY(TODAY(),7)	7 Working Days After Today
=WORKDAY(TODAY(),-5)	5 Working Days Before Today
=WORKDAY(A2,20,E2:E10)	Exclude Holidays

💼 Real-Life Uses

• Project Deadline Planning.
• Employee Joining Date.
• Payroll Processing.
• Courier & Delivery Schedule.
• Loan EMI Planning.
• Business Task Scheduling.

❌ Common Mistakes

• Using invalid date values.
• Forgetting to include holiday dates.
• Expecting weekends to be counted (they are excluded by default).

📝 Practice Exercise

• Find the date after 15 working days.
• Find the date before 20 working days.
• Calculate the delivery date excluding holidays.

⭐ NETWORKDAYS() vs WORKDAY()

Function	Returns
NETWORKDAYS()	Number of Working Days
WORKDAY()	Future/Past Working Date

💡 Pro Tip

Combine WORKDAY() with NETWORKDAYS(), TODAY(), IF(), and Conditional Formatting to build professional HR systems, payroll calculators, delivery trackers, project management dashboards, and business planning tools.

🎉 Congratulations! You Have Completed All 50 Excel Formulas

You have successfully learned the Top 50 Microsoft Excel Formulas from Beginner to Advanced level. These formulas are widely used by students, office employees, accountants, HR professionals, business owners, data analysts, and finance experts.

📚 Quick Formula Cheat Sheet

Category	Popular Formulas
Math	SUM, AVERAGE, MAX, MIN
Logical	IF, IFS, SWITCH, IFERROR
Lookup	VLOOKUP, XLOOKUP, INDEX, MATCH
Text	LEFT, RIGHT, MID, LEN, CONCAT, TEXTJOIN
Date & Time	TODAY, NOW, EDATE, EOMONTH
Advanced	OFFSET, INDIRECT, NETWORKDAYS, WORKDAY

❌ Common Excel Mistakes

• Using wrong cell references.
• Not using Absolute References ($A$1).
• Ignoring IFERROR() in reports.
• Typing formulas manually without checking syntax.
• Not formatting dates and numbers correctly.
• Using VLOOKUP() where XLOOKUP() is more suitable.

❓ Frequently Asked Questions (FAQ)

Which Excel formula is used the most?

SUM(), IF(), XLOOKUP(), VLOOKUP(), COUNTIF(), SUMIF(), and IFERROR() are among the most commonly used Excel formulas.

Can beginners learn all 50 formulas?

Yes. Start with basic formulas like SUM(), AVERAGE(), and IF(), then gradually practice advanced functions.

Which formulas are important for Excel job interviews?

IF(), VLOOKUP(), XLOOKUP(), INDEX(), MATCH(), SUMIFS(), COUNTIFS(), IFERROR(), FILTER(), and TEXTJOIN().

Which Excel version supports all these formulas?

Microsoft Excel 365 and Excel 2021 support all modern formulas covered in this guide.

🏆 Final Conclusion

Learning Excel is one of the best investments for students, office employees, accountants, HR professionals, data analysts, business owners, and freelancers. By mastering these Top 50 Microsoft Excel Formulas, you can automate calculations, analyze data faster, create professional reports, and improve your productivity. Practice each formula with real examples to become confident in Excel and prepare yourself for interviews, office work, and business reporting.

🚀 Continue Learning

• 100+ Excel Keyboard Shortcuts
• Excel Pivot Table Complete Guide
• Excel Charts Tutorial
• Excel Conditional Formatting Guide
• Excel Data Validation Tutorial
• Microsoft Excel Interview Questions & Answers

⭐ Thank You for Reading!

If this guide helped you, please share it with your friends, classmates, and colleagues.

📌 Bookmark this page for future reference.

💬 Have any questions? Leave a comment below.

🚀 Keep Learning with Smart AI Profit Hub

Last Updated: July 2026 • This guide is regularly updated with the latest Microsoft Excel formulas and Microsoft 365 features.