Sum security holdings monthly and yearly in a pivot table
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you […]
Latest modified blog articles
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
Prevent overlapping date and time ranges using data validation
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
Group rows based on a condition
This article explains how to merge values row by row based on a condition in column A using an array […]
VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Find date range based on a date
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
Find smallest and largest unique number
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Gets a value in a specific cell range based on a row and column number.
Create monthly time sheet using a Pivot Table
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
Analyze trends using pivot tables
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Hide specific columns programmatically
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous […]
Compare your stock portfolio with S&P500 in Excel
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
How to use VLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Highlight lookups in relational tables
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]
Find and replace a text string in file names, folder name and subfolders
The following two macros lets you rename files and folders recursively. Press Alt + F8 to open a list of macros, run […]
Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]
Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Change column/bar color in charts
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
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 […]