TOPICS

BASIC EXCEL FORMULAS EVERYONE SHOULD KNOW

Introduction to Excel Formulas

Excel formulas allow you to perform calculations, analyze data, and automate tasks. They start with an equal sign (=) and can involve numbers, text, and cell references.

Basic Arithmetic Formulas

1. Addition (SUM)
   Formula: =SUM(A1:A5)
   Description: Adds all values from cells A1 to A5. You can also add individual numbers: =SUM(10, 20, 30).
2. Subtraction (-)
   Formula: =A1 - A2
   Description: Subtracts the value in A2 from the value in A1.
3. Multiplication (*)
   Formula: =A1 * A2
   Description: Multiplies the values in A1 and A2.
4. Division (/)
   Formula: =A1 / A2
   Description: Divides the value in A1 by the value in A2.
   

Commonly Used Functions

1. Average (AVERAGE)
   Formula: =AVERAGE(A1:A5)
   Description: Calculates the average of values from A1 to A5.
2. Count (COUNT)
   Formula: =COUNT(A1:A5)
   Description: Counts the number of cells with numeric values in the range A1 to A5.
3. Maximum (MAX)
   Formula: =MAX(A1:A5)
   Description: Finds the highest value in the range A1 to A5.
4. Minimum (MIN)
   Formula: =MIN(A1:A5)
   Description: Finds the lowest value in the range A1 to A5.

Logical Functions

1. If (IF)
   Formula: =IF(A1 > 10, "Yes", "No")
   Description: Checks if A1 is greater than 10. If true, it returns “Yes”; if false, it returns “No”.
2. And (AND)
   Formula: =AND(A1 > 10, B1 < 5)
   Description: Returns TRUE if both conditions are true; otherwise, it returns FALSE.
3. Or (OR)
   Formula: =OR(A1 > 10, B1 < 5)
   Description: Returns TRUE if at least one of the conditions is true.

Text Functions

1. Concatenate (CONCATENATE or &)
   Formula: =CONCATENATE(A1, " ", B1) or =A1 & " " & B1
   Description: Joins text from A1 and B1 with a space in between.
2. Length (LEN)
   Formula: =LEN(A1)
   Description: Returns the number of characters in cell A1.
3. Upper and Lower Case (UPPER, LOWER)
   Formula: =UPPER(A1) or =LOWER(A1)
   Description: Converts the text in A1 to uppercase or lowercase.

Date and Time Functions

1. Today (TODAY)
   Formula: =TODAY()
   Description: Returns the current date.
2. Now (NOW)
   Formula: =NOW()
   Description: Returns the current date and time.
3. Date Difference (DATEDIF)
   Formula: =DATEDIF(A1, B1, "D")
   Description: Calculates the number of days between two dates in A1 and B1.

Lookup Functions

1. VLOOKUP
   Formula: =VLOOKUP(A1, B1:C10, 2, FALSE)
   Description: Looks for the value in A1 within the first column of the range B1:C10 and returns the corresponding value from the second column.
2. HLOOKUP
   Formula: =HLOOKUP(A1, B1:C10, 2, FALSE)
   Description: Similar to VLOOKUP but searches horizontally.

Conclusion

Excel formulas are powerful tools that can simplify your data management tasks. By mastering these basic formulas, you can analyze data more effectively and enhance your productivity. Remember to practice using these formulas in real scenarios to get comfortable with them!

Dive Deep into Python: From Basics to Advanced Data Manipulation – Check it out