## 'IF function' category

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

Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]

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

Table of Contents How to use the DATE function How to use the DATEDIF function How to use the DATEVALUE […]

Array Manipulation Functions

Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]

Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]

How to track sector performance in the stock market – Excel template

The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier […]

The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier […]

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

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

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

This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]

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

Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]

Count cells with text

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

Get the latest revision

Table of Contents Get the latest revision Create a list with most recent data available 1. Get the latest revision […]

Table of Contents Get the latest revision Create a list with most recent data available 1. Get the latest revision […]

How to extract numbers from a cell value

What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

Extract specific word based on position in cell value

Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]

Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]

AVERAGE based on criteria

This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]

This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]

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

AVERAGE ignore NA()

The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]

The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]

AVERAGE ignore blanks

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

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

SUMPRODUCT and nested IF functions

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

SUMPRODUCT and IF function

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

INDEX MATCH – multiple results

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]

If cell equals value from list

This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]

This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]

If cell contains text from list

This article demonstrates several techniques to check if a cell contains text based on a list. The first example shows […]

This article demonstrates several techniques to check if a cell contains text based on a list. The first example shows […]

IF with OR function

This article shows examples of how to use the IF function with the OR function. Table of Contents IF with […]

This article shows examples of how to use the IF function with the OR function. Table of Contents IF with […]

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

If cell contains text

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

Use IF + COUNTIF to evaluate multiple conditions

The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]

The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]

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

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

How to use nested IF functions

Nested IF statements in a formula are multipleÂ combined IF functions so more conditions and outcomes become possible. They all are […]

Nested IF statements in a formula are multipleÂ combined IF functions so more conditions and outcomes become possible. They all are […]

Distribute values across numerical ranges

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

How to create running totals

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

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

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

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

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

Concatenate unique distinct values

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Partial match and return value with highest level

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

Split search value using a delimiter and search for each substring

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

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.

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Rotating unique groups with no repeat

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

Time sheet for work

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

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

Calendar – monthly view

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

How to calculate and plot pivots on an Excel chart

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

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

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]

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

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

This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with […]

Follow stock market trends – trailing stop

This time I want to demonstrate an alternative way to identify a majorÂ trend in the stock market. The previous post […]

This time I want to demonstrate an alternative way to identify a majorÂ trend in the stock market. The previous post […]

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

Find and return the highest number and corresponding date based on a condition

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

How to color chart bars based on their values

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

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

Working with overlapping date ranges

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

Split expenses calculator

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

How to count repeating values

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

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

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

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

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

Working with classic ciphers in Excel

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Highlight unique values in a filtered Excel table

Table of contents Highlight unique values in a filtered Excel table Highlight unique values and unique distinct values in a […]

Table of contents Highlight unique values in a filtered Excel table Highlight unique values and unique distinct values in a […]

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

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

Lookup value based on two critera – second criteria is the adjacent value and its position in a given list

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

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

Identify rows of overlapping records

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

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

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Lookup with any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

Find earliest and latest overlapping dates in a set of date ranges based on a condition

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

How to select and delete blank cells

This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

Pivot Table calendar

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Practice basic arithmetic calculations in Excel

This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above […]

This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above […]

Create a hyperlink linked to the result of a two-dimensional lookup

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]

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

This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]

Highlight duplicates in a filtered Excel Table

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Convert array formula to a regular formula

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

Repeat values across cells

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Group rows based on a condition

What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]

What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]

Working with date ranges

Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]

Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]

Compare the performance of your stock portfolio to S&P 500 using Excel

Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]

Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]

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

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

Highlight lookups in relational tables

This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

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

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

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

Lookups in relational tables

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

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

Excel calendar

Table of Contents Excel monthly calendar - VBA Â Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Table of Contents Excel monthly calendar - VBA Â Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

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

Use drop down lists and named ranges to filter chart values

This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]

This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]

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

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

Change chart axis range programmatically

This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

Plot buy and sell points in an Excel Chart based on two moving averages

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

Add buy and sell points to a stock chart

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

Lookup multiple values across columns and return a single value

Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]

Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]

Create date ranges that stay within month

This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]

This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]

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

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

Use a drop down list to filter and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

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

Automate data entry [VBA]

This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

How to create random numbers, text strings, dates and time values

The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

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

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

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

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

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

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

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

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

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

Rearrange data

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

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

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

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

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

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

Vlookup across multiple sheets

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

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 formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

