List of Microsoft Excel Formulas and Functions

0
59
microsoft excel formulas list

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

LEAVE A REPLY