Signup as a Tutor

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

MS Office Software Training Fees

Estimated Fees in India

₹ 2,500 to ₹ 5,000 per month

Find MS Office Software Training Fees in your locality

Please select a locality

or Get Free Quotes*

* Tutors will contact you with custom quotes as per your need

Estimated fees for MS Office Software Training in

  • LOCALITIES FEE RANGE

Find Tutors in

Map View

Estimated fees in

Find Tutors in

Estimated fees for MS Office Software Training in top cities

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

How UrbanPro works

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

Best MS Office Software Training experts to choose from

Post your requirements now

Lokesh

Badatya Manjeet

Umasankar Natarajan

iTech Analytic Solutions

Computec Professionals Group

Excelytics Vba Macros

Best MS Office Software Training experts to choose from

Post your requirements now

Trending discussions on MS Office Software Training

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

  1. ADDRESS (WS): Returns a text representation of a cell address
  2. AREAS (WS): Returns the number of ranges in a reference
  3. CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
  4. COLUMN (WS): Returns the column number of a cell reference
  5. COLUMNS (WS): Returns the number of columns in a cell reference
  6. 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
  7. HYPERLINK (WS): Creates a shortcut to a file or Internet address
  8. INDEX (WS): Returns either the value or the reference to a value from a table or range
  9. INDIRECT (WS): Returns the reference to a cell based on its string representation
  10. LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
  11. MATCH (WS): Searches for a value in an array and returns the relative position of that item
  12. OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
  13. ROW (WS): Returns the row number of a cell reference
  14. ROWS (WS): Returns the number of rows in a cell reference
  15. TRANSPOSE (WS): Returns a transposed range of cells
  16. 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:

  1. ASC (VBA): Returns ASCII value of a character
  2. CHAR (WS): Returns the character based on the ASCII value
  3. CHR (VBA): Returns the character based on the ASCII value
  4. CLEAN (WS): Removes all nonprintable characters from a string
  5. CODE (WS): Returns the ASCII value of a character or the first character in a cell
  6. CONCAT (WS): Used to join 2 or more strings together
  7. CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
  8. CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
  9. DOLLAR (WS): Converts a number to text, using a currency format
  10. EXACT (WS): Compares two strings and returns TRUE if both values are the same
  11. FIND (WS): Returns the location of a substring in a string (case-sensitive)
  12. FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
  13. FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
  14. INSTR (VBA): Returns the position of the first occurrence of a substring in a string
  15. INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
  16. LCASE (VBA): Converts a string to lowercase
  17. LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
  18. LEN (WS, VBA): Returns the length of the specified string
  19. LOWER (WS): Converts all letters in the specified string to lowercase
  20. LTRIM (VBA): Removes leading spaces from a string
  21. MID (WS, VBA): Extracts a substring from a string (starting at any position)
  22. PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
  23. REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
  24. REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
  25. REPT (WS): Returns a repeated text value a specified number of times
  26. RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
  27. RTRIM (VBA): Removes trailing spaces from a string
  28. SEARCH (WS): Returns the location of a substring in a string
  29. SPACE (VBA): Returns a string with a specified number of spaces
  30. SPLIT (VBA): Used to split a string into substrings based on a delimiter
  31. STR (VBA): Returns a string representation of a number
  32. STRCOMP (VBA): Returns an integer value representing the result of a string comparison
  33. STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
  34. STRREVERSE (VBA): Returns a string whose characters are in reverse order
  35. SUBSTITUTE (WS): Replaces a set of characters with another
  36. T (WS): Returns the text referred to by a value
  37. TEXT (WS): Returns a value converted to text with a specified format
  38. TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
  39. TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
  40. UCASE (VBA): Converts a string to all uppercase
  41. UPPER (WS): Convert text to all uppercase
  42. VAL (VBA): Returns the numbers found in a string
  43. VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

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

