Function Reference

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.

Latest modified blog articles

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

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

How to compare two data sets

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

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

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

How to enter an array formula

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