All Excel Functions

Statistical function
Function Name Description Example Real-Life Use Case

Calculates the average

(arithmetic mean) of a Calculating the average score of
AVERAGE set of numbers. =AVERAGE(A1:A10) students in a class.

Returns the median Finding the middle salary in a

(middle value) of a set of company to analyze wage
MEDIAN numbers. =MEDIAN(B2:B20) distribution.

Determining the lowest

Returns the smallest temperature recorded in a
MIN value in a dataset. =MIN(D1:D50) month.

Returns the largest value Finding the highest sales figure in

MAX in a dataset. =MAX(E1:E30) a year.

Counts the number of Counting how many employees

cells that contain have submitted their monthly
COUNT numbers in a range. =COUNT(F1:F100) reports.

Counts the number of Counting the total number of

non-empty cells in a participants who filled out a
COUNTA range. =COUNTA(G1:G50) survey.

Counts the number of Tracking the number of missing

COUNTBLANK empty cells in a range. =COUNTBLANK(H1:H20) data points in a dataset.

Counts the number of

cells in a range that Counting how many products
COUNTIF meet a single condition. =COUNTIF(I1:I50, ">50") have a price greater than $50.

=COUNTIFS(J1:J50, ">50", Counting how many test scores

Counts the number of
COUNTIFS J1:J50, "<100") are between 50 and 100.
cells in a range that
Function Name Description Example Real-Life Use Case

meet multiple
conditions.

Adds up all the numbers Calculating the total revenue

SUM in a range. =SUM(K1:K10) generated from sales.

Adds up the numbers in

a range that meet a Summing up all invoices with
SUMIF single condition. =SUMIF(L1:L20, ">100") amounts greater than $100.

Adds up the numbers in =SUMIFS(M1:M30,

a range that meet M1:M30, ">50", M1:M30, Summing up all expenses
SUMIFS multiple conditions. "<200") between 50and50and200.

Returns the k-th largest Finding the third-highest sale in a

LARGE value in a dataset. =LARGE(U1:U50, 3) quarter.

Returns the k-th

smallest value in a Identifying the second-lowest
SMALL dataset. =SMALL(V1:V50, 2) expense in a budget.

Math Functions:-
Function Name Description Example Real-Life Use Case

Returns the absolute Calculating the difference

value of a number between forecasted and actual
ABS (removes the sign). =ABS(-15) sales, regardless of direction.

Adds all the numbers Calculating total monthly

SUM in a range of cells. =SUM(A1:A10) expenses.

Adds the cells

specified by a given
SUMIF condition or criteria. =SUMIF(B1:B10, ">100") Summing sales above $100.
Function Name Description Example Real-Life Use Case

Adds the cells Summing sales

specified by multiple =SUMIFS(C1:C10, D1:D10, between 50and50and100 in a
SUMIFS conditions. ">50", E1:E10, "<100") specific region.

Multiplies all the

numbers given as Calculating compound interest or
PRODUCT arguments. =PRODUCT(2, 3, 4) total production output.

Rounds a number to a
specified number of Rounding prices to two decimal
ROUND digits. =ROUND(3.14159, 2) places for invoices.

Rounds a number up, Rounding up the number of boxes

ROUNDUP away from zero. =ROUNDUP(2.3, 0) needed for shipping.

Rounds a number Rounding down the number of full

ROUNDDOWN down, towards zero. =ROUNDDOWN(2.9, 0) teams that can be formed.

Rounds a number up
to the nearest
multiple of Rounding up time to the nearest
CEILING significance. =CEILING(7, 2) 15 minutes for billing.

Rounds a number
down to the nearest
multiple of Rounding down prices to the
FLOOR significance. =FLOOR(7, 2) nearest dollar.

Rounds a number
down to the nearest Calculating the number of full
INT integer. =INT(5.9) days between two dates.

Truncates a number
to an integer by
removing the Removing cents from a price to
TRUNC fractional part. =TRUNC(8.9) show only whole dollars.
Function Name Description Example Real-Life Use Case