D. Math/Trig Functions:

  1. ABS (WS, VBA): Returns the absolute value of a number
  2. ACOS (WS): Returns the arccosine (in radians) of a number
  3. ACOSH (WS): Returns the inverse hyperbolic cosine of a number
  4. AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
  5. ASIN (WS): Returns the arcsine (in radians) of a number
  6. ASINH (WS): Returns the inverse hyperbolic sine of a number
  7. ATAN (WS): Returns the arctangent (in radians) of a number
  8. ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
  9. ATANH (WS): Returns the inverse hyperbolic tangent of a number
  10. ATN (VBA): Returns the arctangent of a number
  11. CEILING (WS): Returns a number rounded up based on a multiple of significance
  12. CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
  13. COMBIN (WS): Returns the number of combinations for a specified number of items
  14. COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
  15. COS (WS, VBA): Returns the cosine of an angle
  16. COSH (WS): Returns the hyperbolic cosine of a number
  17. DEGREES (WS): Converts radians into degrees
  18. EVEN (WS): Rounds a number up to the nearest even integer
  19. EXP (WS, VBA): Returns e raised to the nth power
  20. FACT (WS): Returns the factorial of a number
  21. FIX (VBA): Returns the integer portion of a number
  22. FLOOR (WS): Returns a number rounded down based on a multiple of significance
  23. FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
  24. INT (WS, VBA): Returns the integer portion of a number
  25. LN (WS): Returns the natural logarithm of a number
  26. LOG (WS, VBA): Returns the logarithm of a number to a specified base
  27. LOG10 (WS): Returns the base-10 logarithm of a number
  28. MDETERM (WS): Returns the matrix determinant of an array
  29. MINVERSE (WS): Returns the inverse matrix for a given matrix
  30. MMULT (WS): Returns the matrix product of two arrays
  31. MOD (WS): Returns the remainder after a number is divided by a divisor
  32. ODD (WS): Rounds a number up to the nearest odd integer
  33. PI (WS): Returns the mathematical constant called pi
  34. POWER (WS): Returns the result of a number raised to a given power
  35. PRODUCT (WS): Multiplies the numbers and returns the product
  36. RADIANS (WS): Converts degrees into radians
  37. RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
  38. RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
  39. RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
  40. RND (VBA): Used to generate a random number (integer value)
  41. ROMAN (WS): Converts a number to roman numeral
  42. ROUND (WS): Returns a number rounded to a specified number of digits
  43. ROUND (VBA): Returns a number rounded to a specified number of digits
  44. ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
  45. ROUNDUP (WS): Returns a number rounded up to a specified number of digits
  46. SGN (VBA): Returns the sign of a number
  47. SIGN (WS): Returns the sign of a number
  48. SIN (WS, VBA): Returns the sine of an angle
  49. SINH (WS): Returns the hyperbolic sine of a number
  50. SQR (VBA): Returns the square root of a number
  51. SQRT (WS): Returns the square root of a number
  52. SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
  53. SUM (WS): Adds all numbers in a range of cells
  54. SUMIF (WS): Adds all numbers in a range of cells based on one criteria
  55. SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
  56. SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
  57. SUMSQ (WS): Returns the sum of the squares of a series of values
  58. SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
  59. SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
  60. SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
  61. TAN (WS, VBA): Returns the tangent of an angle
  62. TANH (WS): Returns the hyperbolic tangent of a number
  63. TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

  1. AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
  2. AVERAGE (WS): Returns the average of the numbers provided
  3. AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
  4. AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
  5. AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
  6. BETA.DIST (WS): Returns the beta distribution
  7. BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
  8. BETADIST (WS): Returns the cumulative beta probability density function
  9. BETAINV (WS): Returns the inverse of the cumulative beta probability density function
  10. BINOM.DIST (WS): Returns the individual term binomial distribution probability
  11. BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
  12. BINOMDIST (WS): Returns the individual term binomial distribution probability
  13. CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
  14. CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
  15. CHITEST (WS): Returns the value from the chi-squared distribution
  16. COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
  17. COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
  18. COUNTBLANK (WS): Counts the number of empty cells in a range
  19. COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
  20. COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
  21. COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
  22. FORECAST (WS): Returns a prediction of a future value based on existing values provided
  23. FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
  24. GROWTH (WS): Returns the predicted exponential growth based on existing values provided
  25. INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
  26. LARGE (WS): Returns the nth largest value from a set of values
  27. LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
  28. MAX (WS): Returns the largest value from the numbers provided
  29. MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
  30. MEDIAN (WS): Returns the median of the numbers provided
  31. MIN (WS): Returns the smallest value from the numbers provided
  32. MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
  33. PERCENTILE (WS): Returns the nth percentile from a set of values
  34. PERCENTRANK (WS): Returns the nth percentile from a set of values
  35. PERMUT (WS): Returns the number of permutations for a specified number of items
  36. QUARTILE (WS): Returns the quartile from a set of values
  37. RANK (WS): Returns the rank of a number within a set of numbers
  38. SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
  39. SMALL (WS): Returns the nth smallest value from a set of values
  40. STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
  41. STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
  42. STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
  43. STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
  44. VAR (WS): Returns the variance of a population based on a sample of numbers
  45. VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
  46. VARP (WS): Returns the variance of a population based on an entire population of numbers
  47. VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

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

