Microsoft Excel is the best and most widely used spreadsheet software worldwide. It can be quite daunting to use if you don’t know which formulas and functions to use for specific purposes. Here is a list of all Microsoft Excel formulae sorted by their functions.
Microsoft Excel Mathematical Formulas
The most important and most used mathematical functions are addition, subtraction, division and multiplication so here are the formulas for each of them in Microsoft Excel.
1. Addition Formulas
The simplest way to add numbers and cell values in MS Excel is to use the +operator on your keyboard. You can add multiple numbers in a cell or add multiple cell values using it, for example (=12+51+34) or (=A3+B6+F9) and press enter, the sum will automatically be displayed in the cell. There are many formulas for addition in MS Excel depending on you needs, they are:
SUM: Used for adding all numbers in selected cells
SUMIF: Is used for adding all numbers in selected cells based on one criterion
SUMIFS: Used for adding all numbers in selected cells based on multiple criteria
SUMPRODUCT: Multiplies and sums up corresponding items in an array.
SUMSQ: Used to add the squares of selected values
SUMX2MY2: Used to add the difference of squares of two arrays
SUMX2PY2: Used for adding the squares of corresponding items in arrays
SUMXMY2: Adds the squares of the differences of corresponding items in arrays
SUBTOTAL: Is used to find the subtotal of numbers in a column, list or database.
2. Subtraction Formulas
There is no specific formula for subtraction in Excel, to subtract numbers in a cell, you can use the minus sign on your keyboard for example (=20 – 10) or (=A5 – C3) and the cell will return the difference automatically.
To subtract numbers in a range, you have to use the SUM formula by making the numbers you want to subtract negative and then adding all the numbers in the range.
3. Multiplication Formulas
To multiply in MS Excel, you can simply use the asterisk key to multiply a number of values e.g. (=5*4*8) or a number of cells for example. (=B3*D5*E8). There are some formulas for multiplication also, they are:
PRODUCT: Is used to multiply a range of cells for example (=PRODUCT (A3:A30))
SUMPRODUCT: see addition formulas for usage.
MMULT: used for multiplying two arrays in a matrix.
DPRODUCT: Used to multiply database values based on given criteria.
4. Division Formulas
Division in MS Excel can be done easily by using the forward slash operator for example (=20/4) or (=F9/E7). There are no other functions for division in Excel.
5. Matrix Formulas
MDETERM: Is used to find the determinant of a matrix in an array
MINVERSE: Is used to find the inverse matrix of a matrix
MMULT: Is used to multiply two matrices
6. Trigonometry Formulas
ACOS: Is used to find the arc-cosine of a number in radians
ACOSH: Is used to find the inverse hyperbolic cosine of numbers
ASIN: Used to find the arcsine of numbers in radians
ASINH: Used to find the inverse hyperbolic sine of numbers
ATAN: Used to find the arctangent of a number in radians
ATAN2: Used to find the arctangent of coordinates in radians
ATANH: Used to find the inverse hyperbolic tangent of numbers
COS: Is used to find the cosine of angles
COSH: Is used to find the hyperbolic cosine of numbers
SIN: Is used to find the sine of angles
SINH: Is used to find hyperbolic sine
TAN: Is used to find the tangent of an angle
TANH: Is used to find hyperbolic tangent
7. Other MS Excel Mathematical Functions
ABS: Used to find the absolute value of numbers
AGGREGATE: used to apply formulas like AVERAGE, SUM, MAX while ignoring errors and hidden rows.
BIN2DEC: Is used to convert binary numbers to decimal
BIN2HEX: Is used to convert binary numbers to hexadecimal
BIN2OCT:Is used to convert binary numbers to octal
CEILING: Rounds up a number based on a specified significance
CEILING PRECISE: Rounds up numbers to the nearest integer
COMBIN: Used to find the number of combinations for specific items
COMBINA: Used to find the number of combinations for specific items with repetitions included
COMPLEX: Is used to convert real and imaginary coefficients to complex numbers
CONVERT: Is used to convert numbers from one measurement unit to another
DEGREES: Is used to convert numbers from radians to degrees
EVEN: Is used to round up numbers to the closest even integer.
EXP: Is used to find e raised to power n
FACT: Is used to find the factorial of numbers
INT, FIX: are used to find the integer portions of numbers
FLOOR: Is used to round a number down based on significance
LN: Is used to find the natural logarithm of numbers
LOG: Is used to find the logarithm of numbers in a specific base
LOG10: Is used to find the logarithm in base 10 of a number
MOD: Is used to find the remainder of a number after division
ODD: Is used to round up numbers to the nearest odd integer
PI: Is used to return the pi mathematical constant
POWER: Is used to raise a number to a specified power
RADIANS: Is used to convert numbers in degrees to radians
RAND: Is used to find a random number greater than or equal to zero and less than one
RANDBETWEEN: Is used to find random numbers between a top and bottom range of numbers.
ROMAN: Is used for converting numbers to Roman numerals
ROUNDUP: Is used to round up numbers to a specified number of digits
ROUNDDOWN: Is used to round down numbers to a specified number of digits
SIGN: Is used to find the sign of a number
SQRT: Is used to find the square root of numbers
TRUNC: IS used to truncate a number to a specified number of digits
MS Excel Statistics Formulas
AVEDEV: Is used to find average absolute deviation
AVERAGE: Is used to find average of numbers
AVERAGEIF: Used to find the average of selected numbers based on a criterion
AVERAGEIFS: Used to find the average of selected numbers based on multiple criteria
BETA.DIST: Is used to find beta distribution
BETADIST: Is used to find cumulative beta probably density
BETAINV: Is used to find the inverse of the cumulative beta probability density
BINOMDIST: Is used to find individual term binomial distribution probability
BINOM.INV: Used to find the smallest value when the cumulative binomial distribution is larger.
CHIDIST: Used to find one tailed probability of a chi-squared distribution
CHIINV: Used to find the inverse of the one tailed probability of a chi-squared distribution
CHITEST: Used to find the value of a chi squared distribution
COVAR: Is used to find co-variance
FORECAST: Is used to predict the future value based on current data
FREQUENCY: Is used to find how many times values show up in a data set
GROWTH: Is used to predict exponential growth
INTERCEPT: Used to find the intercept on a line graph
LARGE: Is used to find the largest value in a data set
LINEST: Is used to calculate statistics for a straight line using least square method
MAX: Is used to find the largest values in a list of numbers
MAXA: Find the largest value in a data set
MEDIAN: Is used to find median
MIN: Is used to find the smallest value in a list of numbers
MINA: Is used to find the smallest value in a data set
PERCENTILE: Is used to find the nth percentile
PERMUT: Is used to calculate permutations
QUARTILE: is used to find the quartile of selected values
RANK: Is used to find rank in a group of numbers
SLOPE: Is used to find the slope of a regression line
SMALL: Is used to find the nth smallest value in a set of values
STDEV: Is used to find standard deviation of numbers
STDEVA: Is used to find standard deviation of a data set
STDEVP: Is used to find standard deviation of a whole population of numbers
STDEVPA: Is used to find the standard deviation of a whole population of a dataset
VAR: Is used to find the variance of numbers
VARA: Is used to find the variance of a data set
VARP: Is used to find the variance of an entire population of numbers
VARPA: Is used to find the variance of an entire population of a data set
Microsoft Excel Accounting Formulas
ACCRINT: Is used to find accrued interest on a periodic basis
ACCRINTM: Is used to find accrued interest at maturity
AMORDEGRC: Is used to find linear depreciation of assets on a prorated basis
AMORLINC: Is used to find depreciation of assets on a prorated basis
DB: Is used to find asset depreciation using fixed declining balance method
DDB: Is used to find asset depreciation based on double declining balance
FV: Is used to find the future value of investments
IPMT: Is used to find interest on investments
IRR: Is used to find the Internal Rate of Return
ISPMT: Is used to find interest payment on investments
MIRR: Used to find the modified internal rate of return
NPER: Used to find the number of periods of an investment
NPV: Used to find the net present value of investments
PMT: Is used to find loan payment amounts
PPMT: Used to find payment on principal
PV: Used to find the present value of investments
RATE: Used to find annuity interest rate
SLN: Uses straight line depreciation method to find asset depreciation
SYD: Uses sum-of-years digit depreciation methods to find asset depreciation
VDB: Uses variable declining balance depreciation method to find asset depreciation
XIRR: Used to find internal rate of return for non periodic cash flows
MS Excel VBA Formulas and Functions List
Most Microsoft Excel worksheet formulas work on MS Excel VBA but there are some formulas specifically for VBA, here they are:
ATN: Used to find the arctangent of numbers in VBA
ASC: Used to find the ASCII value of a character
CHR: Used to find a character based on its ASCII value
FORMAT NUMBERS: Is used to convert numeric expressions to formatted strings in VBA.
FORMAT STRINGS: Turns a string expression into a formatted string
RANDOMIZE: Changes seed value used by the random number generator for RND function in VBA.
RND: Is used to generate random numbers in VBA
SGN: Is used to return the sign of a number
SQR: To find square root
INSTR: Used to find the position of the first occurrence of a substring within a string
INSTRREV: Used to find the position of a string’s first occurrence in another string
LCASE: Used to convert strings to lowercase
LTRIM: Used to remove lead spaces from strings
SPACE: Is used to specify a number of spaces in a string
SPLIT: Is used to split a string into substrings
STR: Is used to return a number’s string representation
STRCOMP: Used to find the integer value representing a string comparison
STRCONV: Is used to convert a string to uppercase, lowercase, sentence case or Unicode.
STRRESERVE: Is used to reverse the characters of a string
UCASE: Is used to convert a string to uppercase
VAL: Used to return numbers in a string
MS Excel Reference Functions
ADDRESS: Used to locate a cell address
AREAS: Is used to find the number of ranges in a reference
CHOOSE: Is used to find a list of values from a given position
COLUMN: Is used to find the number of columns
HLOOKUP: Is used for horizontal lookups
HYPERLINK: Is used to create a link to a webpage
INDEX: Used to find values from ranges or tables
INDIRECT: Is used to find a cell reference from its string representation
LOOKUP: Finds a value from a range or an array
MATCH: Finds the relative position for a value in an array
OFFSET: Used to find a reference to a range offset specified rows and columns
ROW: Is used to find row numbers
ROWS: Is used to find the number of rows
TRANSPOSE: Is used to find the transpose of a range of cells
VLOOKUP: Is used to search for values vertically across columns
MS Excel String Functions
CHAR: Is used to find characters based on ASCII value
CLEAN: Is used to remove unprintable characters from strings
CODE: Is used to find the ASCII value of a character
CONCAT: Is used to combine multiple strings
DOLLAR: Used to convert numbers to text in currency format
EXACT: Is used to determine if two strings are the same
FIND: Is used to find the location of a substring
FIXED: Is used to find the text version of a rounded number
LEFT: Used to extract substrings from strings
LEN: Is used to find the length of a string
LOWER: Used for converting letters to lower case
MID: Is used to extract substrings from strings
PRPOER: Used to format a string in sentence case
REPLACE: Is used to replace characters in a string with other characters
REPT: Is used to repeat a text multiple times
RIGHT: Is used to extract a substring from a string from the right end
RTRIM: Is used to remove trailing spaces from a string
SEARCH: Is used to locate a substring in a string
SUBSTITUTE: Is used to replace one set of characters with another set
T: Is used to return text which a value refers to
TEXT: Used to convert a value to text in a specified format
TEXTJOIN: Is used to combine multiple strings together
TRIM: Is used to return the text value of a string with the leading and trailing spaces removed
UPPER: Used to convert text to uppercase
VALUE: Used to convert text value representing a number to that number