Here is a comprehensive list of supported functions, categorized by type.
1. Math & Trigonometric Functions
| Function | Description |
|---|---|
| ABS(x) | Returns the absolute value of a number. |
| ACOS(x) | Returns the arccosine (in radians). |
| ACOSH(x) | Returns the hyperbolic arccosine. |
| ASIN(x) | Returns the arcsine (in radians). |
| ASINH(x) | Returns the hyperbolic arcsine. |
| ATAN(x) | Returns the arctangent (in radians). |
| ATAN2(x, y) | Returns the arctangent of (x,y). |
| ATANH(x) | Returns the hyperbolic arctangent. |
| CEILING(x, significance) | Rounds a number up to the nearest multiple of significance. |
| COMBIN(n, k) | Returns the number of combinations for n items taken k at a time. |
| COS(x) | Returns the cosine of an angle (in radians). |
| COSH(x) | Returns the hyperbolic cosine. |
| DEGREES(x) | Converts radians to degrees. |
| EVEN(x) | Rounds a number up to the nearest even integer. |
| EXP(x) | Returns e raised to the power x. |
| FACT(x) | Returns the factorial of a number. |
| FLOOR(x, significance) | Rounds a number down to the nearest multiple of significance. |
| GCD(x1, x2, ...) | Returns the greatest common divisor. |
| INT(x) | Rounds a number down to the nearest integer. |
| LCM(x1, x2, ...) | Returns the least common multiple. |
| LN(x) | Returns the natural logarithm. |
| LOG(x, base) | Returns the logarithm of a number to a specified base. |
| LOG10(x) | Returns the base-10 logarithm. |
| MOD(x, y) | Returns the remainder after division. |
| ODD(x) | Rounds a number up to the nearest odd integer. |
| PI() | Returns the value of π (~3.14159). |
| POWER(x, y) | Returns x raised to the power y. |
| RADIANS(x) | Converts degrees to radians. |
| RAND() | Returns a random number between 0 and 1. |
| RANDBETWEEN(min, max) | Returns a random integer between min and max. |
| ROUND(x, digits) | Rounds a number to a specified number of digits. |
| ROUNDDOWN(x, digits) | Rounds a number down. |
| ROUNDUP(x, digits) | Rounds a number up. |
| SIGN(x) | Returns the sign of a number (-1, 0, or 1). |
| SIN(x) | Returns the sine of an angle (in radians). |
| SINH(x) | Returns the hyperbolic sine. |
| SQRT(x) | Returns the square root. |
| SQRTPI(x) | Returns the square root of (x * π). |
| SUM(x1, x2, ...) | Adds all numbers in a range. |
| SUMIF(range, criteria, [sum_range]) | Adds numbers that meet a condition. |
| SUMIFS(sum_range, criteria_range1, criteria1, ...) | Adds numbers that meet multiple conditions. |
| SUMPRODUCT(array1, array2, ...) | Returns the sum of the products of arrays. |
| TAN(x) | Returns the tangent of an angle (in radians). |
| TANH(x) | Returns the hyperbolic tangent. |
| TRUNC(x, digits) | Truncates a number to an integer (removes decimal part). |
2. Statistical Functions
| Function | Description |
|---|---|
| AVERAGE(x1, x2, ...) | Returns the arithmetic mean. |
| AVERAGEIF(range, criteria, [average_range]) | Returns the average of numbers that meet a condition. |
| AVERAGEIFS(average_range, criteria_range1, criteria1, ...) | Returns the average of numbers that meet multiple conditions. |
| COUNT(x1, x2, ...) | Counts numbers in a range. |
| COUNTA(x1, x2, ...) | Counts non-empty cells. |
| COUNTBLANK(range) | Counts empty cells. |
| COUNTIF(range, criteria) | Counts cells that meet a condition. |
| COUNTIFS(criteria_range1, criteria1, ...) | Counts cells that meet multiple conditions. |
| MAX(x1, x2, ...) | Returns the largest value. |
| MIN(x1, x2, ...) | Returns the smallest value. |
| MEDIAN(x1, x2, ...) | Returns the median. |
| MODE(x1, x2, ...) | Returns the most frequently occurring value. |
| STDEV(x1, x2, ...) | Estimates standard deviation (sample). |
| STDEVP(x1, x2, ...) | Calculates standard deviation (population). |
| VAR(x1, x2, ...) | Estimates variance (sample). |
| VARP(x1, x2, ...) | Calculates variance (population). |
3. Logical Functions
| Function | Description |
|---|---|
| AND(x1, x2, ...) | Returns TRUE if all arguments are TRUE. |
| FALSE() | Returns FALSE. |
| IF(condition, value_if_true, value_if_false) | Returns one value if TRUE, another if FALSE. |
| IFERROR(value, value_if_error) | Returns value_if_error if value is an error. |
| NOT(x) | Reverses the logical value (TRUE → FALSE, FALSE → TRUE). |
| OR(x1, x2, ...) | Returns TRUE if any argument is TRUE. |
| TRUE() | Returns TRUE. |
| XOR(x1, x2, ...) | Returns TRUE if an odd number of arguments are TRUE. |
4. Text Functions
| Function | Description |
|---|---|
| CONCATENATE(text1, text2, ...) | Joins text strings. |
| EXACT(text1, text2) | Checks if two strings are identical (case-sensitive). |
| FIND(find_text, within_text, [start_pos]) | Returns the position of a substring (case-sensitive). |
| LEFT(text, num_chars) | Extracts the leftmost num_chars from a string. |
| LEN(text) | Returns the length of a string. |
| LOWER(text) | Converts text to lowercase. |
| MID(text, start, num_chars) | Extracts a substring from the middle. |
| PROPER(text) | Capitalizes the first letter of each word. |
| REPLACE(old_text, start, num_chars, new_text) | Replaces part of a string. |
| REPT(text, times) | Repeats text a specified number of times. |
| RIGHT(text, num_chars) | Extracts the rightmost num_chars from a string. |
| SEARCH(find_text, within_text, [start_pos]) | Returns the position of a substring (case-insensitive). |
| SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replaces specific text in a string. |
| TEXT(value, format) | Formats a number/date as text. |
| TRIM(text) | Removes extra spaces. |
| UPPER(text) | Converts text to uppercase. |
| VALUE(text) | Converts text to a number. |
5. Date & Time Functions
| Function | Description |
|---|---|
| DATE(year, month, day) | Creates a date from year, month, day. |
| DATEDIF(start_date, end_date, unit) | Calculates the difference between dates ("Y", "M", "D"). |
| DATEVALUE(date_text) | Converts a date string to a serial number. |
| DAY(date) | Extracts the day (1-31). |
| DAYS(end_date, start_date) | Returns the number of days between dates. |
| HOUR(time) | Extracts the hour (0-23). |
| MINUTE(time) | Extracts the minute (0-59). |
| MONTH(date) | Extracts the month (1-12). |
| NOW() | Returns the current date and time. |
| SECOND(time) | Extracts the second (0-59). |
| TIME(hour, minute, second) | Creates a time from hour, minute, second. |
| TODAY() | Returns the current date. |
| WEEKDAY(date, [type]) | Returns the day of the week (1-7). |
| YEAR(date) | Extracts the year. |
6. Financial Functions
| Function | Description |
|---|---|
| FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |
| IPMT(rate, per, nper, pv, [fv], [type]) | Returns the interest payment for a period. |
| NPV(rate, value1, value2, ...) | Returns the net present value. |
| PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan. |
| PV(rate, nper, pmt, [fv], [type]) | Returns the present value. |
7. Lookup & Reference Functions
| Function | Description |
|---|---|
| HLOOKUP(value, table, row_index, [range_lookup]) | Searches for a value in the top row of a table. |
| INDEX(array, row_num, [col_num]) | Returns a value from an array based on row/column. |
| MATCH(lookup_value, lookup_array, [match_type]) | Returns the position of a value in a range. |
| VLOOKUP(value, table, col_index, [range_lookup]) | Searches for a value in the first column of a table. |
Notes
- PHPSpreadsheet supports most Excel functions, but not all (e.g., some dynamic array functions from Excel 365 may be missing).
- To use a formula in PHP:
$sheet->setCellValue('A1', '=SUM(B1:B10)'); - Formulas are recalculated automatically in Excel, but in PHP, you can force recalculation with:
$spreadsheet->getActiveSheet()->calculateWorksheet();