Returns the
remainder after a
number is divided by a Determining if a number is even
MOD divisor. =MOD(10, 3) or odd.

Returns the integer Calculating how many full boxes

QUOTIENT portion of a division. =QUOTIENT(10, 3) can be filled with items.

Returns the result of a

number raised to a Calculating compound growth or
POWER power. =POWER(2, 3) exponential increases.

Returns the square Calculating the length of a side of

SQRT root of a number. =SQRT(16) a square plot given the area.

Returns e raised to
the power of a given Calculating continuous compound
EXP number. =EXP(1) interest.

Returns the value of π Calculating the circumference of a

PI (pi). =PI() circle.

Converts degrees to Converting angles for

RADIANS radians. =RADIANS(180) trigonometric calculations.

Converts radians to Converting angle measurements

DEGREES degrees. =DEGREES(PI()) for construction.

Returns the greatest

common divisor of
GCD two or more integers. =GCD(24, 36) Simplifying ratios in recipes.

Returns the least

common multiple of Scheduling events that repeat at
LCM two or more integers. =LCM(4, 6) different intervals.
Function Name Description Example Real-Life Use Case

Returns a random
number between 0
RAND and 1. =RAND() Generating random test data.

Returns a random
integer between two Assigning random prizes in a
RANDBETWEEN specified numbers. =RANDBETWEEN(1, 100) contest.

Solving permutation and

Returns the factorial combination problems in
FACT of a number. =FACT(5) → 120 statistics.

Multiplies
corresponding
elements in arrays Calculating weighted averages or
and returns the sum =SUMPRODUCT(C1:C5, total revenue from unit prices and
SUMPRODUCT of the products. D1:D5) quantities.

Logical Functions:-
Function
Name Description Example Real-Life Use Case

Checks whether a
condition is met and Grading students based on
returns one value if TRUE their scores (e.g., Pass if
IF and another if FALSE. =IF(A1>50, "Pass", "Fail") score > 50, Fail otherwise).

Returns TRUE if all Checking if a product's

conditions are TRUE; price falls within a specific
AND otherwise, returns FALSE. =AND(A1>10, A1<20) range (e.g., 10to10to20).

Returns TRUE if any Determining if a product is

condition is TRUE; available in either Red or
OR otherwise, returns FALSE. =OR(A1="Red", A1="Blue") Blue color.
Function
Name Description Example Real-Life Use Case

Reverses the logical value

of its argument (TRUE Identifying employees who
becomes FALSE, and vice do not meet a sales target
NOT versa). =NOT(A1>50) (e.g., sales ≤ $50,000).

Checks multiple conditions

and returns a value Assigning letter grades to
corresponding to the first =IFS(A1>90, "A", A1>80, "B", students based on their
IFS TRUE condition. A1>70, "C", TRUE, "F") test scores.

Matches an expression Creating a traffic light

against a list of values and =SWITCH(A1, "Red", "Stop", system for decision-
returns the corresponding "Green", "Go", "Yellow", making (e.g., Red = Stop,
SWITCH result. "Caution", "Unknown") Green = Go).

Returns TRUE if an odd

number of conditions are Determining if only one of
TRUE; otherwise, returns two conditions is met (e.g.,
XOR FALSE. =XOR(A1>10, A1<20) exclusive pricing tiers).

Setting default values in

Returns the logical value formulas or conditional
TRUE TRUE. =TRUE() formatting rules.

Setting default values in

Returns the logical value formulas or conditional
FALSE FALSE. =FALSE() formatting rules.
Lookup and reference Functions:-
Function Name Description Example Real-Life Use Case

Searches for a value in

the first column of a
table and returns a Finding the price of a
value in the same row =VLOOKUP("Apple", A1:B10, 2, product ("Apple") in a
VLOOKUP from another column. FALSE) price list.

Searches for a value in

the first row of a table
and returns a value in Retrieving sales data for
the same column from =HLOOKUP("2023", A1:E5, 2, the year 2023 from a
HLOOKUP another row. FALSE) horizontal table.

