Excel is a powerful tool for managing and analyzing data. With its various functions and formulas, Excel can help you perform complex calculations, automate tasks, and visualize data. In this article, we have compiled a list of the 100 most useful Excel formulas, along with examples on 20 tables, ordered in the most useful way.
Table 1: Basic Arithmetic Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
1
SUM
Adds up a range of numbers
=SUM(A2:A6)
Calculating the total sales of a product
2
AVERAGE
Calculates the average of a range of numbers
=AVERAGE(B2:B6)
Finding the average score of students
3
MIN
Finds the minimum value in a range of numbers
=MIN(C2:C6)
Finding the smallest number in a data set
4
MAX
Finds the maximum value in a range of numbers
=MAX(D2:D6)
Finding the largest number in a data set
5
COUNT
Counts the number of cells in a range that contain numbers
=COUNT(E2:E6)
Counting the number of products sold
Table 2: Text Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
6
CONCATENATE
Combines two or more text strings into one
=CONCATENATE(F2,” “,G2)
Joining first and last name
7
LEFT
Extracts a specified number of characters from the left side of a text string
=LEFT(H2,3)
Getting the first three characters of a product code
8
RIGHT
Extracts a specified number of characters from the right side of a text string
=RIGHT(I2,3)
Getting the last three characters of a product code
9
MID
Extracts a specified number of characters from the middle of a text string
=MID(J2,2,3)
Extracting characters from the middle of a product code
10
LEN
Calculates the length of a text string
=LEN(K2)
Finding the length of a product name
Table 3: Date and Time Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
11
TODAY
Returns the current date
=TODAY()
Keeping track of project deadlines
12
NOW
Returns the current date and time
=NOW()
Timestamping entries in a log
13
DATE
Creates a date from year, month, and day values
=DATE(2023,3,15)
Entering a specific date
14
YEAR
Extracts the year from a date
=YEAR(M2)
Finding the year of a transaction date
15
MONTH
Extracts the month from a date
=MONTH(N2)
Finding the month of a transaction date
Table 4: Logical Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
16
IF
Tests a condition and returns one value if the condition is true and another value if the condition is false
=IF(O2>50,”Pass”,”Fail”)
Determining whether a student passed or failed
17
AND
Tests multiple conditions and returns true if all conditions are true
=AND(P2>20,Q2<30)
Checking if two conditions are true
18
OR
Tests multiple conditions and returns true if at least one condition is true
=OR(R2=”Yes”,S2=”Yes”)
Checking if at least one of two conditions is true
Table 5: Lookup and Reference Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
21
VLOOKUP
Searches for a value in the first column of a table and returns a value in the same row from a specified column
=VLOOKUP(W2,X2:Y6,2,FALSE)
Looking up a product price based on its code
22
HLOOKUP
Searches for a value in the first row of a table and returns a value in the same column from a specified row
=HLOOKUP(Z2,A2:F3,2,FALSE)
Looking up a student’s score based on their name
23
INDEX
Returns a value from a specified position in an array or table
=INDEX(A2:A6,4)
Retrieving the fourth value in a list
24
MATCH
Searches for a value in an array or table and returns its position
=MATCH(B2,A2:A6,0)
Finding the position of a value in a list
25
OFFSET
Returns a range of cells that is a specified number of rows and columns away from a starting cell
=OFFSET(C2,1,1)
Selecting a range of cells that is one row and one column away from a starting cell
Table 6: Math and Trigonometry Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
26
ROUND
Rounds a number to a specified number of digits
=ROUND(D2,2)
Rounding a price to two decimal places
27
CEILING
Rounds a number up to the nearest multiple of a specified value
=CEILING(E2,5)
Rounding up a quantity to the nearest 5
28
FLOOR
Rounds a number down to the nearest multiple of a specified value
=FLOOR(F2,10)
Rounding down a time to the nearest 10 minutes
29
ABS
Returns the absolute value of a number
=ABS(G2)
Finding the distance between two values
30
SQRT
Returns the square root of a number
=SQRT(H2)
Calculating the length of a side of a square
Table 7: Statistical Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
31
STDEV
Calculates the standard deviation of a range of numbers
=STDEV(I2:I6)
Measuring the variability of a data set
32
MEDIAN
Calculates the median value in a range of numbers
=MEDIAN(J2:J6)
Finding the middle value of a data set
33
MODE
Calculates the mode value in a range of numbers
=MODE(K2:K6)
Finding the most common value in a data set
34
QUARTILE
Calculates the quartile values in a range of numbers
=QUARTILE(L2:L6,2)
Finding the second quartile value of a data set
35
PERCENTILE
Calculates the percentile rank of a value in a range of numbers
=PERCENTILE(M2:M6,80)
Finding the 80th percentile rank of a data set
Table 8: Financial Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
36
PMT
Calculates the payment for a loan based on constant payments and a constant interest rate
=PMT(N2/12,O2*12,-P2)
Calculating monthly payments for a loan
37
PV
Calculates the present value of an investment based on a constant interest rate and a constant payment
=PV(Q2/12,R2*12,-S2)
Determining the present value of a retirement fund
38
FV
Calculates the future value of an investment based on a constant interest rate and a constant payment
=FV(T2/12,U2*12,-V2)
Estimating the future value of a savings plan
39
NPV
Calculates the net present value of an investment based on a series of cash flows and a constant interest rate
=NPV(W2,X2:Y6)
Evaluating the profitability of a business project
40
IRR
Calculates the internal rate of return of an investment based on a series of cash flows
=IRR(Z2:Z6)
Measuring the profitability of an investment opportunity
Table 9: Engineering Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
41
CONVERT
Converts a measurement from one unit to another
=CONVERT(AA2,”m”,”ft”)
Converting meters to feet
42
POWER
Raises a number to a specified power
=POWER(AB2,2)
Squaring a number
43
PRODUCT
Multiplies a range of numbers together
=PRODUCT(AC2:AC6)
Calculating the total cost of multiple items
44
SUMIF
Adds up a range of numbers based on a specified condition
=SUMIF(AD2:AD6,”Yes”,AE2:AE6)
Calculating the total revenue from products that are in stock
45
AVERAGEIF
Calculates the average of a range of numbers based on a specified condition
=AVERAGEIF(AF2:AF6,”>50″,AG2:AG6)
Finding the average score of students who scored more than 50
Table 10: Array Formulas
Serial Number
Formula
Unique SEO-optimized description
Example
Use Case Examples
46
ARRAY
Performs a calculation on a range of cells or an array of cells
{=SUM(AH2:AH6)}
Calculating the sum of a range of cells using an array formula
47
TRANSPOSE
Transposes a range of cells or an array of cells
=TRANSPOSE(AI2:AJ3)
Flipping the orientation of a table
48
FREQUENCY
Calculates the frequency distribution of a range of values
=FREQUENCY(AK2:AK6,AL2:AL5)
Creating a histogram of test scores
49
MMULT
Calculates the matrix product of two arrays
=MMULT(AM2:AO4,AP2:AQ4)
Performing matrix multiplication
50
SUMPRODUCT
Multiplies corresponding values in two or more arrays and returns the sum of the products
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
