As a tutor you can connect with more than a million students and grow your network.

Estimated Fees in India

₹ 2,500 to ₹ 5,000 per month

Find MS Office Software Training Fees in your locality

Click for more

₹ 3,000 to ₹ 5,100

Click for more

₹ 3,700 to ₹ 5,000

Click for more

₹ 100 to ₹ 3,100

Click for more

₹ 2,500 to ₹ 5,000

Click for more

₹ 3,000 to ₹ 5,000

Click for more

₹ 2,500 to ₹ 3,000

Post your requirements or browse our professional network

Get customized quotes and responses for your learning requirement

Choose & Connect with the expert of your choice

Lokesh

Badatya Manjeet

Umasankar Natarajan

iTech Analytic Solutions

Computec Professionals Group

Excelytics Vba Macros

Lesson Posted 1 day ago IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra

I am working in IBM as a Lead Finance and Administration. I have gained exposure on financial reporting...

MS Excel: Formulas and Functions: Listed by Category Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual... read more

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

- DATE (WS): Returns the serial date value for a date
- DATE (VBA): Returns the current system date
- DATEADD (VBA): Returns a date after which a certain time/date interval has been added
- DATEDIF (WS): Returns the difference between two date values, based on the interval specified
- DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
- DATEPART (VBA): Returns a specified part of a given date
- DATESERIAL (VBA): Returns a date given a year, month, and day value
- DATEVALUE (WS, VBA): Returns the serial number of a date
- DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
- DAYS (WS): Returns the number of days between 2 dates
- DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
- EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
- EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
- FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
- HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
- ISOWEEKNUM (WS): Returns the ISO week number for a date
- MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
- MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
- MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
- NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NOW (WS, VBA): Returns the current system date and time
- SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
- TIME (WS): Returns a decimal number given an hour, minute and second value
- TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
- TIMEVALUE (WS, VBA): Returns the serial number of a time
- TODAY (WS): Returns the current system date
- WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
- WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
- WEEKNUM (WS): Returns the week number for a date
- WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
- WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
- YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
- YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

- AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
- AVERAGE (WS): Returns the average of the numbers provided
- AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
- AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
- AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
- BETA.DIST (WS): Returns the beta distribution
- BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
- BETADIST (WS): Returns the cumulative beta probability density function
- BETAINV (WS): Returns the inverse of the cumulative beta probability density function
- BINOM.DIST (WS): Returns the individual term binomial distribution probability
- BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
- BINOMDIST (WS): Returns the individual term binomial distribution probability
- CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
- CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
- CHITEST (WS): Returns the value from the chi-squared distribution
- COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
- COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
- COUNTBLANK (WS): Counts the number of empty cells in a range
- COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
- COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
- COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
- FORECAST (WS): Returns a prediction of a future value based on existing values provided
- FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
- GROWTH (WS): Returns the predicted exponential growth based on existing values provided
- INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
- LARGE (WS): Returns the nth largest value from a set of values
- LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
- MAX (WS): Returns the largest value from the numbers provided
- MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
- MEDIAN (WS): Returns the median of the numbers provided
- MIN (WS): Returns the smallest value from the numbers provided
- MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
- PERCENTILE (WS): Returns the nth percentile from a set of values
- PERCENTRANK (WS): Returns the nth percentile from a set of values
- PERMUT (WS): Returns the number of permutations for a specified number of items
- QUARTILE (WS): Returns the quartile from a set of values
- RANK (WS): Returns the rank of a number within a set of numbers
- SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
- SMALL (WS): Returns the nth smallest value from a set of values
- STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
- STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
- STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
- VAR (WS): Returns the variance of a population based on a sample of numbers
- VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
- VARP (WS): Returns the variance of a population based on an entire population of numbers
- VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