G. Information Functions:

  1. CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
  2. ENVIRON (VBA): Returns the value of an operating system environment variable
  3. ERROR.TYPE (WS): Returns the numeric representation of an Excel error
  4. INFO (WS): Returns information about the operating environment
  5. ISBLANK (WS): Used to check for blank or null values
  6. ISDATE (VBA): Returns TRUE if the expression is a valid date
  7. ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
  8. ISERR (WS): Used to check for error values except #N/A
  9. ISERROR (WS, VBA): Used to check for error values
  10. ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
  11. ISNA (WS): Used to check for #N/A error
  12. ISNONTEXT (WS): Used to check for a value that is not text
  13. ISNULL (VBA): Used to check for a NULL value
  14. ISNUMBER (WS): Used to check for a numeric value
  15. ISNUMERIC (VBA): Used to check for a numeric value
  16. ISREF (WS): Used to check for a reference
  17. ISTEXT (WS): Used to check for a text value
  18. N (WS): Converts a value to a number
  19. NA (WS): Returns the #N/A error value
  20. TYPE (WS): Returns the type of a value

H. Financial Functions:

  1. ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
  2. ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
  3. AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
  4. AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
  5. DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
  6. DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
  7. FV (WS, VBA): Returns the future value of an investment
  8. IPMT (WS, VBA): Returns the interest payment for an investment
  9. IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
  10. ISPMT (WS): Returns the interest payment for an investment
  11. MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
  12. NPER (WS, VBA): Returns the number of periods for an investment
  13. NPV (WS, VBA): Returns the net present value of an investment
  14. PMT (WS, VBA): Returns the payment amount for a loan
  15. PPMT (WS, VBA): Returns the payment on the principal for a particular payment
  16. PV (WS, VBA): Returns the present value of an investment
  17. RATE (WS, VBA): Returns the interest rate for an annuity
  18. SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
  19. SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
  20. VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
  21. XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

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

J. Engineering Functions:

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

K. File/Directory Functions:

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

L. Data Type Conv. Functions:

  1. CBOOL (VBA): Converts a value to a boolean
  2. CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
  3. CCUR (VBA): Converts a value to currency
  4. CDATE (VBA): Converts a value to a date
  5. CDBL (VBA): Converts a value to a double
  6. CDEC (VBA): Converts a value to a decimal number
  7. CINT (VBA): Converts a value to an integer
  8. CLNG (VBA): Converts a value to a long integer
  9. CSNG (VBA): Converts a value to a single-precision number
  10. CSTR (VBA): Converts a value to a string
  11. CVAR (VBA): Converts a value to a variant
read less
Comments
Dislike Bookmark

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

  1. ADDRESS (WS): Returns a text representation of a cell address
  2. AREAS (WS): Returns the number of ranges in a reference
  3. CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
  4. COLUMN (WS): Returns the column number of a cell reference
  5. COLUMNS (WS): Returns the number of columns in a cell reference
  6. 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
  7. HYPERLINK (WS): Creates a shortcut to a file or Internet address
  8. INDEX (WS): Returns either the value or the reference to a value from a table or range
  9. INDIRECT (WS): Returns the reference to a cell based on its string representation
  10. LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
  11. MATCH (WS): Searches for a value in an array and returns the relative position of that item
  12. OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
  13. ROW (WS): Returns the row number of a cell reference
  14. ROWS (WS): Returns the number of rows in a cell reference
  15. TRANSPOSE (WS): Returns a transposed range of cells
  16. 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:

  1. ASC (VBA): Returns ASCII value of a character
  2. CHAR (WS): Returns the character based on the ASCII value
  3. CHR (VBA): Returns the character based on the ASCII value
  4. CLEAN (WS): Removes all nonprintable characters from a string
  5. CODE (WS): Returns the ASCII value of a character or the first character in a cell
  6. CONCAT (WS): Used to join 2 or more strings together
  7. CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
  8. CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
  9. DOLLAR (WS): Converts a number to text, using a currency format
  10. EXACT (WS): Compares two strings and returns TRUE if both values are the same
  11. FIND (WS): Returns the location of a substring in a string (case-sensitive)
  12. FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
  13. FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
  14. INSTR (VBA): Returns the position of the first occurrence of a substring in a string
  15. INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
  16. LCASE (VBA): Converts a string to lowercase
  17. LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
  18. LEN (WS, VBA): Returns the length of the specified string
  19. LOWER (WS): Converts all letters in the specified string to lowercase
  20. LTRIM (VBA): Removes leading spaces from a string
  21. MID (WS, VBA): Extracts a substring from a string (starting at any position)
  22. PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
  23. REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
  24. REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
  25. REPT (WS): Returns a repeated text value a specified number of times
  26. RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
  27. RTRIM (VBA): Removes trailing spaces from a string
  28. SEARCH (WS): Returns the location of a substring in a string
  29. SPACE (VBA): Returns a string with a specified number of spaces
  30. SPLIT (VBA): Used to split a string into substrings based on a delimiter
  31. STR (VBA): Returns a string representation of a number
  32. STRCOMP (VBA): Returns an integer value representing the result of a string comparison
  33. STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
  34. STRREVERSE (VBA): Returns a string whose characters are in reverse order
  35. SUBSTITUTE (WS): Replaces a set of characters with another
  36. T (WS): Returns the text referred to by a value
  37. TEXT (WS): Returns a value converted to text with a specified format
  38. TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
  39. TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
  40. UCASE (VBA): Converts a string to all uppercase
  41. UPPER (WS): Convert text to all uppercase
  42. VAL (VBA): Returns the numbers found in a string
  43. VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

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

