'IF function' category
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 solve simultaneous linear equations in Excel
This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]
This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]
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 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 […]
Count cells containing text from list
Table of Contents Count cells containing text from list Count entries based on date and time Count cells with text […]
Table of Contents Count cells containing text from list Count entries based on date and time Count cells with text […]
INDEX MATCH – multiple results
This article demonstrates how to work with multiple criteria using INDEX and MATCH functions. Table of Contents INDEX MATCH - […]
This article demonstrates how to work with multiple criteria using INDEX and MATCH functions. Table of Contents INDEX MATCH - […]
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 […]
Rearrange values using formulas
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
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 […]
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
The image above shows how to summarize work hours using the new GROUPBY function in Excel 365. This is demonstrated […]
The image above shows how to summarize work hours using the new GROUPBY function in Excel 365. This is demonstrated […]
How to generate a round-robin tournament
This article demonstrates macros that create different types of round-robin tournaments. Table of contents Basic schedule - each team plays […]
This article demonstrates macros that create different types of round-robin tournaments. Table of contents Basic schedule - each team plays […]
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 […]
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
Table of Contents Find and return the highest number and corresponding date based on a condition Lookup value based on […]
Table of Contents Find and return the highest number and corresponding date based on a condition Lookup value based on […]
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.
Array formulas allows you to do advanced calculations not possible with regular formulas.
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, […]
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 […]
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 […]
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 […]
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 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, […]
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.
Repeat values across cells
Table of Contents Repeat values Repeat the range according to criteria in loop Find the most/least consecutive repeated value […]
Table of Contents Repeat values Repeat the range according to criteria in loop Find the most/least consecutive repeated value […]
Working with date ranges
Table of Contents Find date range based on a date Sort dates within a date range Create date ranges that […]
Table of Contents Find date range based on a date Sort dates within a date range Create date ranges that […]
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 […]
Working with Relational Tables in Excel
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 […]
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 […]
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 […]
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 […]
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 […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Use a drop down list to search and return multiple values
Table of Contents Use a drop down list to search and return multiple values How to automatically add new items […]
Table of Contents Use a drop down list to search and return multiple values How to automatically add new items […]
Extract unique distinct values from an Excel Table 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 […]
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. […]
Working with running totals
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
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 […]
Monthly calendar template
Table of Contents Monthly calendar template Monthly calendar template 2 Calendar - monthly view - Excel 365 Calendar - monthly […]
Table of Contents Monthly calendar template Monthly calendar template 2 Calendar - monthly view - Excel 365 Calendar - monthly […]
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 […]
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 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 […]
Plot date ranges in a calendar
Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 Highlight events in […]
Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 Highlight events in […]
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 […]
Split data across multiple sheets – VBA
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]
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 […]
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 […]
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 all rows from a range that meet criteria in one column
Lookup with criteria and return records.
Lookup with criteria and return records.
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 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 […]
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 multiple non adjacent cell ranges
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 […]
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 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
This article demonstrates Excel formulas that allows you to list unique distinct values from a single column and sort them […]
This article demonstrates Excel formulas that allows you to list unique distinct values from a single column and sort them […]
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 […]
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 […]
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