Latest modified blog articles

Create numbers based on numerical ranges

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

Extract dates from overlapping date ranges

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

Find empty hours in a weekly schedule

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

How to use the SPLIT function [VBA]

The picture above shows a user-defined function (UDF) that splits the string in cell B3 using delimiting character "|". The SPLIT function […]

Calendar with scheduling [vba]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

Find closest value

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Compare file names in two different folder locations and their sub folders

Today I would like to share a macro that compares the content in two different folders and their sub folders. […]

Count groups in calendar

Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]

Create a unique distinct sorted list containing both numbers text removing blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]

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

Convert dates into date ranges

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Yet another excel calendar

The first sheet contains an overview. You have the option to select a year, date and color. Days with many "events" […]

Calendar – monthly view

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]

An animated column chart

The drop down list lets you select a region. An event macro checks if there is a new drop down […]

Extract a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]

Improve your X Y Scatter Chart with custom data labels

The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]

Search for a file in folder and subfolders (VBA)

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

Return all combinations

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

List dates outside specified date ranges

The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is […]

Sum number based on corresponding unique value

The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

How to use absolute and relative references

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

Highlight duplicates with same date, week or month

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

Filter duplicates within same date, week or month

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

Count unique distinct values within same week, month or year

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

How to count unique distinct items on a given date

The array formula in cell D3 calculates the number of unique distinct items on the given date. Unique distinct values […]

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

Remove duplicates within same month or year

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

AVERAGE based on criteria

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in […]

Remove duplicates based on date

Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]

Consolidate sheets [vba]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Sum based on OR – AND logic

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Rearrange values based on category [VBA]

In this post I am going to rearrange values from a list into unique columns. Before: After: The code Download […]

Count unique distinct numbers across multiple sheets

The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Merge Ranges Add-In

Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Count cells between two values

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

Extract unique distinct values if value contains string

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

How to use the AMORDEGRC function

The AMORDEGRC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. Formula […]

How to use the YIELD function

The YIELD function calculates the yield for a security that pays interest. The YIELD function is designed to calculate the […]

Filter duplicate text values in a range using “begins with” criterion

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

Sort numeric values based on proximity to a given number

The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are […]

Heat map yearly calendar

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]

Return multiple values if in range

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

Sort two columns

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

How to use the SYD function

The SYD function calculates the yearly asset depreciation of a given year. Formula in cell C6: =SYD(C2,C3,C4,C5) Excel Function Syntax […]

Using Excel Solver to schedule employees

This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]

Find positive and negative amounts that net to zero

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

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

How to use the TBILLEQ function

The TBILLPRICE function calculates the equivalent bond yield for a Treasury bill. Formula in cell C6: =TBILLEQ(C2,C3,C4) Excel Function Syntax TBILLEQ(settlement, […]

Extract unique distinct values that begins with a given string from a cell range

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

Filter unique distinct values where adjacent cells contain search string

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

Highlight odd/even months

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]

How to use the TBILLPRICE function

The TBILLPRICE function calculates the par amount (face value) for a Treasury bill. Formula in cell C6: =TBILLPRICE(C2,C3,C4) Excel Function […]

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

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Most frequent value between two dates

In this article I will show you how to extract the most frequent value (text or number) between two dates […]

Lookup min max values within a date range

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

How to use the TBILLYIELD function

The TBILLYIELD function calculates the yield for a Treasury bill. Formula in cell C6: =TBILLYIELD(C2,C3,C4) Excel Function Syntax TBILLYIELD(settlement, maturity, […]

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

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 create name initials

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Filter unique values sorted from A to Z

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Sort text values by length

Array formula in B2: =INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)) copied down as far as needed. To enter an […]

Create a list with most recent data available

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

Change column/bar color in charts

Peter asks: Hello, I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 […]

SUMPRODUCT – multiple criteria

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]

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

Create unique distinct list sorted based on text length

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

How to use the UNICODE function

Use the UNICODE function to return a Unicode number based on a character. Excel Function Syntax UNICHAR(text) Arguments text Required. […]

Unique distinct values sorted based on frequency

Question: How do I create a unique distinct list from a column sorted by occurrence? Array formula in D3: =INDEX($B$3:$B$15, […]

How to use the UNICHAR function

Use the UNICHAR function to calculate a character based on a number. Excel Function Syntax UNICHAR(number) Arguments number Required. The […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

How to quickly select a non contiguous range

A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

Two-way lookup using multiple tables [UDF]

This post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can […]

Split search value using delimiter and search for each substring

Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]

Multiple wildcard lookups and include or exclude criteria

Dave asks: Hi Oscar, This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. […]

Search for a text string 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 […]

Search for multiple text strings in column – AND logic

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

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

Create a list of dates with blanks between quarters

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Extract numbers from a column

Question: I want to extract all numeric values into a new column? If you have both letters and digits in […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

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

Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Search each column for a string each and return multiple records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Reverse a list ignoring blanks

The image above demonstrates a formula in cell D3 that rearranges values, bottom value is now on top etc. Formula […]

List values with past date

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Search and display all cells that contain multiple search strings

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]