Latest modified blog articles

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

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.

Find numbers closest to sum

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

Dynamic Gantt charts

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

How to sum a cell range

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

Concatenate cell values

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

Remove blank cells

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

Highlight current date

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

Nested Search

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

Reorganize data [UDF]

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

Use a map in an Excel chart

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

Extract numbers from a column

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

Sum cells based on criteria

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

Auto refresh a pivot table

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

How to compare two data sets

This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]

How to use the VDB function

The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

How to use the DDB function

The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

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