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 […]