What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]
Latest modified blog articles
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Assign records unique random text strings
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
Units contained in a range that overlap another range
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
Identify the position of a value in an array.
Gets a value in a specific cell range based on a row and column number.
How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
Count multiple text strings in a cell range
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
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 […]
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Highlight group of values in an x y scatter chart programmatically
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
Highlight a column in a stacked column chart
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
Highlight events in a yearly calendar
This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
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 […]
Merge two relational data sets
This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited […]
Lookup multiple values in one cell [UDF]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
How to calculate totals of stock transactions based on dates
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
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 […]
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 […]
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 […]
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 […]