Common Excel Functions
1. Mathematical & Statistical Functions
SUM: Adds up all the numbers in a range.
Example: =SUM(A1:A10)
AVERAGE: Calculates the average of a range.
Example: =AVERAGE(A1:A10)
COUNT: Counts the number of numerical entries in a range.
Example: =COUNT(A1:A10)
COUNTA: Counts non-empty cells.
Example: =COUNTA(A1:A10)
ROUND: Rounds a number to specified decimal places.
Example: =ROUND(A1, 2)
MAX: Finds the maximum value in a range.
Example: =MAX(A1:A10)
MIN: Finds the minimum value in a range.
Example: =MIN(A1:A10)
2. Logical Functions
IF: Returns values based on a condition.
Example: =IF(A1>50, "Pass", "Fail")
AND: Checks if all conditions are true.
Example: =AND(A1>50, B1<100)
OR: Checks if at least one condition is true.
Example: =OR(A1>50, B1<100)
NOT: Reverses a condition.
Example: =NOT(A1>50)
3. Lookup & Reference Functions
VLOOKUP: Searches for a value in a vertical range.
Example: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP: Searches for a value in a horizontal range.
Example: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
INDEX: Returns the value of a cell at a given position.
Example: =INDEX(A1:C10, 2, 3)
ARJUN SIR 9784480476
MATCH: Finds the position of a value in a range.
Example: =MATCH(lookup_value, lookup_array, [match_type])
OFFSET: Creates a dynamic range.
Example: =OFFSET(reference, rows, cols, [height], [width])
4. Text Functions
CONCATENATE (or TEXTJOIN): Combines text from multiple cells.
Example: =CONCATENATE(A1, " ", B1)
LEFT: Extracts characters from the beginning of text.
Example: =LEFT(A1, 5)
RIGHT: Extracts characters from the end of text.
Example: =RIGHT(A1, 3)
MID: Extracts characters from the middle of text.
Example: =MID(A1, 3, 4)
LEN: Returns the length of text.
Example: =LEN(A1)
TRIM: Removes unnecessary spaces.
Example: =TRIM(A1)
SEARCH: Finds the position of a substring.
Example: =SEARCH("a", A1)
5. Date & Time Functions
TODAY: Returns the current date.
Example: =TODAY()
NOW: Returns the current date and time.
Example: =NOW()
DATEDIF: Calculates the difference between two dates.
Example: =DATEDIF(A1, B1, "Y")
DAY: Extracts the day from a date.
Example: =DAY(A1)
MONTH: Extracts the month from a date.
Example: =MONTH(A1)
YEAR: Extracts the year from a date.
Example: =YEAR(A1)
ARJUN SIR 9784480476
6. Financial Functions
PMT: Calculates the monthly payment for a loan.
Example: =PMT(rate, nper, pv)
FV: Calculates the future value of an investment.
Example: =FV(rate, nper, pmt)
NPV: Calculates the Net Present Value of an investment.
Example: =NPV(rate, value1, value2, ...)
7. Data Cleaning & Manipulation
TEXT: Converts a number into a specific format.
Example: =TEXT(A1, "MM/DD/YYYY")
VALUE: Converts text into a number.
Example: =VALUE(A1)
CLEAN: Removes non-printable characters.
Example: =CLEAN(A1)
8. Array Functions
UNIQUE: Returns unique values from a range.
Example: =UNIQUE(A1:A10)
SORT: Sorts data in ascending or descending order.
Example: =SORT(A1:A10)
FILTER: Filters data based on criteria.
Example: =FILTER(A1:A10, A1:A10>50)
ARJUN SIR 9784480476
