Excel Function List (Worksheet)

Excel Function  (VBA)           
Excel Method (VBA)             
Excel Object Property (VBA)
Excel Event (VBA)                  

This page lists all the Excel functions (for worksheet) that have been explained in this website.

You may also want to read:

Differences among Function, Sub, Method, Property

 

String Function

Function Description
CHAR Convert ASCII value to character
CODE Convert the first character of a text into ASCII value
COUNTA Count non-empty cells
FIND Return position of a substring in a string (case sensitive)
LEFT
LEN Return number of characters in a string
MID Extract a substring from a string
MOD Get remainder in division
RIGHT
SEARCH Return position of a substring in a string (can use Wildcard and case insensitive)
wCapLetter A custom Function to capitalize first letter in a sentence
wCheckAlphabet A custom Function to check if a text contains any alphabet
wCheckNumber A custom Function to check if a text contains any number
wCheckOnlyAlphabet A custom Function to check if a text contains only alphabet
wCheckOnlyNumber A custom Function to check if a text contains only number
wCheckSymbol A custom Function to check if a text contains any symbol
wCheckHKID A custom Function to check the last digit of HKID
wCountSubStr A custom Function to count substring in string
wCustomSort A custom Function to assign numeric value to Text for custom sort
wExtractAlphabet A custom Function to extract alphabet from text
wExtractNumber A custom Function to extract number from text
wExtractPercent A custom Function to extract Percent from text
wExtractSymbol A custom Function to extract symbol from text
wExtractText A custom Function to extract substring inside defined text (such as brackets)
wLpad A custom Function to add leading character
wRandomHKID A custom Function to generate random HKID (Hong Kong ID card number)
wRandomLetter A customer Function to generate random letter a to z / A to Z / a to Z
wRandomNumber A custom Function to generate random number by specifying lowerbound and upperbound
wRemoveBrackets A custom Function to remove text inside brackets
wRpad A custom Function to add characters to suffix
wSplit A custom Function to delimit a text and extract the Nth substring
wSumExtractNumber A custom Function to Sum number part of text in a Range
wUniqueStr A custom Function to remove duplicate in a text

 

Numeric Function

Function Description
Cell Get the information of Cell such as address and date/number format
Count Count number of cells that contain number
Int Get the integer part of a number
MRound Round to nearest multiple
Rand Generate a random decimal number between larger than 0 and smaller than 1
RandBetween Generate a random number between two integers (both inclusive)
Round Rounding that uses Round Half Up logic
SumIf Sum a Range of values that meet a condition
SumIfs Sum a Range of values that meet multiple conditions
SumProduct Multiple the sum of two or more arrays and can be used to lookup multiple criteria
SubTotal Calculate Auto Filter value with aggregate Function
wCountColorCell A custom Function to count number of colored Cells in a given Range
wExtractNumber A custom Function to extract number from text
wExtractPercent A custom Function to extract Percent from text
wGroupDecimal A custom Function to categorize/ group decimal number
wGroupInt A custom Function to categorize/ group integer
wIsPrimeNumber A custom Function to check if a number is prime number
wMRound A custom Function to simulate spreadsheet MROUND Function
wRandomNumber A custom Function to generate random number by specifying lowerbound and upperbound
wRound A custom Function to simulate spreadsheet Round Function
wSumColorCell A custom Function to sum colored Cells in a given Range
wSumExtractNumber A custom Function to Sum number part of text in a Range

 

Date Function

Function Description
Cell Get the information of Cell such as address and date/number format
Date Convert year month and day to date serial
DateDif Complete year / month / day difference between two days
DateValue Convert date text to date serial
Days360 Day difference between two days or calculate the year / month difference in decimal places
EDate Add (or subtract) specified months to a date
Networkdays Net working days (exclude Sat and Sun) between two days
wNetworkdays A custom Function that simulates Excel worksheet Networkdays to calculate number of work days (exclude Sat and Sun) between two dates. Suitable for people who cannot install Analysis ToolPak

 

Lookup/Reference Function

Function Description
Address Combine column number and row number to return Cell address in Text format
Cell Get the information of Cell such as address and date/number format
Indirect Convert text to Reference
LookUp Look up a value in a Range and return the corresponding result value in a Range
Rank Give a number a rank in a range of number.
Offset Find a Range that has a relative location to another Range
wlookup A custom Function to Vlookup second matched value or specific occurrence
wrangesort A custom Function to sort data in ascending order
wFirstColorCell A custom Function to look for the first colored Cell
wColLastRow A custom Function to look for last row of specific column
wColLastRowAdd A custom Function to look for address of last row of specific column
wColLastRowVal A custom Function to look for value of last row of specific column
wColNm A custom Function to convert column number to column letter
wColNum A custom Function to convert column letter to column number
wRowLastColNum A custom Function to look for last column number of specific row
wRowLastColNm A custom Function to look for last column name of specific row
wRowLastColAdd A custom Function to look for address of last column Cell of specific row
wRowLastColVal A custom Function to look for value of last column Cell of specific row

 

Boolean Function

Function Description
IFERROR Return a desired value if the Cell contains error
wCheckAlphabet A custom Function to check if a text contains any alphabet
wCheckNumber A custom Function to check if a text contains any number
wCheckOnlyAlphabet A custom Function to check if a text contains only alphabet
wCheckOnlyNumber A custom Function to check if a text contains only number
wCheckSymbol A custom Function to check if a text contains any symbol
wCheckHKID A custom Function to check if the Check Digit (last digit) of HKID is correct
wIfWbOpen A custom Function to check if a workbook is opened
wIsPrimeNumber A custom Function to check if a number is prime number
wsExist A custom Function to check if a worksheet exists

 

Workbook/Worksheet Function

Function Description
wsExist A custom Function to check if a worksheet exists
wIfWbOpen A custom Function to check if a workbook is opened

 

Financial Function

Function Description
IRR Calculate Internal Rate of Return
NPV Calculate Net Present Value

 

Statistics Function

Function Description
Correl Calculate Coefficient of Correlation
Covariance.P Calculate Population Covariance
Covariance.S Calculate Sample Covariance
NORM.DIST Calculate probability using standard deviation and mean
NORM.S.DIST Convert Z Score to probability
Percentile  Calculate percentile using (n-1)*p+1
Percentile.inc Calculate percentile using (n-1)*p+1
Percentile.exc Calculate percentile using (n+1)*p
Standardize Calculate Z Score from mean and standard deviation
T.Test Calculate P value of Independent Sample T Test and Paired-samples T Test

 

Pivot Table Function

Function Description
GetPivotData Get Pivot Table Data.

 

Other Function

Function Description
FormulaText Get Excel Formula of a Cell
Hyperlink Create Hyperlink to link to other cell to open website or open document
Msgbox Create a prompt box to alert users
wGetFormula A custom Function to get Excel Formula of a Cell (similar to FormulaText Function)
wRGB A custom Function to get RGB color of Range
wLinkStatusDescr A custom Function to convert xlLinkInfoStatus from numeric value to description