Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Latest modified blog articles
Dependent drop down lists – Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Running totals based on criteria
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
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 […]
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
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 […]
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 [Array formula]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Highlight records containing text strings (AND Logic)
The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]
Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Highlight records – multiple criteria [OR logic]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Search for a text string in a data set using an array formula
Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]
How to customize the plot area
The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]
What's on this page Chart area Chart title Legend Axis title Axis lines/values Tick marks Plot area Chart series […]
Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
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 demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Find positive and negative amounts that net to zero [UDF]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Count digits and ignore duplicates
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Create links to all sheets in a workbook
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
I have created another monthly calendar template for you to download. Select a month and year in cells A1 and […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
Highlight unique distinct records
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]
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 1/11/2009-1/17/2009 […]
Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Compare two tables: Remove common records
Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]
This blog post demonstrates how to quickly compare two tables in Excel. Table1 Table2 Create a fourth column and use […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
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 […]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
Count cells based on a condition and month
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Use text qualifiers to make text to columns conversion easier [VBA]
This blog post describes how to insert qualifers to make "text to columns" conversion easier. Example I copied a table from […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]
How to use the AMORLINC function
The AMORLINC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. Formula […]
Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Filter not shared values out of two cell ranges [UDF]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
Filter values in common between two cell ranges [UDF]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
Filter duplicates in a large dataset [UDF]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
Sort based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
Compare two columns and return differences
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Count unique distinct values in a large dataset [UDF]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
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 […]
Extract unique distinct values based on a date range
Question: I have a large list of dates and other adjacent values. I want to create a distinct list from […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]
Sort records based on two columns
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Search two related tables simultaneously [VBA]
Let's say you do a lot of searches in two tables. The tables are related so it would be great […]
The user defined function demonstrated in the animated ggif below, resizes a range you specify to columns or rows you also […]
Joe asks: Hello Oscar,I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate additional […]
A quicker A * pathfinding algorithm
3 weeks ago I showed you a A* pathfinding algorithm. It was extremely slow and sluggish and I have now made it […]
Lookup with an unknown number of criteria
Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem […]
Vlookup with multiple matches returns a different value
Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Extract a unique distinct list based on a condition [Pivot Table]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
Extract dates from a cell block schedule
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
5 easy ways to extract unique distinct values
You have quite a few options to choose from if you are looking for a way to create a unique […]
Array formulas allows you to do advanced calculations not possible with regular formulas.
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 […]
Setting up your work hours in a weekly schedule
The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey except weekends. Conditional formatting […]
Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous […]
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 […]
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. […]
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 […]
Welcome! I am 