'IFERROR 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 […]
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 […]
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 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 […]
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 […]
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 […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
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 […]
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.
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 […]
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 […]
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 […]
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 […]
How to build a Team Generator – different number of people per team
JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]
JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]
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 automatically add new items to a drop down list
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
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 […]
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 […]
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 […]
How to use the Scroll Bar
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
How to use the IFERROR function
The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]
The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]
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, […]
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 […]
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 […]
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 […]
Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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.
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 […]
Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
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 […]
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 […]
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 […]
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 […]
Team Generator
Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]
Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]
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. […]
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 […]
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.
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 […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
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 three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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 three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
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 […]
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 […]
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 […]
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 […]
Working with unique values
What's on this page Extract unique values from two columns - Excel 365 Extract unique values from two columns - […]
What's on this page Extract unique values from two columns - Excel 365 Extract unique values from two columns - […]
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 […]
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 […]
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 […]
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 […]
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