Latest modified blog articles

Unique distinct records sorted based on count or frequency

Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]

Count groups of repeated values per row

Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]

Use a drop down list to filter and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

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

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Add or remove a value in a drop down list programmatically

This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Concatenate unique distinct values

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]

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

Auto populate a worksheet

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Combine cell ranges ignore blank cells

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

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

Toggle a macro on/off using a button

This article demonstrates how the user can run a macro by clicking on a button, the text on the button […]

Extract text between words [UDF]

Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]

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

Schedule project dates based on a finish date

Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]

Use a drop down list to display data in worksheet header [VBA]

Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]

Automate data entry [VBA]

This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

Apply drop-down lists dynamically

This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

How to log when a workbook is opened and closed [VBA]

This article demonstrates how to automatically create log entries when a workbook opens or closes using event code. Column A […]

Working with Conditional Formatting formulas

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

How to use the XNPV function

The XNPV function calculates the net present value for cash flows that may or may not be periodic. Net present […]

How to create random numbers, text strings, dates and time values

The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

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

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

Days contained in a range that overlap another range

Rene asks: Hi Oscar, I need a fomula that gives me the number of days contained in a range that […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

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

How to use the RRI function

The RRI function calculates the growth of an investment in percent per period. Formula in cell C6: =RRI(C2, C3, C4) […]

Shift Schedule

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Watch schedule that populates vacation time

This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Advanced Gantt Chart Template

This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]

How to use the SET statement

The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that […]

Auto resize columns as you type

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Concatenate cell values based on a condition [No VBA]

Add cell values to a single cell with a condition, no VBA in this article.

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

Highlight lookup values

In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

Find entry based on conditions

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]

Free School Schedule Template

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

Copy a dynamic cell range [VBA]

In this blog post, I will demonstrate some VBA copying techniques that may be useful if you don't know the […]

Label groups of duplicate records

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

List all open workbooks and corresponding sheets [VBA]

In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]

Basic data entry [VBA]

In this small tutorial, I am going to show you how to create basic data entry with a small amount […]

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 use the ROUNDDOWN function

The ROUNDDOWN function calculates a number rounded down based on the number of digits to which you want to round […]

List Excel defined Tables in a workbook [VBA]

The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]

Basic invoice template

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

How to use the ROUNDUP function

The ROUNDUP function calculates a number rounded up based on the number of digits to which you want to round […]

Extract week ranges based on a given date range

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

Count specific WEEKDAYS between two dates

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

Rearrange data source in order to create a dynamic chart

Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]

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

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

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

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

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

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

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

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

Count unique distinct values in a filtered Excel defined Table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Custom data labels in a chart

You can easliy change data labels in a chart. Select a single data label and enter a reference to a […]

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

Lookup with criteria and return records.

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.

Extract records containing negative numbers

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

How to add lines between stacked columns/bars [Excel charts]

The image above shows lines between each colored column, here is how to add them automatically to your chart. Select […]

Count weekday within date range except holidays

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

Extract numbers from a value

Chirag asks: I want to separate numbers from the following text: Abc123bx45 as a result 123 and 45 should be […]

Create permutations [UDF]

Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]

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

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

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

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

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