Highlight unique distinct records
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]

Extract unique distinct records from two data sets
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

Highlight cells based on coordinates
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]

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

Match two columns and return another value on the same row
This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

IF function with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

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

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

Search for a sequence of cells based on wildcard search
This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

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

How to group items by quarter using formulas
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

Merge tables based on a condition
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

Prevent duplicate records in a worksheet
This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

Assign records unique random text strings
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Lookup using multiple conditions
Debraj Roy asks: Hi Oscar, I failed to search for "Ask" Section.. so submitting my query here.. Apologize for hijacking […]

Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

Unique distinct records sorted based on count or frequency
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

Auto populate a worksheet
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Working with Conditional Formatting formulas
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

Watch schedule that populates vacation time
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Label groups of duplicate records
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

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

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

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

How to compare two data sets
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]

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

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

Filter duplicate records
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]

Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is […]

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

Highlight duplicate columns
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]

Extract duplicate records
This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

Highlight duplicate records
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

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

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

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

List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

Remove duplicates based on date
Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]

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

VLOOKUP/XLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]

Lookup two index columns
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Create a list of duplicates where adjacent cell value meets a condition
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

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

Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]