How to use the ISERROR function
What is the ISERROR function? The ISERROR function returns TRUE if a cell returns an error. What is the difference […]

Highlight empty cells
Table of Contents Highlight empty cells Explaining CF formula How to apply conditional formatting Formulas returning nothing Cells with hidden […]

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

SUMPRODUCT if not blank
This article describes ways to work with the SUMPRODUCT function and blanks, error values, N/A# errors. Table of Contents SUMPRODUCT […]

Two-dimensional lookup using two tables
The following formula performs a two-way lookup in two different tables. Formula in cell D20: =IFERROR(INDEX(C4:G8, MATCH(C19, B4:B8, 0), MATCH(C18, […]

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

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

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

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

Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

How to highlight differences in price lists
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

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

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

Automate Excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

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

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

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

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

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

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

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

Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

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

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

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