- AND (WS): Returns TRUE if all conditions are TRUE
- AND (VBA): Returns TRUE if all conditions are TRUE
- CASE (VBA): Has the functionality of an IF-THEN-ELSE statement
- FALSE (WS): Returns a logical value of FALSE
- FOR...NEXT (VBA): Used to create a FOR LOOP
- IF (WS): Returns one value if the condition is TRUE or another value if the condition is FALSE
- IF (more than 7) (WS): Nest more than 7 IF functions
- IF (up to 7) (WS): Nest up to 7 IF functions
- IF-THEN-ELSE (VBA): Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE
- IFERROR (WS): Used to return an alternate value if a formula results in an error
- IFNA (WS): Used to return an alternate value if a formula results in #N/A error
- IFS (WS): Specify multiple IF conditions within 1 function
- NOT (WS): Returns the reversed logical value
- OR (WS): Returns TRUE if any of the conditions are TRUE
- OR (VBA): Returns TRUE if any of the conditions are TRUE
- SWITCH (WS): Compares an expression to a list of values and returns the corresponding result
- SWITCH (VBA): Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE
- TRUE (WS): Returns a logical value of TRUE
- WHILE...WEND (VBA): Used to create a WHILE LOOP

G. Information Functions:

- CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
- ENVIRON (VBA): Returns the value of an operating system environment variable
- ERROR.TYPE (WS): Returns the numeric representation of an Excel error
- INFO (WS): Returns information about the operating environment
- ISBLANK (WS): Used to check for blank or null values
- ISDATE (VBA): Returns TRUE if the expression is a valid date
- ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
- ISERR (WS): Used to check for error values except #N/A
- ISERROR (WS, VBA): Used to check for error values
- ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
- ISNA (WS): Used to check for #N/A error
- ISNONTEXT (WS): Used to check for a value that is not text
- ISNULL (VBA): Used to check for a NULL value
- ISNUMBER (WS): Used to check for a numeric value
- ISNUMERIC (VBA): Used to check for a numeric value
- ISREF (WS): Used to check for a reference
- ISTEXT (WS): Used to check for a text value
- N (WS): Converts a value to a number
- NA (WS): Returns the #N/A error value
- TYPE (WS): Returns the type of a value

H. Financial Functions:

- ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
- ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
- AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
- AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
- DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
- DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
- FV (WS, VBA): Returns the future value of an investment
- IPMT (WS, VBA): Returns the interest payment for an investment
- IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
- ISPMT (WS): Returns the interest payment for an investment
- MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
- NPER (WS, VBA): Returns the number of periods for an investment
- NPV (WS, VBA): Returns the net present value of an investment
- PMT (WS, VBA): Returns the payment amount for a loan
- PPMT (WS, VBA): Returns the payment on the principal for a particular payment
- PV (WS, VBA): Returns the present value of an investment
- RATE (WS, VBA): Returns the interest rate for an annuity
- SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
- SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
- VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
- XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

- DAVERAGE (WS): Averages all numbers in a column in a list or database, based on a given criteria
- DCOUNT (WS): Returns the number of cells in a column or database that contains numeric values and meets a given criteria
- DCOUNTA (WS): Returns the number of cells in a column or database that contains nonblank values and meets a given criteria
- DGET (WS): Retrieves from a database a single record that matches a given criteria
- DMAX (WS): Returns the largest number in a column in a list or database, based on a given criteria
- DMIN (WS): Returns the smallest number in a column in a list or database, based on a given criteria
- DPRODUCT (WS): Returns the product of the numbers in a column in a list or database, based on a given criteria
- DSTDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- DSTDEVP (WS): Returns the standard deviation of a population based on the entire population of numbers
- DSUM (WS): Sums the numbers in a column or database that meets a given criteria
- DVAR (WS): Returns the variance of a population based on a sample of numbers
- DVARP (WS): Returns the variance of a population based on the entire population of numbers

J. Engineering Functions:

- BIN2DEC (WS): Converts a binary number to a decimal number
- BIN2HEX (WS): Converts a binary number to a hexadecimal number
- BIN2OCT (WS): Converts a binary number to an octal number
- COMPLEX (WS): Converts coefficients (real and imaginary) into a complex number
- CONVERT (WS): Convert a number from one measurement unit to another measurement unit

K. File/Directory Functions:

- CHDIR (VBA): Used to change the current directory or folder
- CHDRIVE (VBA): Used to change the current drive
- CURDIR (VBA): Returns the current path
- DIR (VBA): Returns the first filename that matches the pathname and attributes specified
- FILEDATETIME (VBA): Returns the date and time of when a file was created or last modified
- FILELEN (VBA): Returns the size of a file in bytes
- GETATTR (VBA): Returns an integer that represents the attributes of a file, folder, or directory
- MKDIR (VBA): Used to create a new folder or directory
- SETATTR (VBA): Used to set the attributes of a file

L. Data Type Conv. Functions:

