This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
Latest modified blog articles
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 […]
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
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 […]
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 […]
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 […]
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 […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
The SIN function calculates the sine of an angle. Formula in cell C3: =SIN(B3) Excel Function Syntax SIN(number) Arguments number […]
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 […]
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 […]
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
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 […]
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.
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. […]
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 […]
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 […]
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. […]
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.
What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]
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 […]