Latest modified blog articles

Highlight a column in a stacked column chart

This interactive chart allows you to select a country by clicking on a spin button. The chart and table show […]

How to color chart bars based on their values

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

Calculate date given weekday and week number

This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to […]

Improve your X Y Scatter Chart with custom data labels

The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]

How to use the TRANSPOSE function

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Calculate time between time zones

This article demonstrates how to build a time zone chart and calculate times different than your time zone. The worksheet […]

Find and replace strings in file names, folder name and subfolders

The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The […]

Sum numerical ranges between two numbers

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

How to use the PV function

The PV function calculates the net present value for an investment or loan. PV stands for present value. Formula in […]

Unzip files in folder and subfolders

The VBA macro demonstrated in this article lets you unzip all zip files in a folder to a destination folder […]

Sort based on frequency row-wise

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Working with overlapping date ranges

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

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

How to use the MOD function

The Mod function returns the remainder after a number is divided by a divisor. The Mod function is short for […]

How to build a Team Generator – different number of people per team

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

Working with LIST BOXES (Form Controls)

This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]

Split expenses calculator

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

How to count repeating values

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

List all hyperlinks in worksheet programmatically

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

How to automatically add new items to a drop down list

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

How to use the QUOTIENT function

The quotient function returns the integer portion of a division. Example, 5/2 = 2.5. The integer is 2. Excel Function […]

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Permutations with and without repetition

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]

Highlight duplicate records

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

Return all combinations

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

Filter duplicate files in a folder and subfolders

This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]

Compare file names in two different folder locations and their sub folders

Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]

How to use the LIKE OPERATOR

The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a […]

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.

True round-robin tournament

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

How to save specific multiple worksheets to a pdf file programmatically

This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

Find cells containing formulas with literal (hardcoded) values

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

How to enter array formulas in merged cells

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]

How to use the PMT function

The PMT function returns the payment amount needed for borrowing a fixed sum of money based on constant payments and […]

How to use the COLUMN function

The COLUMN function returns the column number of the top-left cell of a cell reference. If the argument is not […]

How to use the INDIRECT function

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]

Find the most/least consecutive repeated value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

Extract the most repeated adjacent values in a column

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]

How to use the TEXTTOCOLUMNS method

The TextToColumns method puts a cell that contains a string into substrings and distributes them horizontally using a delimiting character […]

Text to columns: Split words in a cell

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

How to use the SPLIT function [VBA]

The picture above shows a user-defined function (UDF) that splits the string in cell C3 using a delimiting character ",". […]

Working with classic ciphers in Excel

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Count comma separated values

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

Search for a sequence of cells based on wildcard search

This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

Search for a sequence of values

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

Search all workbooks in a folder and sub folders

This article demonstrates a macro that allows you to search for a text string in multiple worksheets and workbooks located […]

How to create a list of comments from a worksheet programmatically

Did you know that you can select all cells containing comments in the current sheet? Press F5, click "Special..." button, […]

How to replace columns with pictures in a column chart

I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]

How to use the SUM function

The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]

Build a comparison table/chart

I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]

Add pictures to a chart axis

This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]

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 group items by quarter using formulas

This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]

How to copy Excel tables programmatically

The image above demonstrates a macro linked to a button. Click the button and the macro runs. It copies the […]

How to highlight row of the selected cell programmatically

Today I would like to share with you these small event handler procedures that make it easier for you to […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

How to customize the chart area

The chart area contains all components. If you choose to format the chart area you can change things like border, […]

How to customize the chart title

I recommend that the chart title is short and easy to understand. How to add a chart title Follow these […]

How to customize chart axis titles

The x-axis and y-axis titles are just as easy to format and customize as the other chart elements. How to […]

How to customize chart axis

  Most Excel charts consist of an x-axis and a y-axis, Excel allows you to easily change the looks of […]

How to customize the chart legend

You can easily customize the font, font size, font color etc of the chart legend: Select legend. Go to tab […]

How to customize chart tick marks

The image above shows tick mars in a line chart, there are two types of tick marks, major and minor […]

How to customize a chart data series

To change the looks of a data series simply double-click with left mouse button on the data series. If you […]

How to customize the plot area

The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]

Finding the shortest path – A * pathfinding

Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]

Move data to workbooks

This article demonstrates a VBA macro that saves user input to a given workbook and worksheet programmatically. Macros are great […]

How to use the RANDARRAY function

The RANDARRAY function returns a table of random numbers across rows and columns. It is located in the Math and […]

Excel chart components

  Charts in Microsoft Excel lets you visualize, analyze and explain data. Charting in Excel is very easy and you […]

How to use the SEQUENCE function

The SEQUENCE function creates a list of sequential numbers to a cell range or array. It is located in the […]

How to use the WEEKDAY function

Converts a date to a weekday number from 1 to 7. Formula in cell D3: =WEEKDAY(B3) Excel Function Syntax WEEKDAY(serial_number,[return_type]) […]

How to use the BETADIST function

The BETADIST function calculates the beta distribution representing an outcome in the form of probability. This function is in the compatibility […]

Rearrange values using formulas

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

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

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

How to remove numbers from a cell value

This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

How to use the SORTBY function

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range […]

How to use the SORT function

The SORT function lets you sort values from a cell range or array. It returns an array with a size […]

How to use the FILTER function

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]

How to use the UNIQUE function

The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]

How to use the ADDRESS function

The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Unique distinct list sorted alphabetically based on a condition

Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users. =SORT(UNIQUE(FILTER(C3:C11, E3=B3:B11))) […]

Lookup multiple values in different columns and return multiple values

Jason C asks: I have a set of data, like the one you used in the original example that also […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Filter unique values sorted from A to Z

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

Extract a unique distinct list sorted from A to Z ignore blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Extract a unique distinct list and ignore blanks

Question: How do I extract a unique distinct list from a column containing blanks? Answer: Cell range B3:B12 contains several blank […]

Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

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

How to use the Subtotal tool

What's on this page Prepare data Sort data Start the Subtotal tool Subtotals on multiple levels Dialog box settings Remove […]

How to use the VAR.S function

The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and […]

How to use the STDEV.P function

The STDEV.P function returns standard deviation based on the entire population. The standard deviation is how widely numbers are distributed […]