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

Lookup with criteria and return records.

This extensive list provides detailed information including syntax, arguments, return values and examples for most of the functions used in Excel.

The list is easy to search and lets you sort by function name or category.

This is a great place to start learning Excel functions.

Learn to build charts that let you visualize values in order to analyze and comprehend data.

Learn to format data based on a condition or criteria to make you more productive and effective.

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

Lookup with criteria and return records.

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

Add checkboxes to a sheet (1/2) [VBA]

In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]

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.

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

Filter an Excel defined Table based on selected cell [VBA]

In this post I am going to demonstrate how to quickly apply a filter to a table. I am using […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Populate a combobox with values from a pivot table [VBA]

In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]

Copy selected rows (checkboxes) (2/2)

This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]

Filter unique values from a cell range

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

The easiest way to sum a cell range is to simply select the cell range and read the values in […]

Count cells between specified values

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

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

Remove print preview lines (Page Breaks)

Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

How to use the COUNTIF function to count not blank cells

The COUNTIF function is very capable of counting non-empty values. Column B above have a few blank cells, they are […]

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

Add values to a regular drop-down list programmatically [VBA]

In this tutorial I am going to show you how to add values to drop down list in cell C2. This […]

Use hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Optimize pick path in a warehouse

As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]

How to create a combined stacked area and a clustered column chart

Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]

How to highlight weekends [Conditional Formatting]

The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]

Highlight above average values

The image above shows values above average using conditional formatting applied to cell range B2:G13. Cell range B2:G13 contains 72 […]

Convert column number to column letter

Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

Get date ranges from a schedule

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

Copy data from workbooks in folder and subfolders

I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]

Compare two columns and highlight differences

A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column […]

Extract unique distinct records based on a criterion

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

Find the smallest value in a list that is larger than a number

Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]

How to calculate a date based on specific weekday in a month

Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]

Extract unique distinct values based on the 4 last characters

Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

How to highlight unique distinct values

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

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

Improve your X Y Scatter Chart with custom data labels

The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]

How to extract email addresses from an Excel sheet

Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]

How to ignore zeros using the SMALL function

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros.

Filter an Excel defined Table programmatically [VBA]

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]

Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]

Search for a file in folder and subfolders [UDF]

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

How to count unique distinct values based on a date

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

Populate a list box with unique distinct values from a filtered Excel table [VBA]

Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]

Populate a combo box (form control) [VBA]

In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]

Filter unique distinct records using criteria

This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]

Extract unique distinct values based on a filtered Excel defined Table

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

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

Extract records containing digits [Formula]

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Two-way lookup using multiple tables [UDF]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

How to ignore error values using the SMALL function

The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]

Count entries based on date and time

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

How to add a custom-made item to the shortcut menu [VBA]

This post describes how to add a new custom-built item to the shortcut menu in Excel, when you right-click a cell […]

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

Filter unique distinct values, sorted and blanks removed from a range

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Remove common records between two data sets

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

SUMIF across multiple sheets [UDF]

This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]

Select cell A1 on all sheets before you close a workbook [VBA]

This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The […]

Insert blank rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

How to sort a data set in a custom order

Your boss wants you to sort the company's products by a new criterion, quality. You receive a list from your […]

Vlookup across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

List files in a folder and subfolders [UDF]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

How to save custom functions and macros to an Add-In

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

7 days (weekly) date ranges using a formula

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Count unique distinct records (rows) in a Pivot Table

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Calculate running average of last 10 data with random blank cells

Question: List of data and blank cells in a column which will be added from day to day. There are […]

Compare your stock portfolio with S&P500 in Excel

By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]

Filter unique distinct records (case sensitive) [UDF]

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]

Filter unique distinct values (case sensitive) [UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

Combine cell ranges eliminating blanks

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Dependent drop down lists – Enable/Disable selection filter

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

Filter records based on a date range and a text string

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

Text to columns: Split words in a cell [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Highlight records containing text strings (AND Logic)

The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]

Filter words containing a given string in a cell range [UDF]

The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]