Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
Latest modified blog articles
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Add checkboxes to a sheet (1/2) [VBA]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]
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.
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Filter an Excel defined Table based on selected cell [VBA]
In this post I am going to demonstrate how to quickly apply a filter to a table. I am using […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Populate a combobox with values from a pivot table [VBA]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
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 easiest way to sum a cell range is to simply select the cell range and read the values in […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
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 […]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting non-empty values. Column B above have a few blank cells, they are […]
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
How to extract not shared values in two columns
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
Add values to a regular drop-down list programmatically [VBA]
In this tutorial I am going to show you how to add values to drop down list in cell C2. This […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Optimize pick path in a warehouse
As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]
How to create a combined stacked area and a clustered column chart
Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
How to highlight weekends [Conditional Formatting]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
Highlight above average values
The image above shows values above average using conditional formatting applied to cell range B2:G13. Cell range B2:G13 contains 72 […]
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Get date ranges from a schedule
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
Compare two columns and highlight differences
A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column […]
Extract unique distinct records based on a criterion
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
Find the smallest value in a list that is larger than a number
Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]
How to calculate a date based on specific weekday in a month
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]
Extract unique distinct values based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
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 […]
How to highlight unique distinct values
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
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 […]
Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]
How to ignore zeros using the SMALL function
The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros.
Filter an Excel defined Table programmatically [VBA]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
Populate a list box with unique distinct values from a filtered Excel table [VBA]
Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]
Populate a combo box (form control) [VBA]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Filter unique distinct records using criteria
This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
Extract unique distinct values based on a filtered Excel defined Table
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
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, […]
This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]
Two-way lookup using multiple tables [UDF]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
How to ignore error values using the SMALL function
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
Count entries based on date and time
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
How to add a custom-made item to the shortcut menu [VBA]
This post describes how to add a new custom-built item to the shortcut menu in Excel, when you right-click a cell […]
VLOOKUP in a filtered Excel Table and return multiple values
This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]
Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
SUMIF across multiple sheets [UDF]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
Select cell A1 on all sheets before you close a workbook [VBA]
This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The […]
Insert blank rows for missing values
HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]
How to sort a data set in a custom order
Your boss wants you to sort the company's products by a new criterion, quality. You receive a list from your […]
Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
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 […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
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, […]
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 […]
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]
The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]
The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]
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 […]
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 […]
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 […]
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 […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Welcome! I am 