Latest modified blog articles

Create date ranges that stays within month

This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]

Add values to worksheets based on a condition [VBA]

This tutorial shows you how to add a record to a particular worksheet based on a condition, the image above […]

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

Add values to a two-dimensional table based on conditions [VBA]

This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Extract unique distinct values if adjacent cell is text

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

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

Search columns for a string and return records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Extract unique distinct values A to Z from a range and ignore blanks

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Filter duplicate values from a range that begins with string

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

Extract unique distinct values from cell range that begins with string

The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]

Use VLOOKUP to calculate discounts, commissions, tariffs, charges, shipping costs, packaging expenses or bonuses

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]

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