D. Math/Trig Functions:

  1. ABS (WS, VBA): Returns the absolute value of a number
  2. ACOS (WS): Returns the arccosine (in radians) of a number
  3. ACOSH (WS): Returns the inverse hyperbolic cosine of a number
  4. AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
  5. ASIN (WS): Returns the arcsine (in radians) of a number
  6. ASINH (WS): Returns the inverse hyperbolic sine of a number
  7. ATAN (WS): Returns the arctangent (in radians) of a number
  8. ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
  9. ATANH (WS): Returns the inverse hyperbolic tangent of a number
  10. ATN (VBA): Returns the arctangent of a number
  11. CEILING (WS): Returns a number rounded up based on a multiple of significance
  12. CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
  13. COMBIN (WS): Returns the number of combinations for a specified number of items
  14. COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
  15. COS (WS, VBA): Returns the cosine of an angle
  16. COSH (WS): Returns the hyperbolic cosine of a number
  17. DEGREES (WS): Converts radians into degrees
  18. EVEN (WS): Rounds a number up to the nearest even integer
  19. EXP (WS, VBA): Returns e raised to the nth power
  20. FACT (WS): Returns the factorial of a number
  21. FIX (VBA): Returns the integer portion of a number
  22. FLOOR (WS): Returns a number rounded down based on a multiple of significance
  23. FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
  24. INT (WS, VBA): Returns the integer portion of a number
  25. LN (WS): Returns the natural logarithm of a number
  26. LOG (WS, VBA): Returns the logarithm of a number to a specified base
  27. LOG10 (WS): Returns the base-10 logarithm of a number
  28. MDETERM (WS): Returns the matrix determinant of an array
  29. MINVERSE (WS): Returns the inverse matrix for a given matrix
  30. MMULT (WS): Returns the matrix product of two arrays
  31. MOD (WS): Returns the remainder after a number is divided by a divisor
  32. ODD (WS): Rounds a number up to the nearest odd integer
  33. PI (WS): Returns the mathematical constant called pi
  34. POWER (WS): Returns the result of a number raised to a given power
  35. PRODUCT (WS): Multiplies the numbers and returns the product
  36. RADIANS (WS): Converts degrees into radians
  37. RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
  38. RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
  39. RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
  40. RND (VBA): Used to generate a random number (integer value)
  41. ROMAN (WS): Converts a number to roman numeral
  42. ROUND (WS): Returns a number rounded to a specified number of digits
  43. ROUND (VBA): Returns a number rounded to a specified number of digits
  44. ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
  45. ROUNDUP (WS): Returns a number rounded up to a specified number of digits
  46. SGN (VBA): Returns the sign of a number
  47. SIGN (WS): Returns the sign of a number
  48. SIN (WS, VBA): Returns the sine of an angle
  49. SINH (WS): Returns the hyperbolic sine of a number
  50. SQR (VBA): Returns the square root of a number
  51. SQRT (WS): Returns the square root of a number
  52. SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
  53. SUM (WS): Adds all numbers in a range of cells
  54. SUMIF (WS): Adds all numbers in a range of cells based on one criteria
  55. SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
  56. SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
  57. SUMSQ (WS): Returns the sum of the squares of a series of values
  58. SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
  59. SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
  60. SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
  61. TAN (WS, VBA): Returns the tangent of an angle
  62. TANH (WS): Returns the hyperbolic tangent of a number
  63. TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

  1. AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
  2. AVERAGE (WS): Returns the average of the numbers provided
  3. AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
  4. AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
  5. AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
  6. BETA.DIST (WS): Returns the beta distribution
  7. BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
  8. BETADIST (WS): Returns the cumulative beta probability density function
  9. BETAINV (WS): Returns the inverse of the cumulative beta probability density function
  10. BINOM.DIST (WS): Returns the individual term binomial distribution probability
  11. BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
  12. BINOMDIST (WS): Returns the individual term binomial distribution probability
  13. CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
  14. CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
  15. CHITEST (WS): Returns the value from the chi-squared distribution
  16. COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
  17. COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
  18. COUNTBLANK (WS): Counts the number of empty cells in a range
  19. COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
  20. COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
  21. COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
  22. FORECAST (WS): Returns a prediction of a future value based on existing values provided
  23. FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
  24. GROWTH (WS): Returns the predicted exponential growth based on existing values provided
  25. INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
  26. LARGE (WS): Returns the nth largest value from a set of values
  27. LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
  28. MAX (WS): Returns the largest value from the numbers provided
  29. MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
  30. MEDIAN (WS): Returns the median of the numbers provided
  31. MIN (WS): Returns the smallest value from the numbers provided
  32. MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
  33. PERCENTILE (WS): Returns the nth percentile from a set of values
  34. PERCENTRANK (WS): Returns the nth percentile from a set of values
  35. PERMUT (WS): Returns the number of permutations for a specified number of items
  36. QUARTILE (WS): Returns the quartile from a set of values
  37. RANK (WS): Returns the rank of a number within a set of numbers
  38. SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
  39. SMALL (WS): Returns the nth smallest value from a set of values
  40. STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
  41. STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
  42. STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
  43. STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
  44. VAR (WS): Returns the variance of a population based on a sample of numbers
  45. VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
  46. VARP (WS): Returns the variance of a population based on an entire population of numbers
  47. VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

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

