ROWS function
Excel » Functions » Lookup and reference » ROWS function »
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 […]
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 […]
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. […]
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 […]
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 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 […]
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 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 […]
INDEX MATCH – multiple results
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 […]
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 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). […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
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 […]
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 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 […]
Compare two columns and extract differences
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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" […]
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 […]
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) […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]
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 […]
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 […]
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 […]
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 […]
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 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.
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
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.
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 […]
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) […]
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
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 […]
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: […]
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: […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
The picture above shows how to merge two columns into one list using a formula. If you are looking […]
Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]
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 […]
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 […]
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, […]
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. […]
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 […]
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 […]
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 […]
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 […]
Extract shared values between two columns
Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]
What values are missing in List 1 that exists i List 2?
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]