Common Excel Formulas

 

Excel is a powerful tool widely used for data analysis, financial modeling, and much more. Understanding common Excel formulas is essential for efficiently managing and analyzing data. Here’s a guide to some of the most frequently used Excel formulas:

Basic Formulas

1. SUM

   • Purpose: Adds up all the numbers in a range of cells.

   • Example: =SUM(A1:A10) adds all numbers from cell A1 to A10.

   
   

2. AVERAGE

   • Purpose: Calculates the average of numbers in a specified range.

   • Example: =AVERAGE(B1:B10) finds the average of the numbers in cells B1 to B10.

   
   

3. MIN and MAX

   • Purpose: Returns the smallest and largest number in a set of values.

   • Example:

     • =MIN(C1:C10) finds the smallest number in the range C1 to C10.

     • =MAX(C1:C10) finds the largest number in the range C1 to C10.

     
     

4. COUNT

   • Purpose: Counts the number of cells that contain numbers.

   • Example: =COUNT(D1:D10) counts the number of cells with numeric entries in the range D1 to D10.

Text Functions

1. CONCATENATE

   • Purpose: Joins several text strings into one string.

   • Example: =CONCATENATE(E1, " ", F1) combines the text in cells E1 and F1 with a space in between.

   
   

2. LEFT, RIGHT, and MID

   • Purpose: Extracts a substring from a text string.

   • Example:

     • =LEFT(G1, 3) returns the first three characters from the string in G1.

     • =RIGHT(G1, 3) returns the last three characters from the string in G1.

     • =MID(G1, 2, 4) returns four characters starting from the second character of the string in G1.

     
     

3. LEN

   • Purpose: Returns the number of characters in a text string.

   • Example: =LEN(H1) gives the length of the string in cell H1.

Logical Functions

1. IF

   • Purpose: Performs a logical test and returns one value for a TRUE result, and another for a FALSE result.

   • Example: =IF(I1 > 100, "Above 100", "100 or below") checks if the value in I1 is greater than 100.

   
   

2. AND, OR

   • Purpose: Used to test multiple conditions.

   • Example:

     • =AND(J1 > 100, K1 < 200) returns TRUE if both conditions are met.

     • =OR(J1 > 100, K1 < 200) returns TRUE if at least one condition is met.

Lookup Functions

1. VLOOKUP

   • Purpose: Searches for a value in the first column of a table and returns a value in the same row from a specified column.

   • Example: =VLOOKUP(L1, M1:N10, 2, FALSE) looks for the value in L1 within the range M1:N10 and returns the corresponding value from the second column.

   
   

2. HLOOKUP

   • Purpose: Similar to VLOOKUP, but searches in the first row and returns a value from a specified row.

   • Example: =HLOOKUP(O1, P1:Q10, 3, FALSE) searches for the value in O1 within the range P1:Q10 and returns the corresponding value from the third row.

These formulas form the backbone of Excel functionality and serve as a foundation for more advanced operations and analysis. By mastering these, you can greatly enhance your productivity and capability in Excel.