Latest modified blog articles

Move data to workbooks

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

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.

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.

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

How to use the STDEV function

The STDEV function calculates the standard deviation of a group of values. The standard deviation shows how much the values […]

Sort and return unique distinct single digits from cell range

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

Print screen the entire worksheet

This article demonstartes different techniques on how to take a screenshot of your worksheet. You can do that by simply […]

Highlight unique values in a filtered Excel table

This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]

Customize Excel maze

Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I […]

Find last matching value in an unsorted list

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

Solve a maze programmatically in Excel

This article demonstrates a macro that finds a way between a start and an end point in a maze.  My […]

Build a maze programmatically in Excel

The image above shows the creation of a maze located on a worksheet. A macro builds this maze randomly, a […]

How to animate a line chart

This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]

Merge tables based on a condition

This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

How to animate an Excel Bar Chart

This article demonstrates macros that animate bars in a chart bar. The image above shows a bar chart that animates […]

Lookup value based on two critera – second criteria is the adjacent value and its position in a given list

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

Multi-level To-Do list template

Today I will share a To-do list excel template with you. You can add text to the sheet and an […]

How to animate an Excel chart

This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]

Color chart columns based on cell color

This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]

Highlight a group of chart bars

This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]

Sort by multiple columns

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

Search all workbooks in a folder

Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

All you need to know about calculating NAV units for your stock portfolio

This blog article explains in greater detail how to determine stock portfolio performance based on units of NAV (Net Asset […]

Lookup and return multiple sorted values based on corresponding values in another column

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

Customize the ribbon and how to add your macros

Save links to your favorite macros in a personal tab on the ribbon for easy access and become more productive. […]

Identify rows of overlapping records

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Remove print preview lines (Page Breaks)

Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

Compare two columns and extract differences

This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]

How to filter chart data

What if you want to show a selection of a data set on a chart and easily change that selection? […]

How to use the SIN function

The SIN function calculates the sine of an angle. Formula in cell C3: =SIN(B3) Excel Function Syntax SIN(number) Arguments number […]

Create number series

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

How to copy every n-th row from a list

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

If cell contains text from list

This article demonstrates several ways to check if a cell contains a value based on a list. The first example […]

How to solve simultaneous linear equations in Excel

This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]

Working with TEXT BOXES [Form Controls]

There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Show / hide a picture using a button

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

Lookup with any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

Hide specific worksheets programmatically

This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]

Click a cell to make a column hidden or visible [VBA]

What's on this page Click a specific cell to hide/show entire column Where to put the event code Download Excel […]

How to use the Scroll Bar

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

Count cells based on background color

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

Min and max date of overlapping date ranges

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

How to select and delete blank cells

This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Count unique distinct values by cell color

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]

Pivot Table calendar

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Highlight date ranges overlapping selected record [VBA]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

Practice basic arithmetic calculations in Excel

This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above […]

Which Excel files in folder are password protected?

This article explains how to check if Excel files in a given folder are password protected. The image above shows […]

Count text string in all formulas in a worksheet [VBA]

I will in this article demonstrate a macro that counts how many times a specific text string is found in […]

Easily select data using hyperlinks

The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]

Navigate to first empty cell using a hyperlink formula

This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]

How to use the IFERROR function

The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]

How to use the CELL function

The CELL function gets information about the formatting, location, or the contents of a cell. The formula example above in […]

Create a hyperlink linked to the result of a two-dimensional lookup

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]

How to generate random numbers and text

What's on this page Random numbers with a decimal Random negative numbers with two decimals Random negative numbers with three […]

Compare data in an Excel chart using drop down lists

I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]

Locate a shape in a workbook

This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]

Hover with mouse cursor to change stock in a candlestick chart

This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]

Find all sequences of consecutive dates

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

Highlight a data series in a line chart

This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]

Heat map using pictures

I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to build an interactive map in Excel

This article describes how to create a map in Excel, the map is an x y scatter chart with an […]

How to use the SEARCH function

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

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.