- CBOOL (VBA): Converts a value to a boolean
- CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
- CCUR (VBA): Converts a value to currency
- CDATE (VBA): Converts a value to a date
- CDBL (VBA): Converts a value to a double
- CDEC (VBA): Converts a value to a decimal number
- CINT (VBA): Converts a value to an integer
- CLNG (VBA): Converts a value to a long integer
- CSNG (VBA): Converts a value to a single-precision number
- CSTR (VBA): Converts a value to a string
- CVAR (VBA): Converts a value to a variant

Like 0

Comments Lesson Posted 1 day ago IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra

I am working in IBM as a Lead Finance and Administration. I have gained exposure on financial reporting...

MS Excel: Formulas and Functions: Listed by Category Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual... read more

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

- DATE (WS): Returns the serial date value for a date
- DATE (VBA): Returns the current system date
- DATEADD (VBA): Returns a date after which a certain time/date interval has been added
- DATEDIF (WS): Returns the difference between two date values, based on the interval specified
- DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
- DATEPART (VBA): Returns a specified part of a given date
- DATESERIAL (VBA): Returns a date given a year, month, and day value
- DATEVALUE (WS, VBA): Returns the serial number of a date
- DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
- DAYS (WS): Returns the number of days between 2 dates
- DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
- EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
- EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
- FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
- HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
- ISOWEEKNUM (WS): Returns the ISO week number for a date
- MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
- MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
- MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
- NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NOW (WS, VBA): Returns the current system date and time
- SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
- TIME (WS): Returns a decimal number given an hour, minute and second value
- TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
- TIMEVALUE (WS, VBA): Returns the serial number of a time
- TODAY (WS): Returns the current system date
- WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
- WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
- WEEKNUM (WS): Returns the week number for a date
- WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
- WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
- YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
- YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

- AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
- AVERAGE (WS): Returns the average of the numbers provided
- AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
- AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
- AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
- BETA.DIST (WS): Returns the beta distribution
- BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
- BETADIST (WS): Returns the cumulative beta probability density function
- BETAINV (WS): Returns the inverse of the cumulative beta probability density function
- BINOM.DIST (WS): Returns the individual term binomial distribution probability
- BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
- BINOMDIST (WS): Returns the individual term binomial distribution probability
- CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
- CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
- CHITEST (WS): Returns the value from the chi-squared distribution
- COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
- COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
- COUNTBLANK (WS): Counts the number of empty cells in a range
- COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
- COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
- COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
- FORECAST (WS): Returns a prediction of a future value based on existing values provided
- FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
- GROWTH (WS): Returns the predicted exponential growth based on existing values provided
- INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
- LARGE (WS): Returns the nth largest value from a set of values
- LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
- MAX (WS): Returns the largest value from the numbers provided
- MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
- MEDIAN (WS): Returns the median of the numbers provided
- MIN (WS): Returns the smallest value from the numbers provided
- MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
- PERCENTILE (WS): Returns the nth percentile from a set of values
- PERCENTRANK (WS): Returns the nth percentile from a set of values
- PERMUT (WS): Returns the number of permutations for a specified number of items
- QUARTILE (WS): Returns the quartile from a set of values
- RANK (WS): Returns the rank of a number within a set of numbers
- SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
- SMALL (WS): Returns the nth smallest value from a set of values
- STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
- STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
- STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
- VAR (WS): Returns the variance of a population based on a sample of numbers
- VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
- VARP (WS): Returns the variance of a population based on an entire population of numbers
- VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

- AND (WS): Returns TRUE if all conditions are TRUE
- AND (VBA): Returns TRUE if all conditions are TRUE
- CASE (VBA): Has the functionality of an IF-THEN-ELSE statement
- FALSE (WS): Returns a logical value of FALSE
- FOR...NEXT (VBA): Used to create a FOR LOOP
- IF (WS): Returns one value if the condition is TRUE or another value if the condition is FALSE
- IF (more than 7) (WS): Nest more than 7 IF functions
- IF (up to 7) (WS): Nest up to 7 IF functions
- IF-THEN-ELSE (VBA): Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE
- IFERROR (WS): Used to return an alternate value if a formula results in an error
- IFNA (WS): Used to return an alternate value if a formula results in #N/A error
- IFS (WS): Specify multiple IF conditions within 1 function
- NOT (WS): Returns the reversed logical value
- OR (WS): Returns TRUE if any of the conditions are TRUE
- OR (VBA): Returns TRUE if any of the conditions are TRUE
- SWITCH (WS): Compares an expression to a list of values and returns the corresponding result
- SWITCH (VBA): Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE
- TRUE (WS): Returns a logical value of TRUE
- WHILE...WEND (VBA): Used to create a WHILE LOOP

