Merge three columns into one list

The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]

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.

Merge three columns into one list

The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

Create a unique distinct list based on criteria

The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]

Sum number based on corresponding unique value

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

Extract a list of duplicates from two columns combined

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Extract a list of duplicates from three columns combined

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]

A record is returned if both search strings are found on the same row

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

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 an unique distinct […]

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Array Formula in D6 =SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2, 0,0, […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Highlight duplicates in two columns

Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates […]

Sort dates within a date range

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

How to rank uniquely based on a condition

The following formula ranks text values in column C uniquely based on the category in column B. Formula in D3: […]

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

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified. […]

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

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Count dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

Delete blanks and errors in a list

The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they […]

Calculate last date of a given month

The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]

Create a numbered list ignoring blank cells

The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]

How to rank text uniquely without duplicates

Question: How do I rank text cell values uniquely? If text values were sorted alphabetically from A to Z, the […]

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

How to use the PERCENTRANK function

The PERCENTRANK function calculates the percent rank of a given number compared to the whole data set. The image above […]

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

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

Extract unique distinct year and months from dates

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

INDEX and MATCH – multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

Identify all characters in a cell value

Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]

How to remove unwanted characters in a cell

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

How to use the EVALUATE method

The Evaluate method converts an Excel name to an object or value. The picture above demonstrates a macro that uses […]