Latest modified blog articles

Yet another Excel Calendar

What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]

How to use the MMULT function

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]

How to use the SMALL function

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.

Working with FILES

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

How to use the ROW function

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.

How to use the IF function

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

How to use the MATCH function

Identify the position of a value in an array.

How to use the INDEX function

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

Custom data labels in a chart

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

Move a shape [VBA]

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

AVERAGE ignore NA()

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]

Highlight a bar in a chart

This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]

Dynamic scoreboard

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Repeat values across cells

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

Lookups in relational tables

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

Excel calendar [VBA]

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