How to use the most common formulas in Excel
Mastering Excel's formulas is essential for efficient data analysis and management. Here's a basic list of some of the most commonly used formulas:
-
SUM: Adds all the numbers in a specified range.
-
Syntax:
=SUM(number1, [number2], ...)
-
Example:
=SUM(A1:A5) calculates the total of values from cell A1 to A5.
-
AVERAGE: Calculates the average of numbers in a range.
-
Syntax:
=AVERAGE(number1, [number2], ...)
-
Example:
=AVERAGE(B1:B10) computes the average of values from cell B1 to B10.
-
COUNT: Counts the number of cells that contain numbers.
-
Syntax:
=COUNT(value1, [value2], ...)
-
Example:
=COUNT(C1:C20) counts the number of cells with numeric values between C1 and C20.
-
COUNTA: Counts the number of non-empty cells.
-
Syntax:
=COUNTA(value1, [value2], ...)
-
Example:
=COUNTA(D1:D20) counts all non-empty cells from D1 to D20.
-
IF: Performs a logical test and returns one value for a TRUE result and another for a FALSE result.
-
Syntax:
=IF(logical_test, value_if_true, value_if_false)
-
Example:
=IF(E1>50, "Pass", "Fail") checks if the value in E1 is greater than 50; if true, returns "Pass", otherwise "Fail".
-
VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
-
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
-
Example:
=VLOOKUP(F1, A1:C10, 3, FALSE) looks for the value in F1 within the range A1:C10 and returns the corresponding value from the third column.
-
HLOOKUP: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
-
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
-
Example:
=HLOOKUP(G1, A1:Z5, 4, TRUE) searches for the value in G1 across the first row of A1:Z5 and returns the value from the fourth row in the matching column.
-
MATCH: Returns the relative position of an item in an array that matches a specified value.
-
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
-
Example:
=MATCH("Apples", B1:B10, 0) finds the position of "Apples" within the range B1:B10.
-
INDEX: Returns the value of a cell in a specified row and column within a range.
-
Syntax:
=INDEX(array, row_num, [column_num])
-
Example:
=INDEX(A1:C10, 2, 3) retrieves the value from the second row and third column within the range A1:C10.
-
CONCATENATE (or CONCAT in newer versions): Joins two or more text strings into one string.
-
Syntax:
=CONCATENATE(text1, [text2], ...)
-
Example:
=CONCATENATE("Hello, ", "World!") results in "Hello, World!".
-
LEN: Returns the number of characters in a text string.
-
Syntax:
=LEN(text)
-
Example:
=LEN("Excel") returns 5.
-
TRIM: Removes all spaces from a text string except for single spaces between words.
-
Syntax:
=TRIM(text)
-
Example:
=TRIM(" Hello World ") results in "Hello World".
-
LEFT: Extracts a specified number of characters from the start (left) of a text string.
-
Syntax:
=LEFT(text, [num_chars])
-
Example:
=LEFT("Excel", 2) returns "Ex".
-
RIGHT: Extracts a specified number of characters from the end (right) of a text string.
-
Syntax:
=RIGHT(text, [num_chars])
-
Example:
=RIGHT("Excel", 3) returns "cel".
-
MID: Returns a specific number of characters from a text string, starting at the position you specify.
-
Syntax:
=MID(text, start_num, num_chars)
-
Example:
=MID("Excel", 2, 3) returns "xce".