VLOOKUP a multi-column range and return multiple values.

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

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

Table of Contents How to compare two data sets - Excel Table and autofilter Filter shared records from two tables […]

VLOOKUP – Return multiple unique distinct values

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

Bill reminder in excel

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Fuzzy VLOOKUP

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

A Comprehensive Guide to Splitting Text in Excel

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

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

Filter strings containing a given substring in a cell range

This post describes ways to extract all matching strings from cells in a given cell range if they contain a […]

This post describes ways to extract all matching strings from cells in a given cell range if they contain a […]

Filter overlapping date ranges

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

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

Tracking a stock portfolio #2

This is follow up post to:Â Tracking a stock portfolio in excel (auto update) In this post we are going to […]

This is follow up post to:Â Tracking a stock portfolio in excel (auto update) In this post we are going to […]

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

Monthly calendar template

Table of Contents Monthly calendar template Monthly calendar template 2 1. Monthly calendar template The image above shows a calendar […]

Table of Contents Monthly calendar template Monthly calendar template 2 1. Monthly calendar template The image above shows a calendar […]

Count unique distinct records

Table of Contents Count unique distinct records Count records with possible blank rows in data set How to count blank […]

Table of Contents Count unique distinct records Count records with possible blank rows in data set How to count blank […]

Compare tables: Filter records occurring only in one table

Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]

Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

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

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

Create a random playlist

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

Vlookup with multiple matches returns a different value

Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]

Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to the weekly schedule I built in a previous […]

In this post I am going to add one more function to the weekly schedule I built in a previous […]

Create numbers based on numerical ranges

Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]

Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]

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

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

Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

Extract a unique distinct list sorted from A to Z ignore blanks

This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

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

This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

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

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

Plot date ranges in a calendar

Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

Merge two columns with possible blank cells

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

List all unique distinct rows in a given month

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see 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. […]

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

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

Automate net asset value (NAV) calculation on your stock portfolio

Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

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

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

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

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

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

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Most frequent value between two dates

I will in this article show you how to extract the most frequent value (text or number) between two dates […]

I will in this article show you how to extract the most frequent value (text or number) between two dates […]

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

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

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.

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]

Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]

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

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

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 all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Lookup with criteria and return records.

Create unique distinct list sorted based on text length

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

How to list unique distinct values sorted by frequency

What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]

What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]

Extract unique distinct values sorted from A to Z

Table of Contents List a unique distinct list from a column sorted A to Z Extract a unique distinct list […]

Table of Contents List a unique distinct list from a column sorted A to Z Extract a unique distinct list […]

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

Extract unique distinct values A to Z from a range and ignore blanks

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

Find min and max unique and duplicate numerical values

Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

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

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 values in common between two cell ranges

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Filter values occurring in range 1 but not in range 2

Table of Contents Filter values occurring in range 1 but not in range 2 Filter not shared values out of […]

Table of Contents Filter values occurring in range 1 but not in range 2 Filter not shared values out of […]

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

Extract duplicates from a multi-column cell range

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

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

Count a specific text string in a cell

Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]

Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]

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

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

How to perform a two-dimensional lookup

Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]

Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]

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

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

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

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

Find missing numbers

Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]

Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]

Count unique distinct values

This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]

This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]

Split values equally into groups

Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]

Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

How to highlight duplicate values

Table of Contents How to highlight duplicate values Highlight the smallest duplicate number Highlight more than once taken course in […]

Table of Contents How to highlight duplicate values Highlight the smallest duplicate number Highlight more than once taken course in […]

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

Filter unique distinct values based on a date range

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Sort column based on frequency

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

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 a unique distinct list sorted from A to Z

Table of Contents Extract a unique distinct list sorted from A to Z Unique distinct list sorted alphabetically based on […]

Table of Contents Extract a unique distinct list sorted from A to Z Unique distinct list sorted alphabetically based on […]

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

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Sort a column alphabetically

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Extract unique distinct values from a multi-column cell range

This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]

This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]

Calculate machine utilization

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

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

Extract records between two dates

This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]

This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]

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

How to extract email addresses from an Excel sheet

Question: How to extract email addresses from this sheet? Answer: It dependsÂ on how the emails are populated in your worksheet? […]

Question: How to extract email addresses from this sheet? Answer: It dependsÂ on how the emails are populated in your worksheet? […]

Filter unique distinct records

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

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

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

Find closest value

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

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

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

In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]

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

This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]

This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]

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