Returns the value of

an element in a table Extracting the value from
based on its row and the 3rd row and 2nd
INDEX column number. =INDEX(A1:C10, 3, 2) column of a dataset.

Searches for a
specified item in a Finding the row number
range and returns its where "Banana" appears
MATCH relative position. =MATCH("Banana", A1:A10, 0) in a list.

Looks up a value in a
one-row or one-
column range and
retrieves a value from Finding the price of
the same position in =LOOKUP("Orange", A1:A10, "Orange" in a list of fruits
LOOKUP another range. B1:B10) and prices.

Returns a value from a Selecting a color based on

list of values based on =CHOOSE(3, "Red", "Blue", user input (e.g., 3 =
CHOOSE a given index number. "Green", "Yellow") → Green Green).

Converts a text string

into a valid cell Dynamically referencing
INDIRECT reference. =INDIRECT("A"&5) cell A5 using a formula.
Function Name Description Example Real-Life Use Case

Returns the row

number of a Identifying the row
ROW reference. =ROW(A5) → 5 number of a specific cell.

Returns the column

number of a Identifying the column
COLUMN reference. =COLUMN(B1) → 2 number of a specific cell.

Returns the number of Counting the total number

ROWS rows in a reference. =ROWS(A1:A10) → 10 of rows in a dataset.

Returns the number of

columns in a Counting the total number
COLUMNS reference. =COLUMNS(A1:C1) → 3 of columns in a dataset.

Converts a vertical
range of cells into a Rotating a column of data
horizontal range, or into a row for better layout
TRANSPOSE vice versa. {=TRANSPOSE(A1:A5)} or analysis.

Filters a range of data

based on specified
criteria (available in Extracting rows where the
FILTER Excel 365/2021). =FILTER(A1:B10, A1:A10="Apple") product name is "Apple".

Sorts a range of data

in ascending or
descending order Sorting sales data by
(available in Excel revenue in descending
SORT 365/2021). =SORT(A1:B10, 2, -1) order.

Searches for a value in

a range and returns a Finding the price of
corresponding result "Banana" and returning
(available in Excel =XLOOKUP("Banana", A1:A10, "Not Found" if it doesn't
XLOOKUP 365/2021). B1:B10, "Not Found") exist.
Date and time Functions:-

Function Name Description Example Real-Life Use Case

Tracking deadlines or
calculating how many
days are left until a
TODAY Returns the current date. =TODAY() project deadline.

Logging timestamps for

Returns the current date data entry or tracking
NOW and time. =NOW() real-time updates.

Creates a valid date from Generating dates

year, month, and day dynamically for reports
DATE values. =DATE(2023, 10, 5) → 10/5/2023 or schedules.

Creates a valid time from Scheduling tasks or

hour, minute, and second setting reminders with
TIME values. =TIME(14, 30, 0) → 2:30 PM specific times.

Calculating employee
Extracts the year from a tenure based on hire
YEAR given date. =YEAR("10/5/2023") → 2023 dates.

Extracts the month from a Grouping sales data by

MONTH given date. =MONTH("10/5/2023") → 10 month for analysis.

Identifying specific days

Extracts the day from a for recurring events (e.g.,
DAY given date. =DAY("10/5/2023") → 5 billing cycles).

Extracts the hour from a Calculating work hours or

HOUR given time. =HOUR("2:30 PM") → 14 scheduling shifts.
Function Name Description Example Real-Life Use Case

Setting reminders for

Extracts the minute from a meetings or
MINUTE given time. =MINUTE("2:30 PM") → 30 appointments.

Timing events or
Extracts the second from a processes down to the
SECOND given time. =SECOND("2:30:45 PM") → 45 second.

Returns the day of the week Determining weekends

as a number (1 for Sunday, =WEEKDAY("10/5/2023") → 5 or weekdays for
WEEKDAY 2 for Monday, etc.). (Thursday) scheduling purposes.

Tracking weekly progress

