The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Latest modified blog articles
The SYD function calculates the yearly asset depreciation of a given year. Formula in cell C6: =SYD(C2,C3,C4,C5) Excel Function Syntax […]
Using Excel Solver to schedule employees
This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Filter duplicate values in a range using “contain” condition
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
Extract unique distinct text values containing string in a range
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
Filter duplicate text values in a range using “begins with” criterion
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
How to use the TBILLEQ function
The TBILLPRICE function calculates the equivalent bond yield for a Treasury bill. Formula in cell C6: =TBILLEQ(C2,C3,C4) Excel Function Syntax TBILLEQ(settlement, […]
Extract unique distinct values that begins with a given string from a cell range
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
Filter unique distinct values where adjacent cells contain search string
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
How to use the TBILLPRICE function
The TBILLPRICE function calculates the par amount (face value) for a Treasury bill. Formula in cell C6: =TBILLPRICE(C2,C3,C4) Excel Function […]
Automate Excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
Most frequent value between two dates
In this article I will show you how to extract the most frequent value (text or number) between two dates […]
Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
How to use the TBILLYIELD function
The TBILLYIELD function calculates the yield for a Treasury bill. Formula in cell C6: =TBILLYIELD(C2,C3,C4) Excel Function Syntax TBILLYIELD(settlement, maturity, […]
Extract unique distinct values sorted based on sum of adjacent values
Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]
Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
Filter unique values sorted from A to Z
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
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) […]
Array formula in B2: =INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)) copied down as far as needed. To enter an […]
Create a list with most recent data available
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
Change column/bar color in charts
Peter asks: Hello, I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 […]
SUMPRODUCT – multiple criteria
The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]
Unique distinct list from a column sorted A to Z
Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]
Create unique distinct list sorted based on text length
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
How to use the UNICODE function
Use the UNICODE function to return a Unicode number based on a character. Excel Function Syntax UNICHAR(text) Arguments text Required. […]
Unique distinct values sorted based on frequency
Question: How do I create a unique distinct list from a column sorted by occurrence? Array formula in D3: =INDEX($B$3:$B$15, […]
How to use the UNICHAR function
Use the UNICHAR function to calculate a character based on a number. Excel Function Syntax UNICHAR(number) Arguments number Required. The […]
Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
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 […]
Two-way lookup using multiple tables [UDF]
This post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can […]
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 search value using delimiter and search for each substring
Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]
Multiple wildcard lookups and include or exclude criteria
Dave asks: Hi Oscar, This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. […]
Search for a text string and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
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 […]
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 […]
Search for multiple text strings in column – AND logic
Question: How do I search a list for two text strings and return a list with where both strings occurs? […]
How to search for a string in a column
Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]
Create a list of dates with blanks between quarters
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
Question: I want to extract all numeric values into a new column? If you have both letters and digits in […]
Find min and max unique and duplicate numerical values
Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]
How to highlight MAX and MIN value based on month
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
Identify missing three character alpha code numbers
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
Search each column for a string each and return multiple records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Reverse a list ignoring blanks
The image above demonstrates a formula in cell D3 that rearranges values, bottom value is now on top etc. Formula […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Highlight more than once taken course in any given day
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
How to use the PERCENTRANK.EXC function
The PERCENTRANK.INC function calculates the percent rank of a given number in a data set. This function was introduced in […]
Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Sort column based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
Filter values occurring in range 1 but not in range 2
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Filter common values from three separate columns
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
Filter duplicate values and sort by corresponding date
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
Filter unique values and sort based on adjacent date
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
Extract a unique distinct list sorted from A-Z from range
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
How to use the PERCENTRANK.INC function
The PERCENTRANK.INC function calculates the percent rank of a given number compared to the whole data set. The image above […]
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
Extract a list of alphabetically sorted duplicates from a column
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
Highlight duplicate values in a cell range
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
Highlight unique values and unique distinct values in a cell range
The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]
Extract duplicates from a range
The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]
Search for a cell value in a dataset
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
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 […]
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 […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Welcome! I am 