Change chart axis range programmatically
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
Latest modified blog articles
Plot buy and sell points in an Excel Chart based on two moving averages
This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]
List files in folder and create hyperlinks (VBA)
This article demonstrates a macro that populates a new worksheet with filenames from the active folder which is the same […]
Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
Add buy and sell points to a stock chart
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
Use drop down lists to compare data series in an excel chart
The two drop down lists below the chart let´s you compare two data series from separate tables. It is easier […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
Find numbers in close proximity to a given number
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
How to use the SUMIFS function
The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Search two related tables [VBA]
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
Create comment if cell value is larger than column
It can sometimes be helpful having a large cell value in a comment. You can then easily hover over cell […]
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 […]
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.
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Learn how to return values based on where you enter the UDF
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Extract unique distinct values if adjacent cell is text
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
Disable autofit column widths for Pivot table
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Create date ranges that stay 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 […]
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 […]
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 […]