Array Manipulation Functions
Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]

How to use the MAKEARRAY function
The MAKEARRAY function returns an array with a specific number of rows and columns calculated by applying a LAMBDA function. […]

How to copy non contiguous cell ranges
Table of Contents How to copy non contiguous cell ranges How to copy every n-th row from a list 1. […]

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 […]

How to use the COLUMNS function
What is the COLUMNS function? The COLUMNS function allows you to calculate the number of columns in a cell range […]

If cell contains text from list
This article demonstrates several techniques to check if a cell contains text based on a list. The first example shows […]

Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

Rearrange values using formulas
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

How to extract rows containing digits [Formula]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]

Sort items by adjacent number in every other value
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

How to color chart bars based on their values
(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Sort by multiple columns
This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then […]

Use a drop down list to search and return multiple values
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

How to compare two data sets
Table of Contents How to compare two data sets - Excel Table and autofilter Filter shared records from two tables […]

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 […]

Create a drop down calendar
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

VLOOKUP/XLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]

5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.

Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.

Perform multiple partial matches and return records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

How to create date ranges in Excel
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Split values equally into groups
  Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]

Partial match with two conditions and return multiple results
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]