Workaround for the TEXTSPLIT function – LAMBDA function
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]

Extract a unique distinct list across multiple columns and rows sorted based on frequency
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in […]

How to use the EXPAND function
What is the EXPAND function? The EXPAND function increases a cell range or array by a specified number of columns […]

How to use the CHOOSECOLS function
The CHOOSECOLS function returns given columns from a cell range or array. Dynamic array formula in cell B9: =CHOOSECOLS(B2:D5, 2, […]

How to use the CHOOSEROWS function
The CHOOSEROWS function returns given rows from a cell range or array. Dynamic array formula in cell B9: =CHOOSEROWS(B2:D5, 2, […]

How to use the DROP function
The DROP function removes a given number of rows or columns from a 2D cell range or array. Dynamic array […]

How to use the TAKE function
The TAKE function returns a given number of rows or columns from a 2D cell range or array. Dynamic array […]

How to use the WRAPCOLS function
The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of […]

How to use the WRAPROWS function
The WRAPROWS function rearranges values from a single row to a 2D cell range. Dynamic array formula in cell B8: […]

How to use the TOROW function
The TOROW function rearranges values from a 2D cell range to a single row. Dynamic array formula in cell B8: […]

How to use the TOCOL function
The TOCOL function lets you rearrange values in 2D cell ranges to a single column. Dynamic array formula in cell […]

How to use the HSTACK function
What is the HSTACK function? The HSTACK function lets you combine cell ranges or arrays, it joins data to the […]

How to use the VSTACK function
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the […]

Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]

Rearrange values in a cell range to a single column
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

Multiply numbers in each row by entire cell range
This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with […]

Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

SUMIF across multiple sheets
This post describes two ways to a search multiple ranges and calculate a total of the corresponding values across worksheets […]

Filter unique distinct records case sensitive
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]

Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Fuzzy lookups
In this post I will describe a basic user defined function with better search functionality than the array formula in […]

Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Filter words containing a given string in a cell range
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]

Sort rows based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

Find empty hours in a weekly schedule
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

Merge two columns with possible blank cells
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

Merge two columns
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract unique distinct values from a multi-column cell range
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]

How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]