How to find the smallest number excluding zeros
Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are […]

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
The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts […]

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

Reverse two-way lookups
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]

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

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

Find smallest and largest unique number
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

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

Find entry based on conditions
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]

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

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
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

Get the smallest number larger than a given number
This article demonstrates how to extract the smallest number larger than a condition and the largest number smaller than a […]

Filter duplicate values in a range using “contain” condition
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

Extract unique distinct text values containing string in a range
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

Filter duplicate values from a range that begins with string
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

Extract unique distinct values from cell range that begins with string
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]

Lookup two index columns
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

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

Unique distinct list from a column sorted A to Z
Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

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

How to highlight MAX and MIN value based on month
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

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

Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Filter common values between two 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
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

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 a unique distinct list sorted from A-Z from range
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

Extract a list of alphabetically sorted duplicates from a column
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

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 highlight duplicate values
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]

Lookup the nearest date
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

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
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

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 latest date in a list
The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

Find the smallest value in a list that is larger than a number
Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]