G. Information Functions:

  1. CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
  2. ENVIRON (VBA): Returns the value of an operating system environment variable
  3. ERROR.TYPE (WS): Returns the numeric representation of an Excel error
  4. INFO (WS): Returns information about the operating environment
  5. ISBLANK (WS): Used to check for blank or null values
  6. ISDATE (VBA): Returns TRUE if the expression is a valid date
  7. ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
  8. ISERR (WS): Used to check for error values except #N/A
  9. ISERROR (WS, VBA): Used to check for error values
  10. ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
  11. ISNA (WS): Used to check for #N/A error
  12. ISNONTEXT (WS): Used to check for a value that is not text
  13. ISNULL (VBA): Used to check for a NULL value
  14. ISNUMBER (WS): Used to check for a numeric value
  15. ISNUMERIC (VBA): Used to check for a numeric value
  16. ISREF (WS): Used to check for a reference
  17. ISTEXT (WS): Used to check for a text value
  18. N (WS): Converts a value to a number
  19. NA (WS): Returns the #N/A error value
  20. TYPE (WS): Returns the type of a value

H. Financial Functions:

  1. ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
  2. ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
  3. AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
  4. AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
  5. DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
  6. DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
  7. FV (WS, VBA): Returns the future value of an investment
  8. IPMT (WS, VBA): Returns the interest payment for an investment
  9. IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
  10. ISPMT (WS): Returns the interest payment for an investment
  11. MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
  12. NPER (WS, VBA): Returns the number of periods for an investment
  13. NPV (WS, VBA): Returns the net present value of an investment
  14. PMT (WS, VBA): Returns the payment amount for a loan
  15. PPMT (WS, VBA): Returns the payment on the principal for a particular payment
  16. PV (WS, VBA): Returns the present value of an investment
  17. RATE (WS, VBA): Returns the interest rate for an annuity
  18. SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
  19. SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
  20. VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
  21. XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

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

J. Engineering Functions:

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

K. File/Directory Functions:

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

L. Data Type Conv. Functions:

  1. CBOOL (VBA): Converts a value to a boolean
  2. CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
  3. CCUR (VBA): Converts a value to currency
  4. CDATE (VBA): Converts a value to a date
  5. CDBL (VBA): Converts a value to a double
  6. CDEC (VBA): Converts a value to a decimal number
  7. CINT (VBA): Converts a value to an integer
  8. CLNG (VBA): Converts a value to a long integer
  9. CSNG (VBA): Converts a value to a single-precision number
  10. CSTR (VBA): Converts a value to a string
  11. CVAR (VBA): Converts a value to a variant
