Excel Essential Formulas

SUM

• Use: Adds a range of numbers.
• Example: =SUM(A1:A10) sums all values in the range A1 to A10.

PERCENTAGE

• Use: Converts a number to a percentage.
• Example: =A1 / B1 and format the result as a percentage.

RANK

• Use: Determines the rank of a number in a list.
• Example: =RANK(A1, A$1:A$10) ranks the value in A1 within the range A1 to A10.

IF

• Use: Performs a logical test and returns one value if true, and another if false.
• Example: =IF(A1 > 50, "Pass", "Fail") returns “Pass” if A1 is greater than 50.

MAX

• Use: Returns the largest value in a range.
• Example: =MAX(A1:A10) returns the highest value in the range.

MIN

• Use: Returns the smallest value in a range.
• Example: =MIN(A1:A10) returns the lowest value in the range.

AVERAGE

• Use: Calculates the average of a range of numbers.
• Example: =AVERAGE(A1:A10) returns the average value in the range.

COUNT

• Use: Counts the number of cells containing numbers in a range.
• Example: =COUNT(A1:A10) counts the number of numeric values in the range.

COUNTIF

• Use: Counts cells that meet a single criterion.
• Example: =COUNTIF(A1:A10, ">50") counts cells with values greater than 50.

SUMIF

• Use: Adds cells that meet a single criterion.
• Example: =SUMIF(A1:A10, ">50") sums values greater than 50.

MULTIPLY

• Use: Multiplies numbers or ranges.
• Example: =A1 * B1 multiplies the values in A1 and B1.

DIVISION

• Use: Divide one number by another.
• Example: =A1 / B1 divides the value in A1 by the value in B1.

SUBTRACT

• Use: Subtracts one number from another.
• Example: =A1 - B1 subtracts the value in B1 from the value in A1.

PRODUCT

• Use: Multiplies all numbers in a range.
• Example: =PRODUCT(A1:A10) multiplies all values in the range.

UPPER

• Use: Converts text to uppercase.
• Example: =UPPER(A1) converts the text in A1 to uppercase.

LOWER

• Use: Converts text to lowercase.
• Example: =LOWER(A1) converts the text in A1 to lowercase.

PROPER

• Use: Capitalizes the first letter of each word in text.
• Example: =PROPER(A1) capitalizes the first letter of each word in A1.

LEFT

• Use: Extracts a specified number of characters from the start of a text string.
• Example: =LEFT(A1, 5) extracts the first 5 characters from the text in A1.

RIGHT

• Use: Extracts a specified number of characters from the end of a text string.
• Example: =RIGHT(A1, 3) extracts the last 3 characters from the text in A1.

MID

• Use: Extracts characters from the middle of a text string.
• Example: =MID(A1, 2, 5) extracts 5 characters from A1 starting at the 2nd character.

POWER

• Use: Returns a number raised to a specified power.
• Example: =POWER(A1, 3) raises the value in A1 to the power of 3.

ROMAN

• Use: Converts a number to Roman numerals.
• Example: =ROMAN(1987) converts the number 1987 to Roman numerals.

SQRT

• Use: Returns the square root of a number.
• Example: =SQRT(A1) returns the square root of the value in A1.

CONCATENATE

• Use: Combines text from multiple cells into one cell.
• Example: =CONCATENATE(A1, " ", B1) combines text from A1 and B1 with a space in between.

TRIM

• Use: Removes extra spaces from text.
• Example: =TRIM(A1) removes extra spaces from the text in A1.