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 […]
Latest modified blog articles
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
The RRI function calculates the growth of an investment in percent per period. Formula in cell C6: =RRI(C2, C3, C4) […]
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 […]
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
