Most useful functions in MS Excel

1. SUM:

Description: Adds together all numbers in a range of cells.

Example: =SUM(A1:A10) adds the values in cells A1 through A10.

2. IF:

Description: Checks a condition and returns one value if the condition is true and another value if it is false.

Example: =IF(A1>50, “Pass”, “Fail”) returns “Pass” if the value in A1 is greater than 50, otherwise it returns “Fail”.

3. VLOOKUP:

Description: Looks for a value in the first column of a range and returns a value in the same row from another column.

Example: =VLOOKUP(A1, B2:D10, 3, FALSE) looks up the value in A1 in the first column of the range B2:D10 and returns the corresponding value from the third column.

4. INDEX and MATCH:

Description: Used together, INDEX returns the value of a cell at a given position, and MATCH returns the relative position of a value in a range.

Example: =INDEX(B1:B10, MATCH(“Product A”, A1:A10, 0)) finds “Product A” in A1:A10 and returns the corresponding value from the B column.

5. COUNTIF:

Description: Counts the number of cells that meet a specified condition.

Example: =COUNTIF(A1:A10, “>50”) counts how many cells in the range A1:A10 contain values greater than 50.

6. CONCATENATE (or TEXTJOIN in newer versions):

Description: Combines the contents of two or more cells into one cell.

Example: =CONCATENATE(A1, ” “, B1) combines the values of cells A1 and B1 with a space in between.

7. TEXT:

Description: Formats numbers or dates into text in a specified format.

Example: =TEXT(A1, “mm/dd/yyyy”) converts the date in A1 to the format “MM/DD/YYYY”.

8. LEFT, RIGHT, MID:

Description: Extracts a specific number of characters from a string, either from the left, right, or middle.

Example: =LEFT(A1, 5) extracts the first five characters from the value in A1.

9. SUMIF:

Description: Adds up the values in a range that meet a specified condition.

Example: =SUMIF(A1:A10, “>50”, B1:B10) adds the values in B1:B10 where the corresponding value in A1:A10 is greater than 50.

10. CHARTS and PIVOT TABLES (not a single function but extremely useful):

Description: Pivot tables summarize large data sets, and charts can visually represent the data for better understanding.


These functions enhance productivity and are crucial for data analysis in Excel.