## 'ROWS function' category

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

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

How to decode URL-encoded strings

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

How to replace part of formula in all cells

Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]

Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]

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

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

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

How to use the ROWS function

The ROWS function allows you to calculate the number of rows in a cell range. The exampleÂ above shows that cell […]

The ROWS function allows you to calculate the number of rows in a cell range. The exampleÂ above shows that cell […]

SMALL function – INDEX MATCH

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

How to ignore zeros using the SMALL function

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

SMALL function – multiple conditions

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

SMALL function for text

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

SMALL function ignore duplicates

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

INDEX MATCH – multiple results

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]

INDEX MATCH with multiple criteria

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

Rearrange values using formulas

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

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

How to extract a case sensitive unique list from a column

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

Count unique distinct values based on a condition

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

Extract a list of alphabetically sorted duplicates based on a condition

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

Extract duplicate values with exceptions

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

Filter duplicate values based on criteria

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

Use VLOOKUP and return multiple values sorted from A to Z

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

How to use Excel Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

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

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

Sort values by corresponding text arranged in a column

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

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

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

Follow stock market trends – Moving Average

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

Sort based on frequency row-wise

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

Find empty dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Dynamic scoreboard

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

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

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Extract the most repeated adjacent values in a column

The array formula in cell D3 returnsÂ the the length of longest consecutive sequence of a value in column A. Â Cell […]

The array formula in cell D3 returnsÂ the the length of longest consecutive sequence of a value in column A. Â Cell […]

Search for a sequence of values

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

Sort by multiple columns

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

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

Lookup and return multiple sorted values based on corresponding values in another column

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

Compare two columns and extract differences

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

Find all sequences of consecutive dates

This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]

This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]

How to sort a data set using three different approaches, built-in tools, array formulas, and VBA

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

Working with date ranges

Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]

Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]

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

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

Populate drop down list with filtered Excel Table values

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

Shift Schedule

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Partial match and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

Extract unique distinct values in a filtered list

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

VLOOKUP in a filtered Excel Table and return multiple values

This post describes how to search filtered values in an ExcelÂ defined TableÂ using a condition given in cell 12 and return […]

This post describes how to search filtered values in an ExcelÂ defined TableÂ using a condition given in cell 12 and return […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

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

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

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

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

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

Compare two columns and return differences

This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]

This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]

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

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

True round-robin tournament

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Team Generator

Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]

Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]

Extract dates from a cell block schedule

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

Create numbers based on numerical ranges

Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]

Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]

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

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

Create dependent drop down lists containing unique distinct values

Table of Contents Create dependent drop down lists containing unique distinct values Create dependent drop down lists containing unique distinct […]

Table of Contents Create dependent drop down lists containing unique distinct values Create dependent drop down lists containing unique distinct […]

Extract dates from overlapping date ranges

The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]

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

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

Remove duplicates within same month or year

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

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

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

Create number series

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

How to return a value if lookup value is in a range

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

Create a quartely date range

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

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.

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

Filter unique values sorted from A to Z

A unique value is a value thatÂ only exists once in a list. A unique distinct list contains all cell values […]

A unique value is a value thatÂ only exists once in a list. A unique distinct list contains all cell values […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Lookup with criteria and return records.

Extract numbers from a column

I this article I will show you how to get numerical values from a cell range manually and using an […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

Reverse a list ignoring blanks

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

List values with past date

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

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

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Sort a range from A to Z [Array formula]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell rangeÂ $B$2:$E$5 contains text values in random […]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell rangeÂ $B$2:$E$5 contains text values in random […]

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

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Sort text cells alphabetically from two columns

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Merge cell ranges into one list

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

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

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

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Partial match based on two conditions

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

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

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

Find missing numbers

Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]

Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]

Split values equally into groups

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

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

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Partial match for multiple strings – AND logic

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Sort a column alphabetically

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

How to extract not shared values in two columns

Question: How do I remove common values between two lists? Answer: I created the formulas demonstrated in this article, they […]

Question: How do I remove common values between two lists? Answer: I created the formulas demonstrated in this article, they […]

Extract shared values between two columns

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

What values are missing in List 1 that exists i List 2?

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

Cleaning Up Excel Worksheets: Eliminating Blank Cells, Rows, and Errors

In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]

In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]

## 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