Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
Latest modified blog articles
Copy worksheets in active workbook to new workbooks
This article demonstrates several VBA macros, they will save you time if you have lots of worksheets. The first macro […]
Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Copy Excel Table filter criteria programmatically
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
Perform thousands of conditions to a data set
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then […]
Use a calendar to filter an Excel defined Table
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
Schedule recurring expenses in a calendar in excel (Personal Finance)
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
Use drop down lists and named ranges to filter chart values
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
Extract cell references populated with values [VBA]
This article demonstrates a macro that returns cell references for cell ranges populated with values on a worksheet. Jinesh asks: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A […]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
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 […]
Macro creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Create new worksheets programmatically based on values in a cell range [VBA]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
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 […]
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 […]