Latest modified blog articles

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

How to save custom functions and macros to an Add-In

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

7 days (weekly) date ranges using a formula

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Count unique distinct records (rows) in a Pivot Table

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]

How to compare two data sets

This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]

How to use the VDB function

The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

How to use the DDB function

The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

Sum unique distinct invoices

Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Calculate running average of last 10 data with random blank cells

Question: List of data and blank cells in a column which will be added from day to day. There are […]

Compare your stock portfolio with S&P500 in Excel

By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]

Filter unique distinct records (case sensitive) [UDF]

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]

Filter unique distinct values (case sensitive) [UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

Bill reminder in excel

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Combine cell ranges eliminating blanks

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

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

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

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

Fuzzy lookups [UDF]

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

Fuzzy vlookup [Array formula]

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

Highlight records [AND logic]

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

Excel chart components

  What's on this page Chart area Chart title Legend Axis title Axis lines/values Tick marks Plot area Chart series […]

Cash drawer bill extractor

Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]

Filter duplicate records

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.

Highlight closest number

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

Merge matching rows

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

Find numbers in sum [UDF]

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

Create a drop down calendar

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

Weekly appointment calendar

This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]

Monthly calendar template #2

I have created another monthly calendar template for you to download. Select a month and year in cells A1 and […]

Monthly calendar template

The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]

Count unique distinct records

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

Create a date range [Formula]

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

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

Highlight duplicate columns

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

Highlight duplicate records

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

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Select and view invoice [VBA]

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

Edit invoice data [VBA]

In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]

Save invoice data [VBA]

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

Dynamic team generator

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

Create a random playlist

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

Team Generator

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

Resize a range of values

The user defined function demonstrated in the animated ggif below, resizes a range you specify to columns or rows you also […]

Multiply cells efficiently

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