G. Information Functions:

- CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
- ENVIRON (VBA): Returns the value of an operating system environment variable
- ERROR.TYPE (WS): Returns the numeric representation of an Excel error
- INFO (WS): Returns information about the operating environment
- ISBLANK (WS): Used to check for blank or null values
- ISDATE (VBA): Returns TRUE if the expression is a valid date
- ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
- ISERR (WS): Used to check for error values except #N/A
- ISERROR (WS, VBA): Used to check for error values
- ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
- ISNA (WS): Used to check for #N/A error
- ISNONTEXT (WS): Used to check for a value that is not text
- ISNULL (VBA): Used to check for a NULL value
- ISNUMBER (WS): Used to check for a numeric value
- ISNUMERIC (VBA): Used to check for a numeric value
- ISREF (WS): Used to check for a reference
- ISTEXT (WS): Used to check for a text value
- N (WS): Converts a value to a number
- NA (WS): Returns the #N/A error value
- TYPE (WS): Returns the type of a value

H. Financial Functions:

- ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
- ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
- AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
- AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
- DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
- DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
- FV (WS, VBA): Returns the future value of an investment
- IPMT (WS, VBA): Returns the interest payment for an investment
- IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
- ISPMT (WS): Returns the interest payment for an investment
- MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
- NPER (WS, VBA): Returns the number of periods for an investment
- NPV (WS, VBA): Returns the net present value of an investment
- PMT (WS, VBA): Returns the payment amount for a loan
- PPMT (WS, VBA): Returns the payment on the principal for a particular payment
- PV (WS, VBA): Returns the present value of an investment
- RATE (WS, VBA): Returns the interest rate for an annuity
- SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
- SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
- VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
- XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

- DAVERAGE (WS): Averages all numbers in a column in a list or database, based on a given criteria
- DCOUNT (WS): Returns the number of cells in a column or database that contains numeric values and meets a given criteria
- DCOUNTA (WS): Returns the number of cells in a column or database that contains nonblank values and meets a given criteria
- DGET (WS): Retrieves from a database a single record that matches a given criteria
- DMAX (WS): Returns the largest number in a column in a list or database, based on a given criteria
- DMIN (WS): Returns the smallest number in a column in a list or database, based on a given criteria
- DPRODUCT (WS): Returns the product of the numbers in a column in a list or database, based on a given criteria
- DSTDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- DSTDEVP (WS): Returns the standard deviation of a population based on the entire population of numbers
- DSUM (WS): Sums the numbers in a column or database that meets a given criteria
- DVAR (WS): Returns the variance of a population based on a sample of numbers
- DVARP (WS): Returns the variance of a population based on the entire population of numbers

J. Engineering Functions:

- BIN2DEC (WS): Converts a binary number to a decimal number
- BIN2HEX (WS): Converts a binary number to a hexadecimal number
- BIN2OCT (WS): Converts a binary number to an octal number
- COMPLEX (WS): Converts coefficients (real and imaginary) into a complex number
- CONVERT (WS): Convert a number from one measurement unit to another measurement unit

K. File/Directory Functions:

- CHDIR (VBA): Used to change the current directory or folder
- CHDRIVE (VBA): Used to change the current drive
- CURDIR (VBA): Returns the current path
- DIR (VBA): Returns the first filename that matches the pathname and attributes specified
- FILEDATETIME (VBA): Returns the date and time of when a file was created or last modified
- FILELEN (VBA): Returns the size of a file in bytes
- GETATTR (VBA): Returns an integer that represents the attributes of a file, folder, or directory
- MKDIR (VBA): Used to create a new folder or directory
- SETATTR (VBA): Used to set the attributes of a file

L. Data Type Conv. Functions:

- CBOOL (VBA): Converts a value to a boolean
- CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
- CCUR (VBA): Converts a value to currency
- CDATE (VBA): Converts a value to a date
- CDBL (VBA): Converts a value to a double
- CDEC (VBA): Converts a value to a decimal number
- CINT (VBA): Converts a value to an integer
- CLNG (VBA): Converts a value to a long integer
- CSNG (VBA): Converts a value to a single-precision number
- CSTR (VBA): Converts a value to a string
- CVAR (VBA): Converts a value to a variant

