Some commonly used Excel formulas with examples

Here are some commonly used Excel formulas along with examples and explanations. Excel formulas are powerful tools that enable users to perform complex calculations, analyze data, and automate repetitive tasks with ease. At the core of Excel's functionality are its formulas, which allow users to manipulate data efficiently. By mastering some of the most commonly used formulas, users can significantly enhance their productivity and accuracy when working with spreadsheets. These formulas range from basic arithmetic operations to more advanced functions, such as data analysis and text manipulation. Understanding how to use these formulas effectively can transform how you handle data, making it easier to extract meaningful insights and make informed decisions. 

Among the most frequently used Excel formulas are SUM, AVERAGE, IF, VLOOKUP, and COUNTIF. Each of these formulas serves a specific purpose, allowing users to perform tasks such as adding up numbers, calculating averages, performing conditional operations, looking up values, and counting specific entries within a dataset. For example, the SUM formula is used to add a range of numbers, while AVERAGE calculates their mean. The IF function allows for conditional logic, enabling users to create dynamic and responsive spreadsheets. Meanwhile, VLOOKUP and COUNTIF are essential for searching data and counting entries that meet specific criteria. By incorporating these formulas into their workflows, users can streamline their processes and improve their ability to manage and analyze data effectively.

1. SUM

Formula: =SUM(number1, [number2], ...)

Example:

A1: 10

A2: 20

A3: 30

Formula in A4: =SUM(A1:A3)

Result in A4: 60

Explanation: The SUM function adds all the numbers in the specified range. Here, it adds the values in cells A1, A2, and A3.

2. AVERAGE

Formula: =AVERAGE(number1, [number2], ...)

Example:

B1: 10

B2: 20

B3: 30

Formula in B4: =AVERAGE(B1:B3)

Result in B4: 20

Explanation: The AVERAGE function calculates the mean of the numbers in the specified range. Here, it finds the average of the values in cells B1, B2, and B3.

3. IF

Formula: =IF(logical_test, value_if_true, value_if_false)

Example:

C1: 85 (Score)

Formula in C2: =IF(C1>=50, "Pass", "Fail")

Result in C2: Pass

Explanation: The IF function checks whether a condition is met and returns one value if TRUE, and another value if FALSE. Here, it checks if the score in C1 is greater than or equal to 50. If true, it returns "Pass", otherwise "Fail".

4. VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

D1: Product ID

D2: 102

E1: Product Name

E2: Apple

F1: Product Price

F2: 1.50

Formula in G1: =VLOOKUP(102, D1:F2, 2, FALSE)

Result in G1: Apple

Explanation: The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from another column. Here, it looks for the product ID 102 in column D and returns the corresponding product name from column E.

5. CONCATENATE (or CONCAT)

Formula: =CONCATENATE(text1, [text2], ...) or =CONCAT(text1, [text2], ...)

Example:

H1: John

I1: Doe

Formula in J1: =CONCATENATE(H1, " ", I1)

Result in J1: John Doe

Explanation: The CONCATENATE (or CONCAT) function joins two or more text strings into one string. Here, it combines the text in H1 and I1 with a space in between.

6. COUNTIF

Formula: =COUNTIF(range, criteria)

Example:

K1: Passed

K2: Failed

K3: Passed

K4: Passed

Formula in L1: =COUNTIF(K1:K4, "Passed")

Result in L1: 3

Explanation: The COUNTIF function counts the number of cells within a range that meet the given condition. Here, it counts the number of cells that contain the text "Passed" in the range K1.

7. SUMIF

Formula: =SUMIF(range, criteria, [sum_range])

Example:

M1: 10

M2: 20

M3: 10

N1: 5

N2: 15

N3: 10

Formula in O1: =SUMIF(M1:M3, 10, N1:N3)

Result in O1: 15

Explanation: The SUMIF function adds the cells specified by a given condition or criteria. Here, it sums the values in N1

where the corresponding value in M1

is 10.

8. LEFT

Formula: =LEFT(text, [num_chars])

Example:

P1: "ExcelDax"

Formula in Q1: =LEFT(P1, 5)

Result in Q1: Excel

Explanation: The LEFT function returns the first num_chars characters in a text string. Here, it extracts the first 5 characters from the text in P1.

9. RIGHT

Formula: =RIGHT(text, [num_chars])

Example:

R1: "Analytics"

Formula in S1: =RIGHT(R1, 4)

Result in S1: ytics

Explanation: The RIGHT function returns the last num_chars characters in a text string. Here, it extracts the last 4 characters from the text in R1.

10. MID

Formula: =MID(text, start_num, num_chars)

Example:

T1: "ExcelDax"

Formula in U1: =MID(T1, 2, 4)

Result in U1: xcel

Explanation: The MID function returns a specific number of characters from a text string, starting at the position you specify. Here, it extracts 4 characters from the text in T1, starting at the 2nd character.

These formulas cover a wide range of functions useful for various data analysis tasks in Excel.