'ISERROR function' category
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 […]
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 […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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.
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 […]
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 […]
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 […]
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. […]
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 […]
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 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 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 a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
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? […]
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 […]
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 […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
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