true

Find the best tutors and institutes for Microsoft Excel Training

Find Best Microsoft Excel Training classes

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra
24/02/2018 0 0

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
0 Dislike
Follow 2

Please Enter a comment

Submit

Other Lessons for You

Hide Duplicate Values & Apply Borders Using Conditional Formatting
Please refer following screenshots:1st Screenshot: Original Dataset2nd Screenshot: After Conditioinal Formatting is applied In 2nd screenshot, using Conditional Formatting:1) Repeat months are...

Ankur Sharma | 02 Jan

1 0
0

Hidden Gems Of MS Excel - Consolidate
Situation: You are part of the Finance Team, and work at Headquarters of an Organization.Periodically, You receive expense data from Teams in different Regions.After You receive expense data, You SUM all...

Ankur Sharma | 27/12/2019

1 0
0

Hidden Gems of MS Excel - Reapply Filter
Q) How does Reapply help? Or, when could I use Reapply?A) e.g. Please refer following screenshot: You are Analysing a Dataset.You have applied Filter on Date Column, to focus on April month entries.You...

Ankur Sharma | 13/12/2019

0 0
0

Hidden Gems of MS Excel - Compare Year-on-Year Performance Using Pivot Table
Did You know You can Compare Year-on-Year& Performance Using Pivot Table?Yes. Just drag-&-drop and Compare Year-on-Year& Performance Quickly.& Year-on-Year simply means Periodic. As per...

Ankur Sharma | 10/12/2019

1 0
0

Before analysis start Important
Hi All, Often we do analysis in day to day life, before we start we should follow few principles of data cleaning activities in any application such as removing unwanted space, converting data into correct...

Amol Mane | 10/12/2019

0 0
0
X

Looking for Microsoft Excel Training Classes?

Find best tutors for Microsoft Excel Training Classes by posting a requirement.

  • Post a learning requirement
  • Get customized responses
  • Compare and select the best

Looking for Microsoft Excel Training Classes?

Find best Microsoft Excel Training Classes in your locality on UrbanPro

Post your learning requirement

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more