'ROWS function' category



test
Engineering functions – D to IMC
Table of Contents How to use the DEC2BIN function How to use the DEC2HEX function How to use the DEC2OCT […]
test
Date and Time Functions – N to Z
Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]
test
Date and Time Functions – A to M
Table of Contents How to use the DATE function How to use the DATEDIF function How to use the DATEVALUE […]
test
Array Manipulation Functions
Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]
test
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. […]
test
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 […]
test
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 […]
test
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 […]
test
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. […]
test
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 […]
test
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 […]
test
SMALL function – INDEX MATCH
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
test
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 […]
test
SMALL function – multiple conditions
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
test
SMALL function for text
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
test
SMALL function ignore duplicates
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
test
INDEX MATCH – multiple results
Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]
test
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 […]
test
Rearrange values using formulas
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
test
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). […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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.
test
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 […]
test
Compare two columns in different worksheets
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
test
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. […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
test
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 […]
test
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 […]
test
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 […]
test
Sort by multiple columns
This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]
test
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 […]
test
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 […]
test
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, […]
test
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 […]
test
Working with date ranges
Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]
test
How to use VLOOKUP/XLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
test
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 […]
test
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 […]
test
Shift Schedule
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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" […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
Team Generator
Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]
test
Create numbers based on numerical ranges
Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]
test
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 […]
test
Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
test
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 […]
test
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 […]
test
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 […]
test
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.
test
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 […]
test
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]
test
Find missing numbers
Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]
test
Split values equally into groups
  Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]
test
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. […]
test
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 […]
test
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 […]
test
Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
test
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 […]
test
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 […]
test
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 […]

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


Home page
Excel Functions
Excel Formulas
Excel Tables
Advanced Filter
Data Validation
Drop Down List
Named Ranges
Excel Solver
Charts
Conditonal Formatting
Pivot Tables
VBA
Macros
UDFs
Archive