Pivot table: Count unique distinct records (rows)

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

This extensive list provides detailed information including syntax, arguments, return values and examples for most of the functions used in Excel.

The list is easy to search and lets you sort by function name or category.

This is a great place to start learning Excel functions.

Learn to build charts that let you visualize values in order to analyze and comprehend data.

Learn to format data based on a condition or criteria to make you more productive and effective.

Pivot table: Count unique distinct records (rows)

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

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

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

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

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

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.

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

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

Highlight unique distinct records

The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]

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

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

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

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

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

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

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

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

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