How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
Latest modified blog articles
Search values and return value with highest level
This article demonstrates a formula that searches a table and returns a value based on its position. The value with […]
Highlight cells based on ranges
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
Rearrange values in a cell range to a single column
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Count matching strings using regular expressions
This article demonstrates a User Defined Function (UDF) that counts how many times a string exists in a cell value […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing […]
Split search value using delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
How to use the FILTERXML function
The FILTERXML function extracts specific values from XML content by using the given xpath. The function was introduced in 2013 […]
Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
Fetching values from ThingSpeak programmatically
Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
Count cells based on background color programmatically
This article demonstrates a VBA macro that counts cells based on their background color. What's on this page Question VBA […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
Rotating unique groups with no repeat
This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]
How to generate a round-robin tournament
This article demonstrates macros that create different types of round-robin tournaments. Table of contents Basic schedule - each team plays […]
Extract cell references from a formula
This article demonstrates a User Defined Function that allows you to extract cell references from a formula in a given […]
How to extract rows containing digits [Formula]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
List permutations with repetition and how many to choose from
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
A dialog box is an excellent alternative to a userform, they are built-in to VBA and can save you time […]
List all permutations with a condition
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
How to use Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]
Working with ARRAY VARIABLES (VBA)
This post will teach you how to work with Excel arrays in visual basic for applications (VBA). Why do you […]
How to calculate and plot pivots on an Excel chart
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]
Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
Compare two columns in different worksheets
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Sort values by corresponding text arranged in a column
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
Sort items by adjacent number in every other value
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
The ROUND function calculates a rounded number based on the number of digits you specify. Formula in cell D3: =ROUND(B3, […]
How to use the MROUND function
The MROUND function calculates a number rounded to a given multiple. Formula in cell D3: =MROUND(B3,C3) Excel Function Syntax MROUND(number, […]
Multiply numbers in each row with remaining rows in cell range (UDF)
This article demonstrates a User Defined Function (UDF) that multiplies numbers in each row with the remaining rows in a […]
Resize a range of values (UDF)
The User Defined Function (UDF) demonstrated in this article, resizes a given range to columns or rows you specify. This […]
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
Follow stock market trends – trailing stop
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]
How to add horizontal line to chart
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
Follow stock market trends – Moving Average
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]
Find and return the highest number and corresponding date based on a condition
This article describes how to filter records based on the maximum value of a specific item. There are names in […]
Excel chart problem: Hard to read series values
It might be impossible to read the smaller series values on the y-axis if you have two series of data […]
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
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 […]
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 […]
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 […]
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
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 […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
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 […]
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.
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: […]
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 […]
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, press with left […]
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 […]
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, […]