read less
Comments
Dislike Bookmark

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

  1. ADDRESS (WS): Returns a text representation of a cell address
  2. AREAS (WS): Returns the number of ranges in a reference
  3. CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
  4. COLUMN (WS): Returns the column number of a cell reference
  5. COLUMNS (WS): Returns the number of columns in a cell reference
  6. 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
  7. HYPERLINK (WS): Creates a shortcut to a file or Internet address
  8. INDEX (WS): Returns either the value or the reference to a value from a table or range
  9. INDIRECT (WS): Returns the reference to a cell based on its string representation
  10. LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
  11. MATCH (WS): Searches for a value in an array and returns the relative position of that item
  12. OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
  13. ROW (WS): Returns the row number of a cell reference
  14. ROWS (WS): Returns the number of rows in a cell reference
  15. TRANSPOSE (WS): Returns a transposed range of cells
  16. 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:

  1. ASC (VBA): Returns ASCII value of a character
  2. CHAR (WS): Returns the character based on the ASCII value
  3. CHR (VBA): Returns the character based on the ASCII value
  4. CLEAN (WS): Removes all nonprintable characters from a string
  5. CODE (WS): Returns the ASCII value of a character or the first character in a cell
  6. CONCAT (WS): Used to join 2 or more strings together
  7. CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
  8. CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
  9. DOLLAR (WS): Converts a number to text, using a currency format
  10. EXACT (WS): Compares two strings and returns TRUE if both values are the same
  11. FIND (WS): Returns the location of a substring in a string (case-sensitive)
  12. FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
  13. FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
  14. INSTR (VBA): Returns the position of the first occurrence of a substring in a string
  15. INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
  16. LCASE (VBA): Converts a string to lowercase
  17. LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
  18. LEN (WS, VBA): Returns the length of the specified string
  19. LOWER (WS): Converts all letters in the specified string to lowercase
  20. LTRIM (VBA): Removes leading spaces from a string
  21. MID (WS, VBA): Extracts a substring from a string (starting at any position)
  22. PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
  23. REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
  24. REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
  25. REPT (WS): Returns a repeated text value a specified number of times
  26. RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
  27. RTRIM (VBA): Removes trailing spaces from a string
  28. SEARCH (WS): Returns the location of a substring in a string
  29. SPACE (VBA): Returns a string with a specified number of spaces
  30. SPLIT (VBA): Used to split a string into substrings based on a delimiter
  31. STR (VBA): Returns a string representation of a number
  32. STRCOMP (VBA): Returns an integer value representing the result of a string comparison
  33. STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
  34. STRREVERSE (VBA): Returns a string whose characters are in reverse order
  35. SUBSTITUTE (WS): Replaces a set of characters with another
  36. T (WS): Returns the text referred to by a value
  37. TEXT (WS): Returns a value converted to text with a specified format
  38. TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
  39. TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
  40. UCASE (VBA): Converts a string to all uppercase
  41. UPPER (WS): Convert text to all uppercase
  42. VAL (VBA): Returns the numbers found in a string
  43. VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

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

