true

Learn Microsoft Excel Training from the Best Tutors

• Affordable fees
• 1-1 or Group class
• Flexible Timings
• Verified Tutors

Search in

# 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

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
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

## Other Lessons for You

VBA Tip: Print Random Numbers In A Selected Range
Let’s say you want to print some random numbers in a selected range then you can use below Macro. Sub Print_Randomnumbers() Dim myrng As Range For Each myrng In Selection myrng.Value = Application.WorksheetFunction.Int(Rnd...

Vlookup in excel
Vlookup is a very useful formula in excel. Vlookup can be used in two ways Exact and approximate. We can find and fill in data with the help of Vlookup. The syntax of Vlookup is: Vlookup (Lookupvalue,...

HTML (Hypertext Markup Language)
HTML (Hypertext Markup Language) is the set of markup symbols or codes inserted in a file intended for display on a World Wide Web browser page. The markup tells the Web browser how to display a Web page's...

What is the difference between Analytics and analysis?
Analysis> Separation of a whole into its component parts> Looks backwards over time, providing marketers with a historical view of what has happened Analytics > Defines the science behind the...

Use of Indirect Function
I would like to share my experience of how Indirect formula helped me in my recent project. I was working with some report which was connected to a large database in excel, so whatever changes I made to...

### Looking for Microsoft Excel Training classes?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you
X

### Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

• Select the best Tutor
• Book & Attend a Free Demo
• Pay and start Learning

### Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro