How to sum overlapping time
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

How to use the MIN function
What is the MIN function? The MIN function allows you to retrieve the smallest number in a cell range. When […]

SMALL function ignore duplicates
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

Partial match and return value with highest level
This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

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

Follow stock market trends – trailing stop
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]

Find and return the highest number and corresponding date based on a condition
This article describes how to filter records based on the maximum value of a specific item. There are names in […]

A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

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

Split expenses calculator
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 […]

Find the most/least consecutive repeated value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

Extract the most repeated adjacent values in a column
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]

Sort by multiple columns
This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

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

Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

Hover with mouse cursor to change stock in a candlestick chart
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]

Repeat values across cells
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

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

Change chart axis range programmatically
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

Label groups of duplicate records
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

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

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

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

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

Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

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

Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

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

Extract unique distinct values sorted from A to Z
Table of Contents List a unique distinct list from a column sorted A to Z Extract a unique distinct list […]

Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

Find min and max unique and duplicate numerical values
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

Filter values in common between two cell ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Filter values occurring in range 1 but not in range 2
Table of Contents Filter values occurring in range 1 but not in range 2 Filter not shared values out of […]

Sort a range from A to Z [Array formula]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

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

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

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

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

Lookup the nearest date
Table of Contents Lookup the nearest date Lookup min max values within a date range 1. Lookup the nearest date […]

How to quickly find the maximum or minimum value [Formula]
This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

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

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

Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]

Find the smallest number in a list that is larger than a given number
This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]