Returns the week number or organizing data by
WEEKNUM of the year for a given date. =WEEKNUM("10/5/2023") → 40 week.

Calculates the difference Calculating employee

between two dates in years, =DATEDIF("1/1/2020", tenure or project
DATEDIF months, or days. "10/5/2023", "Y") → 3 duration.
Financial Functions:-
Function Name Description Example Real-Life Use Case

Calculates the periodic

payment for a loan or
investment based on
constant payments and =PMT(5%/12, 60, 10000) → Determining monthly
PMT interest. -₹188.71 mortgage or loan payments.

Calculates the interest Tracking how much of a loan

portion of a loan payment =IPMT(5%/12, 1, 60, payment goes toward
IPMT for a specific period. 10000) → -₹41.67 interest.

Calculates the principal Tracking how much of a loan

portion of a loan payment =PPMT(5%/12, 1, 60, payment reduces the
PPMT for a specific period. 10000) → -₹147.04 principal balance.

Calculates the future value

of an investment based on Estimating the future value of
periodic payments and =FV(5%/12, 60, -200, 0) → a retirement fund with
FV interest. ₹13,598.76 regular contributions.

Calculates the present

value of an investment Determining the current
based on periodic =PV(5%/12, 60, -200, 0) → value of a future stream of
PV payments and interest. ₹10,280.32 payments (e.g., annuities).

Calculates the number of

periods required to pay off Finding how many months it
a loan or reach an =NPER(5%/12, -200, will take to pay off a loan with
NPER investment goal. 10000) → 55.48 fixed payments.

Calculates the interest rate Determining the interest rate

per period for a loan or =RATE(60, -200, 10000) → required to achieve a financial
RATE investment. 0.42% goal.
Function Name Description Example Real-Life Use Case

Calculates the net present

value of an investment Evaluating the profitability of
based on a series of cash =NPV(5%, -1000, 300, 400, a project by discounting
NPV flows. 500) → ₹85.94 future cash flows.

Calculates the internal rate Determining the return on

of return for a series of =IRR({-1000, 300, 400, investment for a business
IRR cash flows. 500}) → 8.21% project.

Database Function:-
Function
Name Description Example Real-Life Use Case

Adds up numbers in a Summing sales figures for a

database column that specific region or product
DSUM meet specified criteria. =DSUM(A1:C10, "Sales", E1:F2) category.

Calculates the average

of numbers in a
database column that =DAVERAGE(A1:C10, "Sales", Finding the average sales for a
DAVERAGE meet specified criteria. E1:F2) specific product or time period.

Counts numeric entries

in a database column Counting how many transactions
that meet specified =DCOUNT(A1:C10, "Sales", have sales figures above a certain
DCOUNT criteria. E1:F2) threshold.

Counts non-blank
entries in a database Counting how many products
column that meet =DCOUNTA(A1:C10, "Product", meet specific conditions (e.g., in
DCOUNTA specified criteria. E1:F2) stock).

Identifying the highest sales figure

Finds the maximum
DMAX =DMAX(A1:C10, "Sales", E1:F2) for a specific region or product.
value in a database
Function
Name Description Example Real-Life Use Case

column that meets

specified criteria.

Finds the minimum

value in a database
column that meets Identifying the lowest sales figure
DMIN specified criteria. =DMIN(A1:C10, "Sales", E1:F2) for a specific region or product.

Retrieves a single value

from a database column
that matches specified Retrieving the sales figure for a
DGET criteria. =DGET(A1:C10, "Sales", E1:F2) specific product or transaction.

Other Important Functions:-

Function Name Description Example Real-Life Use Case

Combines
multiple text Creating full names
strings into one =CONCATENATE("Hello", " ", "World") → "Hello from first and last
CONCATENATE string. World" names.

Combines
multiple text
strings with a
delimiter and Generating a
optionally comma-separated
ignores blank list of names or
TEXTJOIN cells. =TEXTJOIN(", ", TRUE, A1:A5) items.

Removes non- Cleaning imported