D. Math/Trig Functions:

  1. ABS (WS, VBA): Returns the absolute value of a number
  2. ACOS (WS): Returns the arccosine (in radians) of a number
  3. ACOSH (WS): Returns the inverse hyperbolic cosine of a number
  4. AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
  5. ASIN (WS): Returns the arcsine (in radians) of a number
  6. ASINH (WS): Returns the inverse hyperbolic sine of a number
  7. ATAN (WS): Returns the arctangent (in radians) of a number
  8. ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
  9. ATANH (WS): Returns the inverse hyperbolic tangent of a number
  10. ATN (VBA): Returns the arctangent of a number
  11. CEILING (WS): Returns a number rounded up based on a multiple of significance
  12. CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
  13. COMBIN (WS): Returns the number of combinations for a specified number of items
  14. COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
  15. COS (WS, VBA): Returns the cosine of an angle
  16. COSH (WS): Returns the hyperbolic cosine of a number
  17. DEGREES (WS): Converts radians into degrees
  18. EVEN (WS): Rounds a number up to the nearest even integer
  19. EXP (WS, VBA): Returns e raised to the nth power
  20. FACT (WS): Returns the factorial of a number
  21. FIX (VBA): Returns the integer portion of a number
  22. FLOOR (WS): Returns a number rounded down based on a multiple of significance
  23. FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
  24. INT (WS, VBA): Returns the integer portion of a number
  25. LN (WS): Returns the natural logarithm of a number
  26. LOG (WS, VBA): Returns the logarithm of a number to a specified base
  27. LOG10 (WS): Returns the base-10 logarithm of a number
  28. MDETERM (WS): Returns the matrix determinant of an array
  29. MINVERSE (WS): Returns the inverse matrix for a given matrix
  30. MMULT (WS): Returns the matrix product of two arrays
  31. MOD (WS): Returns the remainder after a number is divided by a divisor
  32. ODD (WS): Rounds a number up to the nearest odd integer
  33. PI (WS): Returns the mathematical constant called pi
  34. POWER (WS): Returns the result of a number raised to a given power
  35. PRODUCT (WS): Multiplies the numbers and returns the product
  36. RADIANS (WS): Converts degrees into radians
  37. RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
  38. RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
  39. RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
  40. RND (VBA): Used to generate a random number (integer value)
  41. ROMAN (WS): Converts a number to roman numeral
  42. ROUND (WS): Returns a number rounded to a specified number of digits
  43. ROUND (VBA): Returns a number rounded to a specified number of digits
  44. ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
  45. ROUNDUP (WS): Returns a number rounded up to a specified number of digits
  46. SGN (VBA): Returns the sign of a number
  47. SIGN (WS): Returns the sign of a number
  48. SIN (WS, VBA): Returns the sine of an angle
  49. SINH (WS): Returns the hyperbolic sine of a number
  50. SQR (VBA): Returns the square root of a number
  51. SQRT (WS): Returns the square root of a number
  52. SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
  53. SUM (WS): Adds all numbers in a range of cells
  54. SUMIF (WS): Adds all numbers in a range of cells based on one criteria
  55. SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
  56. SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
  57. SUMSQ (WS): Returns the sum of the squares of a series of values
  58. SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
  59. SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
  60. SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
  61. TAN (WS, VBA): Returns the tangent of an angle
  62. TANH (WS): Returns the hyperbolic tangent of a number
  63. TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

  1. AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
  2. AVERAGE (WS): Returns the average of the numbers provided
  3. AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
  4. AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
  5. AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
  6. BETA.DIST (WS): Returns the beta distribution
  7. BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
  8. BETADIST (WS): Returns the cumulative beta probability density function
  9. BETAINV (WS): Returns the inverse of the cumulative beta probability density function
  10. BINOM.DIST (WS): Returns the individual term binomial distribution probability
  11. BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
  12. BINOMDIST (WS): Returns the individual term binomial distribution probability
  13. CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
  14. CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
  15. CHITEST (WS): Returns the value from the chi-squared distribution
  16. COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
  17. COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
  18. COUNTBLANK (WS): Counts the number of empty cells in a range
  19. COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
  20. COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
  21. COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
  22. FORECAST (WS): Returns a prediction of a future value based on existing values provided
  23. FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
  24. GROWTH (WS): Returns the predicted exponential growth based on existing values provided
  25. INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
  26. LARGE (WS): Returns the nth largest value from a set of values
  27. LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
  28. MAX (WS): Returns the largest value from the numbers provided
  29. MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
  30. MEDIAN (WS): Returns the median of the numbers provided
  31. MIN (WS): Returns the smallest value from the numbers provided
  32. MINA (WS): Returns the smallest value from the values provided (numbers, text and logical values
  33. PERCENTILE (WS): Returns the nth percentile from a set of values
  34. PERCENTRANK (WS): Returns the nth percentile from a set of values
  35. PERMUT (WS): Returns the number of permutations for a specified number of items
  36. QUARTILE (WS): Returns the quartile from a set of values
  37. RANK (WS): Returns the rank of a number within a set of numbers
  38. SLOPE (WS): Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values
  39. SMALL (WS): Returns the nth smallest value from a set of values
  40. STDEV (WS): Returns the standard deviation of a population based on a sample of numbers
  41. STDEVA (WS): Returns the standard deviation of a population based on a sample of numbers, text, and logical values
  42. STDEVP (WS): Returns the standard deviation of a population based on an entire population of numbers
  43. STDEVPA (WS): Returns the standard deviation of a population based on an entire population of numbers, text, and logical values
  44. VAR (WS): Returns the variance of a population based on a sample of numbers
  45. VARA (WS): Returns the variance of a population based on a sample of numbers, text, and logical values
  46. VARP (WS): Returns the variance of a population based on an entire population of numbers
  47. VARPA (WS): Returns the variance of a population based on an entire population of numbers, text, and logical values

F. Logical Functions:

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

G. Information Functions:

  1. CELL (WS): Used to retrieve information about a cell such as contents, formatting, size, etc.
  2. ENVIRON (VBA): Returns the value of an operating system environment variable
  3. ERROR.TYPE (WS): Returns the numeric representation of an Excel error
  4. INFO (WS): Returns information about the operating environment
  5. ISBLANK (WS): Used to check for blank or null values
  6. ISDATE (VBA): Returns TRUE if the expression is a valid date
  7. ISEMPTY (VBA): Used to check for blank cells or uninitialized variables
  8. ISERR (WS): Used to check for error values except #N/A
  9. ISERROR (WS, VBA): Used to check for error values
  10. ISLOGICAL (WS): Used to check for a logical value (TRUE or FALSE)
  11. ISNA (WS): Used to check for #N/A error
  12. ISNONTEXT (WS): Used to check for a value that is not text
  13. ISNULL (VBA): Used to check for a NULL value
  14. ISNUMBER (WS): Used to check for a numeric value
  15. ISNUMERIC (VBA): Used to check for a numeric value
  16. ISREF (WS): Used to check for a reference
  17. ISTEXT (WS): Used to check for a text value
  18. N (WS): Converts a value to a number
  19. NA (WS): Returns the #N/A error value
  20. TYPE (WS): Returns the type of a value

H. Financial Functions:

  1. ACCRINT (WS): Returns the accrued interest for a security that pays interest on a periodic basis
  2. ACCRINTM (WS): Returns the accrued interest for a security that pays interest at maturity
  3. AMORDEGRC (WS): Returns the linear depreciation of an asset for each accounting period, on a prorated basis
  4. AMORLINC (WS): Returns the depreciation of an asset for each accounting period, on a prorated basis
  5. DB (WS): Returns the depreciation of an asset based on the fixed-declining balance method
  6. DDB (WS, VBA): Returns the depreciation of an asset based on the double-declining balance method
  7. FV (WS, VBA): Returns the future value of an investment
  8. IPMT (WS, VBA): Returns the interest payment for an investment
  9. IRR (WS, VBA): Returns the internal rate of return for a series of cash flows
  10. ISPMT (WS): Returns the interest payment for an investment
  11. MIRR (WS, VBA): Returns the modified internal rate of return for a series of cash flows
  12. NPER (WS, VBA): Returns the number of periods for an investment
  13. NPV (WS, VBA): Returns the net present value of an investment
  14. PMT (WS, VBA): Returns the payment amount for a loan
  15. PPMT (WS, VBA): Returns the payment on the principal for a particular payment
  16. PV (WS, VBA): Returns the present value of an investment
  17. RATE (WS, VBA): Returns the interest rate for an annuity
  18. SLN (WS, VBA): Returns the depreciation of an asset based on the straight-line depreciation method
  19. SYD (WS, VBA): Returns the depreciation of an asset based on the sum-of-years' digits depreciation method
  20. VDB (WS): Returns the depreciation of an asset based on a variable declining balance depreciation method
  21. XIRR (WS): Returns the internal rate of return for a series of cash flows that may not be periodic

I. Database Functions:

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

J. Engineering Functions:

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

K. File/Directory Functions:

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

L. Data Type Conv. Functions:

  1. CBOOL (VBA): Converts a value to a boolean
  2. CBYTE (VBA): Converts a value to a byte (ie: number between 0 and 255)
  3. CCUR (VBA): Converts a value to currency
  4. CDATE (VBA): Converts a value to a date
  5. CDBL (VBA): Converts a value to a double
  6. CDEC (VBA): Converts a value to a decimal number
  7. CINT (VBA): Converts a value to an integer
  8. CLNG (VBA): Converts a value to a long integer
  9. CSNG (VBA): Converts a value to a single-precision number
  10. CSTR (VBA): Converts a value to a string
  11. CVAR (VBA): Converts a value to a variant
read less
Comments
Dislike Bookmark

Join Discussions

Post your Requirement

Connect with the best tutors or institutes

Please enter your name.

Please enter valid email

Please enter valid 10 digit Phone Number

Please enter the Pin Code.

Which location would you prefer for MS Office Software Training?

Please select at least one option from Class Location.

By signing up, you agree to our Terms of Use and Privacy Policy.

You Already have an UrbanPro Account

Please Login to continue

Please Enter valid Email or Phone Number

Please Enter your Password

Please enter the OTP sent to your registered mobile number.

Please Enter valid Password or OTP

Forgot Password? Resend OTP OTP Sent

Sorry, we were not able to find a user with that username and password.

We have sent you an OTP to your register email address and registered number. Please enter OTP as Password to continue