## 'IFERROR function' category

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

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

How to count blank cells

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

Get the latest revision

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

Identify all characters in a cell value

Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]

Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]

Extract last word in cell

Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]

Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]

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 ways to check if a cell contains any value based on a list. The first example […]

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Two-dimensional lookup using two tables

The following formula performs a two-way lookup in two different tables.

The following formula performs a two-way lookup in two different tables.

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

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

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

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

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

VLOOKUP and return multiple values across columns

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

Locate lookup values in an Excel table [HYPERLINK]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

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

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

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 date ranges in a calendar part 2

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

Extract table headers based on a condition

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

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

Free School Schedule Template

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

Basic invoice template

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Extract week ranges based on a given date range

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

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

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

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

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

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

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

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

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 [Array formula]

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.

Text to columns: Split words in a cell

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

Tracking a stock portfolio in excel (auto update)

Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]

Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]

Count unique distinct records

The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]

The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]

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

Dynamic team generator

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

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

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

Search for a cell value in a dataset

Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]

Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]

Sort text cells alphabetically from two columns

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Merge two columns

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

Merge three columns 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 create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

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 list of duplicates from two columns combined

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

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

Identify missing numbers in two columns based on a numerical range

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

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

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

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

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

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

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Question: How do I count how many times a word exists in a range of cells? It does not have […]

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

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

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

How to search for a string in a column

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

Remove blank cells

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

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

## Excel categories

Latest updated articles.

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

More than 1300 formulas organized in subcategories.

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

An Excel feature that lets you visualize data in a graph.

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDF stands for User Defined Functions and is custom built functions anyone can create.

A list of all published articles.