printable data that contains
characters from hidden or invalid
CLEAN text. =CLEAN(A1) characters.
Function Name Description Example Real-Life Use Case

Removes extra
spaces from
text, leaving
only single Fixing inconsistent
spaces spacing in user-
between entered data or
TRIM words. =TRIM(" Hello World ") → "Hello World" imported text.

Replaces Standardizing date

specific text in formats or replacing
a string with =SUBSTITUTE("2023-10-05", "-", "/") → placeholders in
SUBSTITUTE new text. "2023/10/05" templates.

Replaces part
of a text string
with new text, Modifying parts of
based on strings (e.g.,
position and =REPLACE("2023-10-05", 6, 2, "Nov") → "2023- updating month
REPLACE length. Nov-05" names).

Extracts a
specified
number of
characters from Extracting initials,
the start of a prefixes, or codes
LEFT text string. =LEFT("Hello World", 5) → "Hello" from text data.

Extracts a
specified
number of Extracting file
characters from extensions or
the end of a suffixes from text
RIGHT text string. =RIGHT("Hello World", 5) → "World" data.

Extracts a
substring from
the middle of a Extracting specific
text string, portions of text
MID =MID("Hello World", 7, 5) → "World"
starting at a (e.g., extracting
Function Name Description Example Real-Life Use Case

specified product IDs from

position. codes).

Validating input
lengths (e.g.,
Returns the ensuring passwords
number of meet minimum
characters in a character
LEN text string. =LEN("Hello World") → 11 requirements).

Finds the
position of one Locating keywords
text string or substrings in text
within another (e.g., finding a
(case- product name in
SEARCH insensitive). =SEARCH("World", "Hello World") → 7 descriptions).

Finds the
position of one
text string
within another Identifying exact
(case- matches for case-
FIND sensitive). =FIND("world", "Hello World") → #VALUE! sensitive searches.

Standardizing text
Converts text for comparisons or
UPPER to uppercase. =UPPER("hello world") → "HELLO WORLD" reporting.

Normalizing text for

Converts text database imports or
LOWER to lowercase. =LOWER("HELLO WORLD") → "hello world" email addresses.

Converts text
to proper case
(capitalizes the Formatting names,
first letter of titles, or addresses
PROPER each word). =PROPER("hello world") → "Hello World" correctly.
Function Name Description Example Real-Life Use Case

Checks if a
value is a
number and Validating numeric
returns TRUE input in forms or
ISNUMBER or FALSE. =ISNUMBER(123) → TRUE datasets.

Checks if a
value is text
and returns Validating text input
ISTEXT TRUE or FALSE. =ISTEXT("Hello") → TRUE in forms or datasets.

Checks if a cell
is empty and Detecting missing
returns TRUE data in surveys or
ISBLANK or FALSE. =ISBLANK(A1) reports.

Checks if a
value is an Handling errors
error and gracefully in
returns TRUE calculations or
ISERROR or FALSE. =ISERROR(#DIV/0!) → TRUE formulas.

Returns a
specified value
if a formula
results in an
error; Providing user-
otherwise, friendly error
returns the messages instead of
IFERROR formula result. =IFERROR(A1/B1, "Error") raw error codes.

Checks multiple
conditions and
returns a value
corresponding Simplifying complex
to the first grading systems or
TRUE =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, decision-making
IFS condition. "F") processes.
Function Name Description Example Real-Life Use Case

Matches an
expression
against a list of
values and
returns a Simplifying decision-
corresponding =SWITCH(A1, "Red", "Stop", "Green", "Go", making logic (e.g.,
SWITCH result. "Yellow", "Caution", "Unknown") traffic light systems).

Returns a list of
unique values
from a range or Removing duplicates
array (available from a dataset for
in Excel analysis or
UNIQUE 365/2021). =UNIQUE(A1:A10) reporting.

Generates a list
of sequential
numbers in an
array (available Creating numbered
in Excel lists or generating
SEQUENCE 365/2021). =SEQUENCE(5, 1, 1, 1) test data.