## 'ROWS function' category

How to copy every n-th row from a list

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

SMALL function with multiple cell ranges

Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]

Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]

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

INDEX and MATCH – multiple criteria and multiple results

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

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

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

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 using criteria

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

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

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

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

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

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

Basic invoice template

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Extract week ranges based on a given date range

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

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

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

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

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

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Compare two columns and return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

Create a drop down list containing alphabetically sorted values

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

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

Sort records based on two columns

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Case sensitive lookup and return multiple values

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

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

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

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

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

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

Convert dates into date ranges

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

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

Return multiple values if in range

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

Sort two columns

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

VLOOKUP and return multiple matches based on many criteria.

Search values distributed horizontally and return corresponding value

Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]

Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]

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 records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

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

Lookup with criteria and return records.

Lookup with criteria and return records.

Sort text values by length

Array formula in B2: =INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)) copied down as far as needed. To enter an […]

Array formula in B2: =INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)) copied down as far as needed. To enter an […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

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

Create a list of dates with blanks between quarters

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

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

Filter duplicate values and sort by corresponding date

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

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) How to create an array formula How to copy […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Merge two columns

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

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

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

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

Sort dates within a date range

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

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

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

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Delete blanks and errors in a list

Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]

Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]

How to extract not shared values in two columns

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

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

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

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

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