MS EXCEL FORMULAS BASIC TO ADVANCE

BASIC ARITHMETIC FORMULAS

1. SUM
o =SUM(A1:A10)
o Description: Adds all the numbers in the range from A1 to A10.
2. AVERAGE
o =AVERAGE(A1:A10)
o Description: Calculates the average of the numbers in the range from A1 to A10.
3. MIN
o =MIN(A1:A10)
o Description: Returns the smallest number in the range from A1 to A10.
4. zMAX
o =MAX(A1:A10)
o Description: Returns the largest number in the range from A1 to A10.
5. COUNT
o =COUNT(A1:A10)
o Description: Counts the number of cells that contain numbers in the range from A1 to A10.
6. COUNTA
o =COUNTA(A1:A10)
o Description: Counts the number of non-empty cells in the range from A1 to A10.
7. COUNTIF
o =COUNTIF(A1:A10, ">10")
o Description: Counts the number of cells in the range that meet a specific condition (e.g., greater
than 10).
8. SUMIF
o =SUMIF(A1:A10, ">10")
o Description: Adds the numbers in the range that meet a specific condition (e.g., greater than 10).
9. PRODUCT
o =PRODUCT(A1:A10)
o Description: Multiplies all the numbers in the range from A1 to A10.
10. ROUND
o =ROUND(A1, 2)
o Description: Rounds the number in A1 to 2 decimal places.

Logical Functions
1. IF
o Formula: =IF(A1 > 10, "Yes", "No")
o Description: Returns one value if a condition is TRUE and another value if FALSE.
2. AND
o Formula: =AND(A1 > 10, B1 < 5)
o Description: Returns TRUE if all conditions are TRUE.
3. OR
o Formula: =OR(A1 > 10, B1 < 5)
o Description: Returns TRUE if at least one condition is TRUE.
4. NOT
o Formula: =NOT(A1 > 10)
o Description: Reverses the logical value (TRUE becomes FALSE and vice versa).
5. IFERROR
o Formula: =IFERROR(A1/B1, "Error")
o Description: Returns a value if there is no error, or a specified value if there is an error.

The Lahore Shorthand Academy 0300-4986610 21, Babar Block, Garden Town,
Mentor: Muhammad Bilawal 03000-755-453
Lookup & Reference Functions
1. VLOOKUP
o Formula: =VLOOKUP(A1, B1:D10, 2, FALSE)
o Description: Searches for a value in the first column of a range and returns a value from a
specified column in the same row.
2. HLOOKUP
o Formula: =HLOOKUP(A1, B1:D10, 2, FALSE)
o Description: Searches for a value in the first row of a range and returns a value from a specified
row.
3. INDEX
o Formula: =INDEX(A1:C10, 2, 3)
o Description: Returns the value of a cell at the intersection of a specific row and column in a
range.
4. MATCH
o Formula: =MATCH(A1, B1:B10, 0)
o Description: Searches for a value in a range and returns the relative position of the value.
5. INDIRECT
o Formula: =INDIRECT("A" & B1)
o Description: Returns the reference specified by a text string (e.g., combines A with the value in
B1 to reference a cell).
6. CHOOSE
o Formula: =CHOOSE(A1, "Red", "Blue", "Green")
o Description: Selects a value from a list of options based on the value in A1.
7. OFFSET
o Formula: =OFFSET(A1, 2, 3)
o Description: Returns a reference to a cell that is a specified number of rows and columns from a
starting cell.

TEXT FUNCTIONS
1. CONCATENATE (or CONCAT in newer versions)
o Formula: =CONCATENATE(A1, " ", B1)
o Description: Joins two or more text strings together.
2. TEXT
o Formula: =TEXT(A1, "mm/dd/yyyy")
o Description: Converts a number to text in a specified format.
3. LEFT
o Formula: =LEFT(A1, 5)
o Description: Extracts the first 5 characters from the left side of the string in A1.
4. RIGHT
o Formula: =RIGHT(A1, 3)
o Description: Extracts the last 3 characters from the right side of the string in A1.
5. MID
o Formula: =MID(A1, 2, 4)
o Description: Extracts 4 characters from the string in A1, starting at the 2nd position.
6. LEN
o Formula: =LEN(A1)
o Description: Returns the length (number of characters) of the text string in A1.
7. TRIM
o Formula: =TRIM(A1)
o Description: Removes leading and trailing spaces from the text in A1.
8. UPPER
o Formula: =UPPER(A1)
o Description: Converts the text in A1 to uppercase.
9. LOWER
o Formula: =LOWER(A1)
o Description: Converts the text in A1 to lowercase.
10. PROPER
o Formula: =PROPER(A1)
o Description: Converts the text in A1 to proper case (capitalizes the first letter of each word).

The Lahore Shorthand Academy 0300-4986610 21, Babar Block, Garden Town,
Mentor: Muhammad Bilawal 03000-755-453
Date & Time Functions
1. TODAY
o Formula: =TODAY()
o Description: Returns the current date.
2. NOW
o Formula: =NOW()
o Description: Returns the current date and time.
3. DATE
o Formula: =DATE(2024, 12, 18)
o Description: Returns the date based on the specified year, month, and day.
4. DATEDIF
o Formula: =DATEDIF(A1, B1, "Y")
o Description: Returns the difference between two dates in years, months, or days.
5. DAY
o Formula: =DAY(A1)
o Description: Returns the day of the month from a date.
6. MONTH
o Formula: =MONTH(A1)
o Description: Returns the month from a date.
7. YEAR
o Formula: =YEAR(A1)
o Description: Returns the year from a date.
8. WEEKDAY
o Formula: =WEEKDAY(A1)
o Description: Returns the day of the week as a number (1 = Sunday, 7 = Saturday).
9. TIME
o Formula: =TIME(14, 30, 0)
o Description: Returns a time value based on the hour, minute, and second provided.

Financial Functions
1. PMT
o Formula: =PMT(interest_rate, periods, present_value)
o Description: Calculates the periodic payment for a loan based on constant payments and a
constant interest rate.
2. FV
o Formula: =FV(interest_rate, periods, payment, present_value)
o Description: Calculates the future value of an investment based on periodic, constant payments
and a constant interest rate.

Array & Statistical Functions

1. TRANSPOSE
o Formula: =TRANSPOSE(A1:B10)
o Description: Changes the orientation of a range of cells (rows become columns and vice versa).
2. FREQUENCY
o Formula: =FREQUENCY(A1:A10, B1:B5)
o Description: Returns a frequency distribution as an array.
3. STDEV
o Formula: =STDEV(A1:A10)
o Description: Estimates the standard deviation of a population based on a sample.
4. MEDIAN
o Formula: =MEDIAN(A1:A10)
o Description: Returns the median (middle value) of a range of numbers.

The Lahore Shorthand Academy 0300-4986610 21, Babar Block, Garden Town,
Mentor: Muhammad Bilawal 03000-755-453