Excel offers a wide range of built-in functions that simplify calculations, automate repetitive tasks, and improve data analysis. Logical functions help evaluate conditions, date and time functions manage schedules and timelines, text functions manipulate text values, and financial functions support loan and investment calculations.
These functions are commonly used in finance, human resources, sales, operations, education, and business reporting. Understanding how they work enables you to build smarter spreadsheets and make better decisions based on your data.
Learning Objectives
Use logical functions such as IF, AND, OR, and IFS
Work with date and time functions
Manipulate text using common text functions
Calculate loan payments using PMT
Understand NPV and IRR
Apply these functions to real-world business scenarios
Logical Functions
Logical functions evaluate conditions and return results based on whether those conditions are true or false.
Common logical functions include:
IF
IFS
AND
OR
NOT
IF Function
The IF function performs one action if a condition is true and another if it is false.
Syntax
=IF(logical_test,value_if_true,value_if_false)
Example
Student | Marks |
John | 80 |
Priya | 45 |
David | 72 |
Formula:
=IF(B2>=50,"Pass","Fail")
Output
Pass
AND Function
The AND function returns TRUE only when all specified conditions are true.
Example:
=AND(B2>=50,C2>=75)
OR Function
The OR function returns TRUE if at least one condition is true.
Example:
=OR(B2>=50,C2>=75)
IFS Function
The IFS function evaluates multiple conditions without requiring nested IF statements.
Example:
=IFS(B2>=90,"A",B2>=75,"B",B2>=50,"C",TRUE,"Fail")
Date and Time Functions
Excel stores dates as serial numbers, allowing calculations such as finding the number of days between dates or displaying the current date and time.
Common functions include:
DATE
TODAY
NOW
YEAR
MONTH
DAY
TODAY Function
=TODAY()
Returns the current date.
NOW Function
=NOW()
Returns the current date and time.
DATE Function
=DATE(2026,8,6)
Creates a valid Excel date.
YEAR Function
=YEAR(A2)
Returns the year from a date.
Text Functions
Text functions help clean, combine, and extract information from text values.
Common functions include:
LEFT
RIGHT
MID
LEN
CONCAT
TEXT
UPPER
LOWER
PROPER
TRIM
LEFT Function
=LEFT(A2,4)
Returns the first four characters.
RIGHT Function
=RIGHT(A2,3)
Returns the last three characters.
MID Function
=MID(A2,3,5)
Extracts characters starting from a specified position.
CONCAT Function
=CONCAT(A2," ",B2)
Combines text from multiple cells.
Financial Functions
Excel provides financial functions for calculating loan repayments and evaluating investments.
PMT Function
The PMT function calculates the periodic payment for a loan.
Syntax
=PMT(rate,nper,pv)
Example:
=PMT(8%/12,60,-500000)
This calculates the monthly payment for a loan of 500,000 over 60 months at 8% annual interest.
NPV Function
The NPV function calculates the Net Present Value of an investment.
Example:
=NPV(10%,B2:B6)
IRR Function
The IRR function calculates the Internal Rate of Return for a series of cash flows.
Example:
=IRR(B2:B7)
Practical Example
Month | Cash Flow |
Initial Investment | -50000 |
Year 1 | 15000 |
Year 2 | 18000 |
Year 3 | 20000 |
Year 4 | 22000 |
Use:
=NPV(10%,B3:B6)
and
=IRR(B2:B6)
to evaluate the investment.
Best Practices
Use IF instead of multiple nested conditions where appropriate.
Prefer IFS for evaluating several conditions.
Format date cells correctly.
Use TRIM when importing text data.
Verify financial inputs before using PMT, NPV, or IRR.
Common Mistakes
Using text instead of dates.
Forgetting quotation marks around text values.
Mixing annual and monthly interest rates in PMT.
Selecting incorrect cash flow ranges for NPV and IRR.
Using incorrect logical operators.
Still need help?
Contact us