Like 0

Comments Lesson Posted 1 day ago IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra

I am working in IBM as a Lead Finance and Administration. I have gained exposure on financial reporting...

MS Excel: Formulas and Functions: Listed by Category Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual... read more

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

- DATE (WS): Returns the serial date value for a date
- DATE (VBA): Returns the current system date
- DATEADD (VBA): Returns a date after which a certain time/date interval has been added
- DATEDIF (WS): Returns the difference between two date values, based on the interval specified
- DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
- DATEPART (VBA): Returns a specified part of a given date
- DATESERIAL (VBA): Returns a date given a year, month, and day value
- DATEVALUE (WS, VBA): Returns the serial number of a date
- DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
- DAYS (WS): Returns the number of days between 2 dates
- DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
- EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
- EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
- FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
- HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
- ISOWEEKNUM (WS): Returns the ISO week number for a date
- MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
- MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
- MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
- NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NOW (WS, VBA): Returns the current system date and time
- SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
- TIME (WS): Returns a decimal number given an hour, minute and second value
- TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
- TIMEVALUE (WS, VBA): Returns the serial number of a time
- TODAY (WS): Returns the current system date
- WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
- WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
- WEEKNUM (WS): Returns the week number for a date
- WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
- WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
- YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
- YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

- AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
- AVERAGE (WS): Returns the average of the numbers provided
- AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
- AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
- AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
- BETA.DIST (WS): Returns the beta distribution
- BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
- BETADIST (WS): Returns the cumulative beta probability density function
- BETAINV (WS): Returns the inverse of the cumulative beta probability density function
- BINOM.DIST (WS): Returns the individual term binomial distribution probability
- BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
- BINOMDIST (WS): Returns the individual term binomial distribution probability
- CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
- CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
- CHITEST (WS): Returns the value from the chi-squared distribution
- COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
- COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
- COUNTBLANK (WS): Counts the number of empty cells in a range
- COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
- COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
- COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
- FORECAST (WS): Returns a prediction of a future value based on existing values provided
- FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
- GROWTH (WS): Returns the predicted exponential growth based on existing values provided
- INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
- LARGE (WS): Returns the nth largest value from a set of values
- LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
- MAX (WS): Returns the largest value from the numbers provided
- MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
- MEDIAN (WS): Returns the median of the numbers provided
- MIN (WS): Returns the smallest value from the numbers provided
- MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
- PERCENTILE (WS): Returns the nth percentile from a set of values
- PERCENTRANK (WS): Returns the nth percentile from a set of values
- PERMUT (WS): Returns the number of permutations for a specified number of items
- QUARTILE (WS): Returns the quartile from a set of values
- RANK (WS): Returns the rank of a number within a set of numbers
- SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
- SMALL (WS): Returns the nth smallest value from a set of values
- STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
- STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
- STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
- VAR (WS): Returns the variance of a population based on a sample of numbers
- VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
- VARP (WS): Returns the variance of a population based on an entire population of numbers
- VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

- AND (WS): Returns TRUE if all conditions are TRUE
- AND (VBA): Returns TRUE if all conditions are TRUE
- CASE (VBA): Has the functionality of an IF-THEN-ELSE statement
- FALSE (WS): Returns a logical value of FALSE
- FOR...NEXT (VBA): Used to create a FOR LOOP
- IF (WS): Returns one value if the condition is TRUE or another value if the condition is FALSE
- IF (more than 7) (WS): Nest more than 7 IF functions
- IF (up to 7) (WS): Nest up to 7 IF functions
- IF-THEN-ELSE (VBA): Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE
- IFERROR (WS): Used to return an alternate value if a formula results in an error
- IFNA (WS): Used to return an alternate value if a formula results in #N/A error
- IFS (WS): Specify multiple IF conditions within 1 function
- NOT (WS): Returns the reversed logical value
- OR (WS): Returns TRUE if any of the conditions are TRUE
- OR (VBA): Returns TRUE if any of the conditions are TRUE
- SWITCH (WS): Compares an expression to a list of values and returns the corresponding result
- SWITCH (VBA): Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE
- TRUE (WS): Returns a logical value of TRUE
- WHILE...WEND (VBA): Used to create a WHILE LOOP

