'User Defined Function' category
Extract text between words [UDF]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Count comma separated values
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
Lookup multiple values in one cell
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Learn how to return values based on where you enter the UDF
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Reorganize data [UDF]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
Two-way lookup using multiple tables [UDF]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
SUMIF across multiple sheets [UDF]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
How to save custom functions and macros to an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
Filter unique distinct records (case sensitive) [UDF]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
Filter unique distinct values (case sensitive) [UDF]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Fuzzy lookups [UDF]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
Find positive and negative amounts that net to zero [UDF]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Find numbers in sum [UDF]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
List permutations no repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
Filter not shared values out of two cell ranges [UDF]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
Filter values in common between two cell ranges [UDF]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
Filter duplicates in a large dataset [UDF]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
Count unique distinct values in a large dataset [UDF]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Filter unique strings from a cell range
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function and […]
This post describes how to split words in a cell range into a cell each using a custom function and […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values
Excel categories
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.