Advance Excel All 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 of another number.
• Example: =A1 / B1 where the result can be formatted 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, another if false.
• Example: =IF(A1 > 50, "Pass", "Fail") returns “Pass” if A1 is greater than 50.

IFS

• Use: Evaluates multiple conditions and returns values based on the first true condition.
• Example: =IFS(A1 > 90, "A", A1 > 80, "B", A1 > 70, "C") assigns a grade based on the value in A1.

AND

• Use: Checks if all conditions are true.
• Example: =AND(A1 > 10, B1 < 20) returns TRUE if both conditions are met.

OR

• Use: Checks if at least one condition is true.
• Example: =OR(A1 > 10, B1 < 20) returns TRUE if at least one condition is met.

IFERROR

• Use: Returns a custom value if a formula results in an error.
• Example: =IFERROR(A1/B1, "Error") returns “Error” if there is a division error.

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.

COUNTBLANK

• Use: Counts the number of empty cells in a range.
• Example: =COUNTBLANK(A1:A10) counts blank cells in the range.

COUNTIF

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

COUNTIFS

• Use: Counts cells that meet multiple criteria.
• Example: =COUNTIFS(A1:A10, ">50", B1:B10, "<100") counts cells meeting both criteria.

SUMIF

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

SUMIFS

• Use: Adds cells that meet multiple criteria.
• Example: =SUMIFS(A1:A10, B1:B10, "Completed", C1:C10, "<100") sums values based on criteria.

AVERAGEIF

• Use: Calculates the average of cells that meet a single criterion.
• Example: =AVERAGEIF(A1:A10, ">50") averages values greater than 50.

AVERAGEIFS

• Use: Calculates the average of cells that meet multiple criteria.
• Example: =AVERAGEIFS(A1:A10, B1:B10, "Completed", C1:C10, "<100") averages values based on criteria.

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.

LEN

• Use: Counts the number of characters in a text string.
• Example: =LEN(A1) returns the length of the text in A1.

TRIM

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

CONCATENATE / CONCAT

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

DATE

• Use: Creates a date from year, month, and day.
• Example: =DATE(2024, 9, 8) returns the date September 8, 2024.

TIME

• Use: Creates a time from hour, minute, and second.
• Example: =TIME(14, 30, 0) returns the time 14:30:00.

DATEDIF

• Use: Calculates the difference between two dates in days, months, or years.
• Example: =DATEDIF(A1, B1, "D") calculates the number of days between dates in A1 and B1.

UNIQUE (Excel 365)

• Use: Returns unique values from a range.
• Example: =UNIQUE(A1:A10) returns unique values from the range A1.

SORT (Excel 365)

• Use: Sorts a range of values dynamically.
• Example: =SORT(A1:A10) sorts values in the range A1.

PRODUCT

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

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.

POWER

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

VLOOKUP

• Use: Searches for a value in the first column of a range and returns a value in the same row from a specified column.
• Example: =VLOOKUP("Apple", A2:D10, 3, FALSE) looks for “Apple” in the first column and returns the corresponding value from the third column.

HLOOKUP

• Use: Searches for a value in the first row of a range and returns a value in the same column from a specified row.
• Example: =HLOOKUP("Q1", A1:D5, 3, FALSE) looks for “Q1” in the first row and returns the value from the third row.

INDEX

• Use: Returns the value of a cell at a specific position within a range.
• Example: =INDEX(B2:B10, 4) returns the value from the fourth row of the range.

MATCH

• Use: Returns the relative position of an item in a range that matches a specified value.
• Example: =MATCH("Orange", A2:A10, 0) returns the position of “Orange” in the list.

XLOOKUP (Excel 365 or newer):

• Use: Searches for a value in a range and returns a corresponding value from another range; more versatile than VLOOKUP.
• Example: =XLOOKUP("Item", A2:A10, B2:B10) finds “Item” in column A and returns the corresponding value from column B.