Migration
ASAP Utilities Functions
link - asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=259
Library Function | Description |
ASAPCELLCOLORINDEX | Returns the color index number of the cell. FORMATGET |
ASAPCOUNTBYCELLCOLOR | Counts the number of cells in the given range that have a certain fill color. COUNTFORMAT |
ASAPCOUNTBYFONTCOLOR | Counts the number of cells in the given range that have a certain font color. COUNTFORMAT |
ASAPCOUNTCHAR | Counts the number of times a character occurs in a text. COUNTSUBSTRING |
ASAPCOUNTSHADES | Counts the number of colored cells in your range. COUNTFORMAT |
ASAPEXTRACTFILENAME | Returns the file name from a full path and filename. EXTRACTFILENAME |
ASAPEXTRACTFOLDERNAME | Returns the folder name from a combined filepath and filename. EXTRACTFOLDERPATH |
ASAPEXTRACTNUMBERS | Returns the numbers from a text string. EXTRACTNUMBERS |
ASAPFILENAME | Returns the name of your workbook. WORKBOOKFILENAME |
ASAPFILEPATH | Returns the filepath (the folder) where your workbook is stored. WORKBOOKPATH |
ASAPFILEPROPERTIES | Returns the value of one of the built-in document properties for the current workbook. WORKBOOKPROPERTIES |
ASAPFONTCOLORINDEX | Returns the color index number of the font of a cell. FORMATGET |
ASAPFULLFILENAME | Returns the full filename of your workbook. WORKBOOKPATHFILENAME |
ASAPGETCOMMENT | Returns the text from the comment a cell. COMMENTGET |
ASAPGETDOMAIN | Returns the (sub)domain from a given hyperlink (website address/url). HYPERLINKGET |
ASAPGETFONTNAME | Returns the name of the font in a cell. FORMATGET |
ASAPGETFONTSIZE | Returns the font size of a cell. FORMATGET |
ASAPGETFORMULA | FORMULATEXT added in 2013 to the Lookup & Reference category. Returns the formula from a particular cell. |
ASAPGETFORMULAINT | Returns the formula of a cell in the "international" notation. |
ASAPGETHYPERLINK | Returns the hyperlink from a cell. HYPERLINKGET |
ASAPGETNUMBERFORMAT | Returns the number format of a cell. NUMBERFORMATGET |
ASAPISBOLD | Returns TRUE if the cell is bold or FALSE if it isn't. ISBOLD |
ASAPISFORMULA | ISFORMULA added in 2013 to the Information category Returns TRUE if the cell has a formula or FALSE if it doesn't. |
ASAPLOADIMAGE | Inserts the specified image as an object and puts it at the left-top of your cell. IMAGEDISPLAY |
ASAPMERGECELLS | Joins several text strings into one text string. MERGECELLS |
ASAPRANDOMPASSWORD | Returns a random string that can be used as a password. RANDOMPASSWORD |
ASAPSHEETNAME | Returns the name of the worksheet this formula is used on. WORKSHEETNAME |
ASAPSPELLNUMBER | Returns a spelled-out number or amount. SPELLNUMBER |
ASAPSTRIPNUMBERS | Removes all numbers from a text string and removes all spaces at the beginning and end of the result. REMOVENUMBERS |
ASAPSUMBYCELLCOLOR | Adds the cells that have a certain fill color. SUMFORMAT |
ASAPSUMBYFONTCOLOR | Adds the cells that have a certain font color. SUMFORMAT |
Library Function Description |
ASAPCELLCOLORINDEX Returns the color index number of the cell. FORMATGET |
ASAPCOUNTBYCELLCOLOR Counts the number of cells in the given range that have a certain fill color. COUNTFORMAT |
ASAPCOUNTBYFONTCOLOR Counts the number of cells in the given range that have a certain font color. COUNTFORMAT |
ASAPCOUNTCHAR Counts the number of times a character occurs in a text. COUNTSUBSTRING |
ASAPCOUNTSHADES Counts the number of colored cells in your range. COUNTFORMAT |
ASAPEXTRACTFILENAME Returns the file name from a full path and filename. EXTRACTFILENAME |
ASAPEXTRACTFOLDERNAME Returns the folder name from a combined filepath and filename. EXTRACTFOLDERPATH |
ASAPEXTRACTNUMBERS Returns the numbers from a text string. EXTRACTNUMBERS |
ASAPFILENAME Returns the name of your workbook. WORKBOOKFILENAME |
ASAPFILEPATH Returns the filepath (the folder) where your workbook is stored. WORKBOOKPATH |
ASAPFILEPROPERTIES Returns the value of one of the built-in document properties for the current workbook. WORKBOOKPROPERTIES |
ASAPFONTCOLORINDEX Returns the color index number of the font of a cell. FORMATGET |
ASAPFULLFILENAME Returns the full filename of your workbook. WORKBOOKPATHFILENAME |
ASAPGETCOMMENT Returns the text from the comment a cell. COMMENTGET |
ASAPGETDOMAIN Returns the (sub)domain from a given hyperlink (website address/url). HYPERLINKGET |
ASAPGETFONTNAME Returns the name of the font in a cell. FORMATGET |
ASAPGETFONTSIZE Returns the font size of a cell. FORMATGET |
ASAPGETFORMULA FORMULATEXT added in 2013 to the Lookup & Reference category. Returns the formula from a particular cell. |
ASAPGETFORMULAINT Returns the formula of a cell in the "international" notation. |
ASAPGETHYPERLINK Returns the hyperlink from a cell. HYPERLINKGET |
ASAPGETNUMBERFORMAT Returns the number format of a cell. NUMBERFORMATGET |
ASAPISBOLD Returns TRUE if the cell is bold or FALSE if it isn't. ISBOLD |
ASAPISFORMULA ISFORMULA added in 2013 to the Information category Returns TRUE if the cell has a formula or FALSE if it doesn't. |
ASAPLOADIMAGE Inserts the specified image as an object and puts it at the left-top of your cell. IMAGEDISPLAY |
ASAPMERGECELLS Joins several text strings into one text string. MERGECELLS |
ASAPRANDOMPASSWORD Returns a random string that can be used as a password. RANDOMPASSWORD |
ASAPSHEETNAME Returns the name of the worksheet this formula is used on. WORKSHEETNAME |
ASAPSPELLNUMBER Returns a spelled-out number or amount. SPELLNUMBER |
ASAPSTRIPNUMBERS Removes all numbers from a text string and removes all spaces at the beginning and end of the result. REMOVENUMBERS |
ASAPSUMBYCELLCOLOR Adds the cells that have a certain fill color. SUMFORMAT |
ASAPSUMBYFONTCOLOR Adds the cells that have a certain font color. SUMFORMAT |
KuTools Functions
Link - extendoffice.com/product/kutools-for-excel/product-tutorials.html
Library Function | Description |
AVERAGEVISIBLE | Average Visible cells: Easily average visible cells, rows, or columns only in excel AVERAGEVISIBLE |
COUNTBYCELLCOLOR | Count by cell color: Count number of cells by a certain fill color in Excel COUNTFORMAT |
COUNTBYFONTCOLOR | Count by font color: Count number of cells by a certain font color in Excel COUNTFORMAT |
COUNTBYFONTBOLD | Count by font bold: Easily count the number of only bold cells in a range in Excel COUNTFORMAT |
COUNTCHAR | Count characters: Quickly count the occurrences of a character in a string in Excel COUNTSUBSTRING |
COUNTSHADES | Count shades: Counting numbers of cells which have been filled with color in Excel COUNTFORMAT |
COUNTVISIBLE | Count Visible Cells: Easily count visible cells, rows, or columns only in Excel COUNTVISIBLE |
SUMBYCELLCOLOR | Sum by cell color: Sum cells by a certain fill color in Excel SUMFORMAT |
SUMBYFONTCOLOR | Sum by font color: Sum cells by a certain font color in Excel SUMFORMAT |
SUMBYFONTBOLD | Sum by font bold: Quickly sum bold values / numbers only in Excel SUMFORMAT |
SUMVISIBLE | Sum Visible Cells: Easily sum visible cells, rows, or columns only in Excel SUMVISIBLE |
EXTRACTNUMBERS | Extract Numbers: Extract numbers from mixed text string with the function EXTRACTNUMBERS |
REVERSETEXT | Reverse Text: Easily reverse order of characters in a cell with functions REVERSE |
TIME2HOURS | Convert "hh:mm:ss" formatting time or text to decimal hours CONVERTTIME |
TIME2MINUTES | Convert "hh:mm:ss" formatting time or text to decimal minutes CONVERTTIME |
TIME2SECONDS | Convert "hh:mm:ss" formatting time or text to decimal seconds CONVERTTIME |
Library Function Description |
AVERAGEVISIBLE Average Visible cells: Easily average visible cells, rows, or columns only in excel AVERAGEVISIBLE |
COUNTBYCELLCOLOR Count by cell color: Count number of cells by a certain fill color in Excel COUNTFORMAT |
COUNTBYFONTCOLOR Count by font color: Count number of cells by a certain font color in Excel COUNTFORMAT |
COUNTBYFONTBOLD Count by font bold: Easily count the number of only bold cells in a range in Excel COUNTFORMAT |
COUNTCHAR Count characters: Quickly count the occurrences of a character in a string in Excel COUNTSUBSTRING |
COUNTSHADES Count shades: Counting numbers of cells which have been filled with color in Excel COUNTFORMAT |
COUNTVISIBLE Count Visible Cells: Easily count visible cells, rows, or columns only in Excel COUNTVISIBLE |
SUMBYCELLCOLOR Sum by cell color: Sum cells by a certain fill color in Excel SUMFORMAT |
SUMBYFONTCOLOR Sum by font color: Sum cells by a certain font color in Excel SUMFORMAT |
SUMBYFONTBOLD Sum by font bold: Quickly sum bold values / numbers only in Excel SUMFORMAT |
SUMVISIBLE Sum Visible Cells: Easily sum visible cells, rows, or columns only in Excel SUMVISIBLE |
EXTRACTNUMBERS Extract Numbers: Extract numbers from mixed text string with the function EXTRACTNUMBERS |
REVERSETEXT Reverse Text: Easily reverse order of characters in a cell with functions REVERSE |
TIME2HOURS Convert "hh:mm:ss" formatting time or text to decimal hours CONVERTTIME |
TIME2MINUTES Convert "hh:mm:ss" formatting time or text to decimal minutes CONVERTTIME |
TIME2SECONDS Convert "hh:mm:ss" formatting time or text to decimal seconds CONVERTTIME |
Power User Software
link - support.powerusersoftware.com/support/solutions/articles/80001023454-advanced-functions
Library Function | Description |
RangeExists | Checks if a named range exists in the defined sheet. |
ShapeExists | |
SheetExists | Checks if a worksheet name exists in the active workbook. |
LastCell | Returns the value of the last cell (bottom right) in the defined sheet |
LastCellAddress | Returns the address of the last cell (bottom right) in the defined sheet |
LastColumnNumber | Returns the number of the last column (bottom right) in the defined sheet |
LastRowNumber | Returns the number of the last row (bottom right) in the defined sheet |
SheetName | Returns the name of the sheet that contains the reference cell. |
HasFormulas | Checks if the reference cell has formulas. |
IsMerged | Checks if the reference cell is merged with other cells. |
SumColor | Returns the sum of cells in the selected range that have the same fill color as the reference cell. |
CountColor | Returns the number of cells in the selected range that have the same fill color as the reference cell. |
SumFromAllSheets | Returns the sum of the values in the reference cell for all sheets in the active workbook. |
CountUnique | Returns the number of unique values in the reference range. |
CountVisible | Returns the number of visible cells in the reference range. |
UsedRange | Returns the address of the range that is being used in the defined sheet. |
CountWords | Returns the number or words in a range that contains text. |
SlicerItems | Returns the list of items selected in the defined slicer. |
Vlookup_Max | Returns the maximum value of all results that match lookup value. |
Vlookup_Min | Returns the minimum value of all results that match lookup value. |
Hlookup_Max | Returns the maximum value of all results that match lookup value. |
Hlookup_Min | Returns the minimum value of all results that match lookup value. |
CAGR | Returns the Compound Annual Growth Rate between 2 values for a given number of years. |
Word_n | Returns the n-th word from a text. |
Concatenate_Range | Returns the concatenation of all cells within the defined range. |
Weighted_Average | Returns the average of values in a column weighted by the values in another column. |
Percent_Change | Returns the percentage that changes one value into another. |
RMS | Returns the Root Mean Square of a set of data. |
Library Function Description |
RangeExists Checks if a named range exists in the defined sheet. |
ShapeExists |
SheetExists Checks if a worksheet name exists in the active workbook. |
LastCell Returns the value of the last cell (bottom right) in the defined sheet |
LastCellAddress Returns the address of the last cell (bottom right) in the defined sheet |
LastColumnNumber Returns the number of the last column (bottom right) in the defined sheet |
LastRowNumber Returns the number of the last row (bottom right) in the defined sheet |
SheetName Returns the name of the sheet that contains the reference cell. |
HasFormulas Checks if the reference cell has formulas. |
IsMerged Checks if the reference cell is merged with other cells. |
SumColor Returns the sum of cells in the selected range that have the same fill color as the reference cell. |
CountColor Returns the number of cells in the selected range that have the same fill color as the reference cell. |
SumFromAllSheets Returns the sum of the values in the reference cell for all sheets in the active workbook. |
CountUnique Returns the number of unique values in the reference range. |
CountVisible Returns the number of visible cells in the reference range. |
UsedRange Returns the address of the range that is being used in the defined sheet. |
CountWords Returns the number or words in a range that contains text. |
SlicerItems Returns the list of items selected in the defined slicer. |
Vlookup_Max Returns the maximum value of all results that match lookup value. |
Vlookup_Min Returns the minimum value of all results that match lookup value. |
Hlookup_Max Returns the maximum value of all results that match lookup value. |
Hlookup_Min Returns the minimum value of all results that match lookup value. |
CAGR Returns the Compound Annual Growth Rate between 2 values for a given number of years. |
Word_n Returns the n-th word from a text. |
Concatenate_Range Returns the concatenation of all cells within the defined range. |
Weighted_Average Returns the average of values in a column weighted by the values in another column. |
Percent_Change Returns the percentage that changes one value into another. |
RMS Returns the Root Mean Square of a set of data. |
Professor Excel
link - professor-excel.com/features-of-professor-excel-tools-in-detail/
Library Function | Description |
PROFEXAuthor | |
PROFEXBackgroundColor | |
PROFEXCellFormatCodeFunction | |
PROFEXColumn | returns the column letter |
PROFEXCommentAuthorName | returns the name of the person who added the initial comment |
PROFEXDateCreated | |
PROFEXDateLastSaved | |
PROFEXFileSize | |
PROFEXFolderPath | |
PROFEXFontColor | |
PROFEXHasStrikethrough | |
PROFEXHyperlinkAddress | |
PROFEXIndentLevel | |
PROFEXIsVisible | |
PROFEXLastSavedBy | |
PROFEXMaxIf | |
PROFEXMinIf | |
PROFEXNoteText | |
PROFEXNumberOfHiddenWorksheets | |
PROFEXNumberOfWorksheets | built-in functions SHEETS |
PROFEXReply | returns the text of a reply comment |
PROFEXReplyAuthorName | returns the name of the person who added the nth comment |
PROFEXSheetName | |
PROFEXText | returns the text of a threaded comment |
PROFEXWeekdayName | |
PROFEXWorkbookPath |
Library Function Description |
PROFEXAuthor |
PROFEXBackgroundColor |
PROFEXCellFormatCodeFunction |
PROFEXColumn returns the column letter |
PROFEXCommentAuthorName returns the name of the person who added the initial comment |
PROFEXDateCreated |
PROFEXDateLastSaved |
PROFEXFileSize |
PROFEXFolderPath |
PROFEXFontColor |
PROFEXHasStrikethrough |
PROFEXHyperlinkAddress |
PROFEXIndentLevel |
PROFEXIsVisible |
PROFEXLastSavedBy |
PROFEXMaxIf |
PROFEXMinIf |
PROFEXNoteText |
PROFEXNumberOfHiddenWorksheets |
PROFEXNumberOfWorksheets built-in functions SHEETS |
PROFEXReply returns the text of a reply comment |
PROFEXReplyAuthorName returns the name of the person who added the nth comment |
PROFEXSheetName |
PROFEXText returns the text of a threaded comment |
PROFEXWeekdayName |
PROFEXWorkbookPath |
Power Utility Pack Functions
Library Function | Description |
CellColor | FORMATGET |
CellHasFormula | ISFORMULA added in Excel 2013 to the Information category. Returns TRUE if the cell has a formula or FALSE if it doesn't. |
CellType | CELLTYPE |
Char2 | UNICODE added in Excel 2013. |
Code2 | UNICHAR added in Excel 2013 |
Contains | CONTAINS |
CountAVisible | COUNTAVISIBLE |
CountBetween | Counts the number of values in a range that fall between two values. COUNTBETWEEN |
CreditCard | |
DaysInMonth | Returns the number of days in a month. NUMBEROFDAYS |
DollorText | Returns its numeric argument, spelled out as dollars and cents text. SPELLNUMBER |
ExcelDir | EXCELDIR |
ExtractElement | Returns the nth element of a string that uses a separator character. WORDGET |
FileExists | FILEEXISTS |
FileName | WORKBOOKFILENAME |
FontColor | FORMATGET |
HInterpolate | HLOOKUPINTERPOLATE |
InsertString | INSERTSTRING |
IsLike | ISLIKE |
LastInColumn | LASTINCOLUMN |
LastInRow | LASTINROW |
MaxAllSheets | Returns the maximum value in a particular cell across all worksheets in a workbook. ALLSHEETS_MAX |
MinAllSheets | Returns the minimum value in a particular cell across all worksheets in a workbook. ALLSHEETS_MIN |
MonthWeek | Returns the calendar week for a date serial number WEEKNUMBER |
NumberFormat | NUMBERFORMATGET |
ParseName | Splits a string that contains a name into its component parts: first name, middle name, and last name |
RemoveSpaces | REMOVESPACES |
SayIt | |
Scramble | SCRAMBLE |
SelectOne | SELECTONE |
SheetCount | SHEETS added in Excel 2013. |
SheetName | WORKSHEETNAME |
SheetOffset | SHEETOFFSET |
StaticRand | RANDARRAY. Returns a random number that doesn't change when the worksheet is recalculated. RANDOMNUMBER |
SumAllSheets | Returns the sum of all values in a particular cell across all worksheets in a workbook. ALLSHEETS |
SumVisible | Similar to Excel's SUM function, but it returns the SUM of just the visible cells. This function is well-suited for use with AutoFiltering and Outlining. SUMVISIBLE |
TimeXX | Returns a string that represents a time, displayed as hh:mm:ss:xx, where xx is hundredths of a second (or another unit).. |
TimeXX_Add | Returns a string that represents the sum of two TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TimeXX_Subtract | Returns a string that represents the difference between two TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TimeXX_Sum | Returns a string that represents the sum TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TopAverage | Returns the average of the top n values in a list. AVERAGETOP |
UniqueItems | UNIQUE added in 2021. |
User | USERNAME |
VInterpolate | VLOOKUPINTERPOLATE |
WhichDay | Returns a date that corresponds to a specified day in a month. For example, the date of the first Friday in January for a given year. WHICHDAY |
XDate | DATESBEFORE1900 |
XDate_Add | DATESBEFORE1900 |
XDate_Day | DATESBEFORE1900 |
XDate_Dif | DATESBEFORE1900 |
XDate_Dow | DATESBEFORE1900 |
XDATE_Month | DATESBEFORE1900 |
XDate_Year | DATESBEFORE1900 |
XDate_YearDif | DATESBEFORE1900 |
Library Function Description |
CellColor FORMATGET |
CellHasFormula ISFORMULA added in Excel 2013 to the Information category. Returns TRUE if the cell has a formula or FALSE if it doesn't. |
CellType CELLTYPE |
Char2 UNICODE added in Excel 2013. |
Code2 UNICHAR added in Excel 2013 |
Contains CONTAINS |
CountAVisible COUNTAVISIBLE |
CountBetween Counts the number of values in a range that fall between two values. COUNTBETWEEN |
CreditCard |
DaysInMonth Returns the number of days in a month. NUMBEROFDAYS |
DollorText Returns its numeric argument, spelled out as dollars and cents text. SPELLNUMBER |
ExcelDir EXCELDIR |
ExtractElement Returns the nth element of a string that uses a separator character. WORDGET |
FileExists FILEEXISTS |
FileName WORKBOOKFILENAME |
FontColor FORMATGET |
HInterpolate HLOOKUPINTERPOLATE |
InsertString INSERTSTRING |
IsLike ISLIKE |
LastInColumn LASTINCOLUMN |
LastInRow LASTINROW |
MaxAllSheets Returns the maximum value in a particular cell across all worksheets in a workbook. ALLSHEETS_MAX |
MinAllSheets Returns the minimum value in a particular cell across all worksheets in a workbook. ALLSHEETS_MIN |
MonthWeek Returns the calendar week for a date serial number WEEKNUMBER |
NumberFormat NUMBERFORMATGET |
ParseName Splits a string that contains a name into its component parts: first name, middle name, and last name |
RemoveSpaces REMOVESPACES |
SayIt |
Scramble SCRAMBLE |
SelectOne SELECTONE |
SheetCount SHEETS added in Excel 2013. |
SheetName WORKSHEETNAME |
SheetOffset SHEETOFFSET |
StaticRand RANDARRAY. Returns a random number that doesn't change when the worksheet is recalculated. RANDOMNUMBER |
SumAllSheets Returns the sum of all values in a particular cell across all worksheets in a workbook. ALLSHEETS |
SumVisible Similar to Excel's SUM function, but it returns the SUM of just the visible cells. This function is well-suited for use with AutoFiltering and Outlining. SUMVISIBLE |
TimeXX Returns a string that represents a time, displayed as hh:mm:ss:xx, where xx is hundredths of a second (or another unit).. |
TimeXX_Add Returns a string that represents the sum of two TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TimeXX_Subtract Returns a string that represents the difference between two TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TimeXX_Sum Returns a string that represents the sum TIMEXX time strings. The result is displayed as hh:mm:ss:xx. |
TopAverage Returns the average of the top n values in a list. AVERAGETOP |
UniqueItems UNIQUE added in 2021. |
User USERNAME |
VInterpolate VLOOKUPINTERPOLATE |
WhichDay Returns a date that corresponds to a specified day in a month. For example, the date of the first Friday in January for a given year. WHICHDAY |
XDate DATESBEFORE1900 |
XDate_Add DATESBEFORE1900 |
XDate_Day DATESBEFORE1900 |
XDate_Dif DATESBEFORE1900 |
XDate_Dow DATESBEFORE1900 |
XDATE_Month DATESBEFORE1900 |
XDate_Year DATESBEFORE1900 |
XDate_YearDif DATESBEFORE1900 |
OzGrid Functions
Library Function | Description |
AutoFilter_Criteria | |
CalculationMode | |
CalculationState | |
CellColor | FORMATGET* |
ColorRank | FORMATGET* |
ConvertCurrencyToEnglish | |
Count_Once | |
CountWords | WORDSCOUNT |
FindNth | |
Five_Con_VLookup | |
Four_Con_VLookup | |
Get_Word | WORDNO |
GetAddress | HYPERLINKGET* |
GetCommentText | COMMENTGET* |
GetFilename | WORKBOOKFILENAME* |
GetMaxBetween | Returns the largest value that is between a range. MAXBETWEEN |
HowManyDaysInMonth | |
IsFormula | ISFORMULA added in Excel 2013 to the Information category. Returns TRUE if the cell has a formula or FALSE if it doesn't. |
LastDayOfMonth | LASTDATE_INTHISMONTH |
Lookup_Occurrence | |
MyFullName | WORKBOOKPATHFILENAME* |
MyName | Returns the application's user name or the domain username. USERNAME |
NthDayOfMonth | |
OzGridLookup | |
RandLotto | RANDOMNUMBER |
ReturnLastWord | WORDNO-LAST |
ReverseCell | Returns the contents of a particular cell with all the characters reversed. REVERSE |
SheetName | WORKSHEETNAME* |
SpellNumber | SPELLNUMBER* |
SumEveryNth | |
Three_Con_VLookup | |
Two_Con_VLookup | |
VLookAllSheets | VLOOKUPALL |
WorkbookExists | FILEEXISTS* |
Library Function Description |
AutoFilter_Criteria |
CalculationMode |
CalculationState |
CellColor FORMATGET* |
ColorRank FORMATGET* |
ConvertCurrencyToEnglish |
Count_Once |
CountWords WORDSCOUNT |
FindNth |
Five_Con_VLookup |
Four_Con_VLookup |
Get_Word WORDNO |
GetAddress HYPERLINKGET* |
GetCommentText COMMENTGET* |
GetFilename WORKBOOKFILENAME* |
GetMaxBetween Returns the largest value that is between a range. MAXBETWEEN |
HowManyDaysInMonth |
IsFormula ISFORMULA added in Excel 2013 to the Information category. Returns TRUE if the cell has a formula or FALSE if it doesn't. |
LastDayOfMonth LASTDATE_INTHISMONTH |
Lookup_Occurrence |
MyFullName WORKBOOKPATHFILENAME* |
MyName Returns the application's user name or the domain username. USERNAME |
NthDayOfMonth |
OzGridLookup |
RandLotto RANDOMNUMBER |
ReturnLastWord WORDNO-LAST |
ReverseCell Returns the contents of a particular cell with all the characters reversed. REVERSE |
SheetName WORKSHEETNAME* |
SpellNumber SPELLNUMBER* |
SumEveryNth |
Three_Con_VLookup |
Two_Con_VLookup |
VLookAllSheets VLOOKUPALL |
WorkbookExists FILEEXISTS* |
MoreFunc Functions
Library Function | Description |
ANSI128 | Strips the accents from a string |
ARRAY.FILTER | Returns only the visible cells of a range (in an array) |
ARRAY.JOIN | Joins various items (ranges, unions, strings, numbers...) in a single array |
CHBASE | Converts a value from a base into another base |
COUNTDIFF | Number of unique values in a range or array |
COUNTIF.3D | Same as COUNTIF working with 3D references |
DATE.DIFF | Returns an age in years, months and days |
EASTERDATE | Date of Easter Sunday for a given year |
EVAL | Evaluates a formula or expression |
FILENAME | Name of the workbook |
FORMULATEXT | Returns the formula of a cell |
GEOMEAN.EXT | Same as GEOMEAN, higher capacity |
GETV | Returns a temporary variable value stored by SETV |
HEX.AND | Bitwise AND between hexadecimal numbers |
HEX.FORMAT | Formats and checks the validity of an hexadecimal number |
HEX.NEG | Inverts the sign of an hexadecimal number (2's complement) |
HEX.NOT | Negates (NOT) an hexadecimal number |
HEX.OR | Bitwise OR between hexadecimal numbers |
HEX.SUM | Adds hexadecimal numbers |
HSORT | Sorts a range or an array (supports up to 14 sort keys) - vertical |
HSORT.IDX | Sort index of a range or array (supports up to 14 sort keys) - vertical |
INDIRECT.EXT | same as INDIRECT working also with closed workbooks |
INTVECTOR | Returns a vector of consecutive integers in the ascending order |
ISO.WEEKNUM | ISO-compliant week number of a day. Excel 2013 added ISOWEEKNUM |
LASTROW | Last filled cell in a column. LASTINROW |
MATRIXROUND | Adjusts an array of rounded numbers (=>sum of percentages = always 100%) |
MCONCAT | Concatenates all values in a range or an array |
MDETERM.EXT | Same as MDETERM, faster and supporting larger arrays |
MINVERSE.EXT | Same as MINVERSE, faster and supporting larger arrays |
MMAX | Returns the N highest numbers of a range or an array |
MMIN | Returns the N lowest numbers of a range or an array |
MMULT.EXT | Same as MMULT, faster and supporting larger arrays |
MRAND | Series of random integers without repetitions |
NBTEXT | Converts a positive number into spelled-out text (supports 13 languages) |
PAGENUM | Page number of a cell |
PN.ISPRIME | Returns TRUE if a number is a prime number (up to 255 digits) |
PN.NEXT | Returns the next prime number (up to 255 digits) |
RECALL | Returns the previous value of the calling cell before the last calculation |
REGEX.COMP | Returns TRUE if a text matches a regular expression |
REGEX.COUNT | Counts the matches of a regular expression in a string |
REGEX.FIND | Location of a substring matching a regular expression |
REGEX.LEN | Counts the matches of a regular expression in a string |
REGEX.MID | Returns a substring matching a regular expression |
REGEX.SUBSTITUTE | Replaces a substring matching a regular expression with a new string |
SETV | Stores a value in a temporary variable |
SHEETNAME | Name of a sheet in the workbook. WORKSHEETNAME |
SHEETOFFSET | Returns a range that is a specified number of sheets from a range |
STDEV.GROUPED | Standard deviation based on a sample (data grouped in classes) |
STDEVP.GROUPED | Standard deviation based on the entire population (grouped data) |
TEXTREVERSE | Reverses the characters of a string. REVERSE |
THREED | Coerces a 3D range into a single array - enables array formulae with 3D references |
UNION.OFFSET | OFFSET-like function working with unions (discontinguous ranges) |
UNIQUEVALUES | Added in 2021 UNIQUE |
VAR.GROUPED | Variance based on a sample (data grouped in classes) |
VARP.GROUPED | Variance based on the entire population (grouped data) |
VSORT | Sorts a range or an array (supports up to 14 sort keys) - horizontal |
VSORT.IDX | Sort index of a range or array (supports up to 14 sort keys) - horizontal |
WEIGHTED.AVERAGE | Returns a weighted average |
WMID | Extracts a word or a group of words from a text |
WORDCOUNT | Number of words in a text |
XLM.GET.CELL | Informations about the formatting, location or contents of a cell or a range |
XLM.GET.DOCUMENT | Informations about the workbook (number of sheets, author, summary...) |
XLM.GET.WORKBOOK | Informations about the current sheet or the workbook (protection, type of sheet...) |
XLM.GET.WORKSPACE | Informations about the workspace (country version of Excel, startup path...) |
Library Function Description |
ANSI128 Strips the accents from a string |
ARRAY.FILTER Returns only the visible cells of a range (in an array) |
ARRAY.JOIN Joins various items (ranges, unions, strings, numbers...) in a single array |
CHBASE Converts a value from a base into another base |
COUNTDIFF Number of unique values in a range or array |
COUNTIF.3D Same as COUNTIF working with 3D references |
DATE.DIFF Returns an age in years, months and days |
EASTERDATE Date of Easter Sunday for a given year |
EVAL Evaluates a formula or expression |
FILENAME Name of the workbook |
FORMULATEXT Returns the formula of a cell |
GEOMEAN.EXT Same as GEOMEAN, higher capacity |
GETV Returns a temporary variable value stored by SETV |
HEX.AND Bitwise AND between hexadecimal numbers |
HEX.FORMAT Formats and checks the validity of an hexadecimal number |
HEX.NEG Inverts the sign of an hexadecimal number (2's complement) |
HEX.NOT Negates (NOT) an hexadecimal number |
HEX.OR Bitwise OR between hexadecimal numbers |
HEX.SUM Adds hexadecimal numbers |
HSORT Sorts a range or an array (supports up to 14 sort keys) - vertical |
HSORT.IDX Sort index of a range or array (supports up to 14 sort keys) - vertical |
INDIRECT.EXT same as INDIRECT working also with closed workbooks |
INTVECTOR Returns a vector of consecutive integers in the ascending order |
ISO.WEEKNUM ISO-compliant week number of a day. Excel 2013 added ISOWEEKNUM |
LASTROW Last filled cell in a column. LASTINROW |
MATRIXROUND Adjusts an array of rounded numbers (=>sum of percentages = always 100%) |
MCONCAT Concatenates all values in a range or an array |
MDETERM.EXT Same as MDETERM, faster and supporting larger arrays |
MINVERSE.EXT Same as MINVERSE, faster and supporting larger arrays |
MMAX Returns the N highest numbers of a range or an array |
MMIN Returns the N lowest numbers of a range or an array |
MMULT.EXT Same as MMULT, faster and supporting larger arrays |
MRAND Series of random integers without repetitions |
NBTEXT Converts a positive number into spelled-out text (supports 13 languages) |
PAGENUM Page number of a cell |
PN.ISPRIME Returns TRUE if a number is a prime number (up to 255 digits) |
PN.NEXT Returns the next prime number (up to 255 digits) |
RECALL Returns the previous value of the calling cell before the last calculation |
REGEX.COMP Returns TRUE if a text matches a regular expression |
REGEX.COUNT Counts the matches of a regular expression in a string |
REGEX.FIND Location of a substring matching a regular expression |
REGEX.LEN Counts the matches of a regular expression in a string |
REGEX.MID Returns a substring matching a regular expression |
REGEX.SUBSTITUTE Replaces a substring matching a regular expression with a new string |
SETV Stores a value in a temporary variable |
SHEETNAME Name of a sheet in the workbook. WORKSHEETNAME |
SHEETOFFSET Returns a range that is a specified number of sheets from a range |
STDEV.GROUPED Standard deviation based on a sample (data grouped in classes) |
STDEVP.GROUPED Standard deviation based on the entire population (grouped data) |
TEXTREVERSE Reverses the characters of a string. REVERSE |
THREED Coerces a 3D range into a single array - enables array formulae with 3D references |
UNION.OFFSET OFFSET-like function working with unions (discontinguous ranges) |
UNIQUEVALUES Added in 2021 UNIQUE |
VAR.GROUPED Variance based on a sample (data grouped in classes) |
VARP.GROUPED Variance based on the entire population (grouped data) |
VSORT Sorts a range or an array (supports up to 14 sort keys) - horizontal |
VSORT.IDX Sort index of a range or array (supports up to 14 sort keys) - horizontal |
WEIGHTED.AVERAGE Returns a weighted average |
WMID Extracts a word or a group of words from a text |
WORDCOUNT Number of words in a text |
XLM.GET.CELL Informations about the formatting, location or contents of a cell or a range |
XLM.GET.DOCUMENT Informations about the workbook (number of sheets, author, summary...) |
XLM.GET.WORKBOOK Informations about the current sheet or the workbook (protection, type of sheet...) |
XLM.GET.WORKSPACE Informations about the workspace (country version of Excel, startup path...) |
link - support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext