Excel Formulas with Examples & Steps
Basic Formulas
Formula: =SUM(A1:A5)
Explanation: Adds values from A1 to A5.
Formula: =AVERAGE(A1:A5)
Explanation: Calculates average of values.
Formula: =MIN(A1:A5)
Explanation: Finds the minimum value.
Formula: =MAX(A1:A5)
Explanation: Finds the maximum value.
Formula: =COUNT(A1:A5)
Explanation: Counts the number of numeric values.
Formula: =COUNTA(A1:A5)
Explanation: Counts the number of non-empty cells.
Text Formulas
Formula: =CONCAT(A1, B1)
Explanation: Joins text in A1 and B1.
Formula: =UPPER(A1)
Explanation: Converts text to uppercase.
Formula: =LOWER(A1)
Explanation: Converts text to lowercase.
Formula: =LEN(A1)
Excel Formulas with Examples & Steps
Explanation: Returns the length of text in A1.
Formula: =TRIM(A1)
Explanation: Removes extra spaces from text.
Logical Formulas
Formula: =IF(A1>50, "Pass", "Fail")
Explanation: Returns Pass if A1>50, else Fail.
Formula: =AND(A1>50, B1<100)
Explanation: Returns TRUE if both conditions are true.
Formula: =OR(A1>50, B1<100)
Explanation: Returns TRUE if any condition is true.
Formula: =NOT(A1>50)
Explanation: Reverses the logical value.
Lookup & Reference
Formula: =VLOOKUP(101, A2:B10, 2, FALSE)
Explanation: Finds value in table vertically.
Formula: =HLOOKUP("Jan", A1:F2, 2, FALSE)
Explanation: Finds value in table horizontally.
Formula: =INDEX(A2:C6, 2, 3)
Explanation: Returns value at 2nd row, 3rd column.
Formula: =MATCH(50, A1:A10, 0)
Explanation: Finds position of 50 in range.
Excel Formulas with Examples & Steps
Date & Time
Formula: =TODAY()
Explanation: Returns current date.
Formula: =NOW()
Explanation: Returns current date and time.
Formula: =DAY(A1)
Explanation: Returns the day from a date.
Formula: =MONTH(A1)
Explanation: Returns the month from a date.
Formula: =YEAR(A1)
Explanation: Returns the year from a date.
Formula: =DATEDIF(A1, B1, "D")
Explanation: Calculates difference in days.
Error Handling
Formula: =IFERROR(A1/B1, "Error")
Explanation: Returns 'Error' if division fails.
Formula: =ISERROR(A1)
Explanation: Returns TRUE if A1 has error.
Gender Count Example
Formula: =COUNTIF(A1:A10, "Male")
Explanation: Counts number of 'Male' entries.
Excel Formulas with Examples & Steps
Formula: =COUNTIF(A1:A10, "Female")
Explanation: Counts number of 'Female' entries.