G. Information Functions:

- CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
- ENVIRON (VBA): Returns the value of an operating system environment variable
- ERROR.TYPE (WS): Returns the numeric representation of an Excel error
- INFO (WS): Returns information about the operating environment
- ISBLANK (WS): Used to check for blank or null values
- ISDATE (VBA): Returns TRUE if the expression is a valid date
- ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
- ISERR (WS): Used to check for error values except #N/A
- ISERROR (WS, VBA): Used to check for error values
- ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
- ISNA (WS): Used to check for #N/A error
- ISNONTEXT (WS): Used to check for a value that is not text
- ISNULL (VBA): Used to check for a NULL value
- ISNUMBER (WS): Used to check for a numeric value
- ISNUMERIC (VBA): Used to check for a numeric value
- ISREF (WS): Used to check for a reference
- ISTEXT (WS): Used to check for a text value
- N (WS): Converts a value to a number
- NA (WS): Returns the #N/A error value
- TYPE (WS): Returns the type of a value

H. Financial Functions:

- ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
- ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
- AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
- AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
- DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
- DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
- FV (WS, VBA): Returns the future value of an investment
- IPMT (WS, VBA): Returns the interest payment for an investment
- IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
- ISPMT (WS): Returns the interest payment for an investment
- MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
- NPER (WS, VBA): Returns the number of periods for an investment
- NPV (WS, VBA): Returns the net present value of an investment
- PMT (WS, VBA): Returns the payment amount for a loan
- PPMT (WS, VBA): Returns the payment on the principal for a particular payment
- PV (WS, VBA): Returns the present value of an investment
- RATE (WS, VBA): Returns the interest rate for an annuity
- SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
- SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
- VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
- XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

- DAVERAGE (WS): Averages all numbers in a column in a list or database, based on a given criteria
- DCOUNT (WS): Returns the number of cells in a column or database that contains numeric values and meets a given criteria
- DCOUNTA (WS): Returns the number of cells in a column or database that contains nonblank values and meets a given criteria
- DGET (WS): Retrieves from a database a single record that matches a given criteria
- DMAX (WS): Returns the largest number in a column in a list or database, based on a given criteria
- DMIN (WS): Returns the smallest number in a column in a list or database, based on a given criteria
- DPRODUCT (WS): Returns the product of the numbers in a column in a list or database, based on a given criteria
- DSTDEV (WS): Returns the standard deviation of a population based on a sample of numbers
- DSTDEVP (WS): Returns the standard deviation of a population based on the entire population of numbers
- DSUM (WS): Sums the numbers in a column or database that meets a given criteria
- DVAR (WS): Returns the variance of a population based on a sample of numbers
- DVARP (WS): Returns the variance of a population based on the entire population of numbers

J. Engineering Functions:

- BIN2DEC (WS): Converts a binary number to a decimal number
- BIN2HEX (WS): Converts a binary number to a hexadecimal number
- BIN2OCT (WS): Converts a binary number to an octal number
- COMPLEX (WS): Converts coefficients (real and imaginary) into a complex number
- CONVERT (WS): Convert a number from one measurement unit to another measurement unit

K. File/Directory Functions:

- CHDIR (VBA): Used to change the current directory or folder
- CHDRIVE (VBA): Used to change the current drive
- CURDIR (VBA): Returns the current path
- DIR (VBA): Returns the first filename that matches the pathname and attributes specified
- FILEDATETIME (VBA): Returns the date and time of when a file was created or last modified
- FILELEN (VBA): Returns the size of a file in bytes
- GETATTR (VBA): Returns an integer that represents the attributes of a file, folder, or directory
- MKDIR (VBA): Used to create a new folder or directory
- SETATTR (VBA): Used to set the attributes of a file

L. Data Type Conv. Functions:

- CBOOL (VBA): Converts a value to a boolean
- CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
- CCUR (VBA): Converts a value to currency
- CDATE (VBA): Converts a value to a date
- CDBL (VBA): Converts a value to a double
- CDEC (VBA): Converts a value to a decimal number
- CINT (VBA): Converts a value to an integer
- CLNG (VBA): Converts a value to a long integer
- CSNG (VBA): Converts a value to a single-precision number
- CSTR (VBA): Converts a value to a string
- CVAR (VBA): Converts a value to a variant

Like 0

Comments ✕