Excel - Get Digital Help

Archive for the ‘Excel’ Category

Excel SUM function
The SUM function in excel allows you to add values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers. Syntax SUM(number1, number2, […]

How to use the HLOOKUP function
Table of Contents Overview Arguments Approximate match Horizontal and vertical lookup Horizontal and vertical lookup - INDEX + MATCH HLOOKUP - multiple tables VBA Example Download excel *.xlsm file Functions in this post Overview The HLOOKUP function lets you search […]

Everything you need to know about the VLOOKUP function
Table of Contents Overview Arguments Approximate match Related tables Dynamic arguments INDEX + MATCH VLOOKUP error VBA Example Using multiple conditions in VLOOKUP Download excel *.xlsm file Functions in this post Overview The VLOOKUP function lets you search the leftmost […]

Nested Search
Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short example:A1: Cash in deposit (Branch A t/t) A2: Borrowed from Corp. A A3: Interest payment A4: Int.panalty pmt A5: Prin. […]

Track progress
Sankalp asks: I am working in a railway project as planner. How can i create a dynamic strip chart in excel? Assume total length be 10 kilometers and each cell of 100 meters. If I update the progress from 9.01 […]

Use VLOOKUP to calculate discount percentages
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to use than nested IF functions. You can also use the methods described in this article to calculate commissions, tariffs, charges, […]

Count matching strings using regular expressions
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first glance hard to understand but with a little bit of practice very useful. Imagine you want to extract phone numbers from […]

Reverse two-way lookup in a cross reference table
The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula lets you concatenate all values in one cell and it works only in excel 2016 because of the TEXTJOIN function. […]

Excel map chart
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing and it works very well, all you need to do is provide data. The map chart below shows US states […]

Split search value using delimiter and search for each substring
Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in B1 (10 30 20) Or B1(30 20 10) Or Lookup array is D1(anil) E1(10) D2(raj) E2(20) D3(singh) E3(30)

Fetching values from ThingSpeak using vba
Thingspeak is web service that allows you to upload and store data from IoT devices. Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Today I want to demonstrate a user […]

Count colored cells
I got a question about counting background colors in a cell range. Excel uses two different properties to color cells and they are ColorIndex and Color property. The ColorIndex property has 56 different colors, shown below. The color property holds up to […]

Excel tables
A very useful feature in Excel is defined tables, introduced in Excel 2007. Earlier versions had this feature as well but it was then known as Excel Lists. What can an excel defined table do for you? It will simplify your […]

Rotating unique groups with no repeat
Kristina asks: Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the […]

Time sheet for work
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each month and a summary sheet. The above picture shows you January 2017, simply enter the project name in column A […]

Extract cell references from a formula
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a sequence of characters that define a search pattern, according to Wikipedia. This is the custom function I am using to […]

Filter rows where a cell contains a numeric value
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value? Answer: The data set above […]

Like Operator
The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically designed to assist you in building a pattern: ? (question mark) - Matches any single character * (asterisk) - Matches […]

Build a stock chart with two series
This article demonstrates how to create a stock chart with two series.

Calendar – monthly view
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe show a monthly view? Times are less important than just showing what is due on what day. I made a […]

Round-robin tournament
Table of contents Basic schedule Round-robin tournament Double round-robin tournament Macro Download workbook How to use custom function According to wikipedia a round-robin tournament is a competition where all plays all. Excel is a great platform for building a round-robin […]

List permutations with repetition and how many to choose from
Noel asks: Is there a way where i can predict all possible outcomes in excel in the below example. Total games are 13 (ABCDEFGHIJKLM). Possible outcomes are win(home team),draw or win(Away team) represented by 1,X or 2 respectively.Outcomes should be […]

How to use dialog boxes in vba
A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because you don't need to code and build a userform. Some of these dialog boxes also have built-in validation. This article […]

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 000 000 000, if the sum of all digits in a number is smaller or equal to 9. You can […]

Excel – Pivot tables
This article explains the basics of excel pivot tables, I have included vba code for the most common actions. Table of contents What is an excel pivot table? Prepare your source data Rearrange values Use an excel table as a data source […]

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 should have been from the beginning. It allows you to combine text strings from multiple cell ranges and also use […]

Excel vba – Array variables
This post will teach you how to work with excel arrays in visual basic for applications (vba). Why do you need arrays? Excel is much quicker executing tasks with arrays than working with values on a worksheet. Arrays exists in your […]

Stock market trends – pivots
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is higher than the previous bar and the following bar, specially in a monthly bar chart. Excel is a fantastic tool […]

Count identical numbers in two columns but items must be in same row
Kidd asks: Hi Oscar,Need a formula to count identical numbers in two columns but items must be in same row (position). 12 15 8 8 good count 1 22 19 7 22 for 22 not count cause is not in same […]

Compare two columns in different worksheets
sissey asks: Hi Oscar, There are multiple columns in two different worksheets, one has more columns than another. I need to compare column F of worksheet 1 and column E of worksheet 2; if the value matches, compare column G […]

Sort values by corresponding text
I showed you in an earlier post how to sort text by number using a formula, it was a question from Denisa. The first thing that comes to mind would be to rearrange the values and then apply a filter or an excel defined […]

Sort names by value
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I have the following situation: A1, B1, C1, D1, E1, F1 where A1 = nick b1 = 10 c1 = zack […]

Multiply cells efficiently
Joe asks: Hello Oscar, I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate additional datapoints from this dataset. I want to multiply (or other function) each row by all 500 rows, creating 250,000 […]

Resize a range of values
The user defined function demonstrated below, resizes a range you specify to columns or rows you also specify. The first argument is the range, second argument is how many columns you want and third argument is how many rows you […]

Website statistics for 2015
This website had 1 817 077 visitors last year 2015, an incredible number. 43 % more than previous year. 2014 was the first year I saw less visitors than the year before and I could not figure out why. There is a lesson to be […]

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 showed you how to identify the trend using moving averages. The following picture shows you a stock chart with two […]

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 range and change index/company. Price data is quickly and automatically fetched from yahoo finance. This post shows you how to […]

Dynamic stock chart
This stock chart in excel allows you to change the date range and the chart is instantly refreshed. Here is how to build this chart.

Find the highest status and when it was acquired
Denis asks: Hello Oscar, thank you for sharing you knowledge and helping us with these excellent formulas. I have a case i could really need your help with: The following Table shows a history of names, stati and the date […]

Combine a column chart and a xy scatter chart
This tutorial shows you how to add a line to a chart, it combines two types of charts, column and scatter chart. The first step is to create a chart with only one series. You can see my data in cell […]

Primary data is hidden behind secondary data
If you have two series of data and one of the series is on a different scale, it might be impossible to interpret the smaller series. Data in series A on the excel chart above is really hard to compare and analyze. You can […]

Learn the basics of Excel arrays
Constructing an array in excel is easy. Type ={1,2,3} in the formula bar and hold and press CTRL + SHIFT + Enter. Make sure the formula bar looks like this: The formula must have a beginning and ending curly bracket, […]

Highlight a column in a stacked column chart (no vba)
This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows selected country. I have made something very similar in year 2013, Highlight a column in a stacked column chart but with vba. This […]

Color chart bars by value
Excel has a built-in feature that allows you to color negative bars differently than positive values. You can even pick colors. (Chart data is made-up)

Count overlapping days across multiple date ranges
NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, play with it, and come to grips with its basics. Your example was clear and very useful, and this has […]

Find date given day and week
La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th week, 2015 >>> how to use function to point out 4/8/2015 ? Answer:

Custom data labels in x y scatter chart
I demonstrated in a post from March 2013 how to create Custom data labels in a chart. Unfortunately that technique worked only for bar and column charts, there was no way you could apply the same technique for a x y scatter […]

TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range is a range with values in one column, a horizontal range has values in one row. You can also transpose a range […]

Calculate time between time zones
This worksheet lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to refresh the time and date in cell E3. Formula in cell E3: =NOW() Formula in cell E5: =$E$3+C5/24 Copy this cell and paste to […]

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 macro "FindReplace". Type a text string you want to find, in the dialog box. In the next dialog box, type a […]

Sum numerical ranges
This article explains how to build an array formula that sums ranges. Example, I want to know how to calculate the total sum between x.2 -x.3 and x.5-x.8 and the range is 0.5 and 4.5. This picture shows you how to […]

Unzip files in folder and subfolders
This vba macro lets you search for zip files in a folder. Then unzip those files to a folder you specify. It continues with sub folders until all zip files have been unzipped. What happens when you execute the macro? Select […]

Count per row
In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a bit more complicated but dynamic and automatic, you only need to provide a search string, everything else is calculated by […]

Frequency bug?
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and 0.2) are equal to or less than 0.2. 1 value (0.3) is larger than 0.2 and equal or smaller than 0.3. 1 […]

Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In this post I will demonstrate how to count overlapping dates among multiple date ranges. The date ranges are in column […]

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 you need to use a more complicated array formula. I have three date ranges (A2:B4) in this example and I […]

Find missing dates in a set of date ranges
John S asks: I would like to find the dates MM/DD/YYYY missing in a set of date ranges. I haven't been able to find any luck on -line with this. example: 2/3/2005 - 2/5/2005 2/7/2005 - 2/9/2005 _____________ Missing dates: […]

Dynamic scoreboard
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

Working with overlapping date ranges
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I […]

Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, times or whatever, the formula demonstrated here works with everything. This picture shows two time ranges, 06:00-13:00 (yellow) and 11:00-18:00 […]

Team generator
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in team A, there could be a max of 4 members but in team B there could be a max of […]

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 shows you a number of values with a scroll bar (if needed). Selected value is Asia and it is the fourth […]

Split expenses calculator
This workbook lets you split expenses evenly with other people. Type name, expense and amount in the excel table on sheet 'Expenses'.

Count contiguous values
David asks: Hi Oscar, In column A, I have a long random list of two variables, "N/A" and the value 1. In column B I want to identify the number of contiguous occurrences of the value 1 before the next […]

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" or "www". It grabs the hyperlink even if you have a hyperlink function in a cell. Example, this sheet has two […]

Add new items to a drop down list automatically
A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you can disable the validation and still use the drop down list? Entering a value that is not in the drop […]

Quotient, Mod and Int functions
You are about to send your company products to customers. You have boxes you can send the products in. You have 18 items of product A in your inventory (cell B2). The box for product A can hold up to 4 items […]

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 of permutations, with repetition and without repetition. Permutations with repetition I explained in my last post that phone numbers are permutations […]

Return all combinations
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

Find numbers closest to sum
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to total? (2): Multiple Solutions. The MMULT function is used in really clever way, I thought that function was pretty much useless. […]

Filter duplicate files in a folder and sub folders
This is a follow up to my last post Compare file names in two different folder locations and their sub folders, the obvious question after reading that article is "How do I find duplicate files in a single folder and its sub […]

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 sub folders. It compares the file names but not the file sizes. In this example the picture shows the content in these […]

Save selected sheets to a pdf file
This macro saves multiple selected sheets to a single pdf file. Make sure you select the sheets you want to save as a single pdf file before you run the macro. Press and hold CTRL and then left click with […]

Find cells containing formulas with literal (hard coded) values
Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach’s website. ---How can I locate cells containing formulas with literal values?--- […]

LOOKUP function
The Lookup function lets you find a value in a cell range and return a corresponding value on the same row, in a column/row you specify. LOOKUP(lookup_value, lookup_vector, [result_vector]) lookup_value - The lookup value can be a number, text or a logical […]

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: Array formulas are not valid in merged cells. This is not true, they are allowed in merged cells. Here are […]

INDIRECT function
INDIRECT(ref_text,a1) Arguments Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. a1 specifies the type of reference in argument ref_text. True or […]

Find the longest/smallest consecutive sequence of a 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 show you how to build a simple udf that will simplify these formulas significantly. The following user defined function returns the size of […]

Split text across columns
The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, across columns.

Count the number of cells within a range that match multiple comma separated values
I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some cells blank and some cells with text. In another column, say B, each *cell* contains many words, separated by a […]

Find the longest/smallest consecutive sequence of a value
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell D4 returns the value of the longest consecutive sequence in column A. Cell F3 returns the the length of the shortest consecutive sequence […]

Find a sequence of values – wildcard search
In my last post I showed you how to find a sequence of values. The array formula extracted the row of the first found sequence, see below. Array formula in cell F3: =MATCH(1, COUNTIFS(E3, B1:B23, E4, B2:B24), 0) What I didn´t […]

Find a sequence
This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells in column A and returns the row number in cell F2. Array formula in cell F11: =MATCH(E2&E3,$A$1:$A$23&$A$2:$A$24,0) The second argument […]

Search all workbooks in a folder and sub folders
Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato asks: hi thanks for the great macro really makes a hard job much easier, can this be made to search […]

List of all comments (excel vba)
Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" and then click OK. What happens if we record a macro while performing these actions? This is what the macro recorder […]

Use 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 for the past 40 years. Check it out. It made me think how can I do this in excel? First […]

Build a comparison chart
I found a chart that I wanted to show you how to build. The printable planting guide is found here. This is what the color means: Light green : plants grow well together Red : don´t plant together Dark green : the combination helps […]

Add pictures to a chart axis
This picture shows you a clustered column chart with pictures (flags) below each column. Instructions Select a data range Click "Insert" tab on the ribbon and click "Column" and then "Clustered column" Right click on the second series and click on […]

Group data by quarters
Sam asks: S/N RailCorp Ref Number Date In 77203 HRC mod program 10377 24/05/2011 77204 HRC mod program 10285 20/04/2011 77697 HRC mod program 10489 5/07/2011 77698 HRC mod program 10554 8/08/2011 77699 HRC mod program 10408 8/06/2011 77700 HRC […]

Copy filtered excel tables in vba
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is easy. It copies all table headers and data to sheet 2010, cell A13. Copy a filtered table The macro above […]

Highlight a selected cell´s row and column
Today I would like to share to you these small event handler procedures that makes it easier for you to read data on the same row or column. These procedures highlight a row and column of the selected cell. Make […]

Optimize pick path in a warehouse
As you probably already are aware of I have shown you earlier a vba macro I made that finds the shortest path between two points. There are obstacles between these two points to make it more difficult. The problem with […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column
Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the other column? I'm comparing list 1 to list 2 List 1 A 10 A 10 B 5 B 6 B […]

A quicker A * pathfinding algorithm
3 weeks ago I showed you a A* pathfinding algorithm. It was extremely slow and sluggish and I have now made it smaller and faster, much faster. Here is an animated gif showing you a grid 255 * 255 cells. The blue cell […]

Learn how the MOD function works
The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo operation (link to wikipedia). MOD(number, divisor) Number - The number for which you want to find the remainder. Divisor - The […]

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 the shortest path between a start cell and an end cell in a maze, and it did that using only […]

Basic substitution cipher
A twitter account belonging to NSA posted this weird message one morning in the beginning of May. It is a basic substitution cipher meaning t=w, p=a and so on. The message is "Want to know what it takes to work at NSA? Check […]

Move data to workbooks
Macros are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is about automatically moving data to a worksheet you specify. I got a comment from Andi: is it possible for add values to […]

Learn Excel’s Subtotal feature
You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so they can be collapsed or expanded. First you need to prepare your data, remove blanks, sort the data and insert […]

Sort and return unique single digit numbers
Eddie asks: Find and Sort I have question, in range A1,A2,A3,A4 contain 097 494 861 575. What is the formula in excel if the result that i want is 01456789 thanks I use two formulas, one array formula in cell range […]

Print screen the entire worksheet
I recently discovered something pretty cool. Sometimes you need to take a screenshot of your worksheet and you can do that by pressing the PrintScreen key on your keyboard. (Alt + Printscreen takes a screenshot of the active window or […]

Highlight unique values in a filtered excel table
Conditional Formatting has some amazing built-in features, for example it lets you highlight unique values in a list without entering a CF formula. If you don´t know how to do this, follow these instructions: Select your list Go to "Home" tab […]

Customize excel maze
Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I built a maze in excel last week, if you don´t check it out. This workbook allows you to choose the […]

Solve a maze
My last post demonstrated how to build a random maze. You might remember that there was only one path between the start point and the end point. This post shows you how to find the path between the start and […]

Build a maze
Two weeks ago I posted a link to a workbook containing formulas calculating the shortest path in a maze. Today I have created a macro that builds a random maze. The cell grid is 110 x 110 and the start […]

Introducing Get Digital Help´s Advanced Excel Course
I am very excited to introduce my new excel course: Advanced Excel Course If you think the array formulas presented here on my blog are fantastic but don´t have a clue how they work, this is the course for you. […]

Shortest path
I found a really interesting workbook that calculates the shortest distance from a start cell to an end cell. It is all made with excel formulas, not a single vba line! It uses a grid of 12 by 12 cells […]

Animating a line chart
Today I will show you how to build a basic animated line chart. The chart has a small trailing shadow effect. How I made this chart The chart data is in cell range B3:J8. A small vba macro calculates the difference […]

Merge lists with criteria
I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells . It demonstrates how to merge two different cell ranges dynamically and that is it. The following examples merge data tables with […]

An animated bar chart
This chart animates one bar at a time. The "Clear chart" button clears all values. The "Animate" button starts the animation. Check out my other animated chart:An animated excel chart If you think this animation is too slow, see the […]

Find the most urgent work orders
This animated picture shows you the most urgent work orders for a location. Type a location in cell F3 and the formula in cell G3 extracts the most urgent value for that particular location. The values in column C are […]

Find last matching value in an unsorted list
I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about finding the last matching value in a sorted list. It got me thinking how to find the last matching item in […]

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 excel creates checkboxes instantly. Click a checkbox and the related text is marked as finished. Also, if you delete text the corresponding […]

An animated excel chart
The drop down list lets you select a region. An event macro checks if there is a new drop down list value. It then compares old and new series values so it can calculate new values, in small steps. Build a […]

Highlight a group of chart bars
Excel has some incredible tools for highlighting cells, rows, dates, comparing data and even series in line charts. A technique using the secondary axis allows you to highlight a bar in a bar chart or column chart. The following chart highlights countries depending on the selected region. You […]

Team ranking with criteria
Today I´ll show you how to rank teams using excel formulas. The following example ranks soccer teams in the Premier League. But first, how are soccer teams ranked in Premier League? Teams are ranked by total points, then goal difference, […]

Search all workbooks in a folder
Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

5 most popular blog posts 2013
2013 is almost over. Here is a list of the 5 most visited blog posts 2013, written 2013. Copy / Rename a file (excel vba) Learn more about file copying techniques in excel using visual basic for applications. Custom data […]

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 Value). I made a similar blog article before (Calculate your stock portfolio performance with Net Asset Value based on units in […]

Lookup and return multiple values sorted in a custom order
Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to return the value with a prefix order would it possible? If not can I just add another column in the […]

Add your personal excel macros to the ribbon
If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that opens every time you start excel. You need excel 2010 for this tutorial. Create a personal *.xlsb file Create a new […]

Identify overlapping records
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in […]

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 words they show how the worksheet will be printed. If you click Page Break Preview button on tab "View" and […]

Compare values between two columns and filter values existing in only one column
There are text values in column A and column B. The question is how do you compare the values in these two columns? More specifically, filtering values existing only in column A (or column B)? The array formula in cell […]

Quickly change chart data source
In excel it is not easy to change a chart´s data source without manually changing the data source in chart settings. I wrote this post Change chart data range using a drop down list (vba) almost two years ago. So how do […]

Working with textboxes (vba)
Excel allows you to insert text boxes on a sheet. There are two kinds of text boxes. Form controls and ActiveX controls. Form controls can only be used on worksheets whereas ActiveX controls are far more advanced and can also […]

Show and hide a picture (vba)
Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it again the picture disappears. This post explains how to do that. The following picture shows when you click the shape, […]

Multiple wildcard lookups and include or exclude criteria
Dave asks: Hi Oscar, This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. However I have been trying to modify the array formulas in the 3rd and 4th posts of that series to […]

Lookup with an unknown number of criteria
Rashid asks: I used your array formula with great success to find the search results from multiple critera. However my problem is modifying your formula. In the above example you have shown us, you have two criteria. And you distinguish […]

Click a button to make specific worksheets hidden or visible (vba)
Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7. Instructions Here is how I did it. Create a button Go to Developer tab on the ribbon Click "Insert" button Click "Button" button Drag on […]

Click a cell to make a column hidden or visible (vba)
Click a single cell in column D to hide or show the comments in column E. Selecting multiple cells won´t change anything. VBA Code Where to put the code? Right click on sheet name Click "View Code" Paste code to […]

Scroll through a data set
Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. With a scroll bar or spin buttons you can accomplish that easily. Clicking the up or down arrows on the […]

Count unique distinct values with a condition
Stephen asks: Could you help me with (1) the Count the number of unique Divisions where ALL products have been delivered. (2) The Count the number of unique Divisions where NOT ALL products have been delivered. Determined by the presence […]

Counting conditionally formatted cells (vba)
UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you tweak the conditional formatting formula to count conditionally formatted cells. The user defined function in cell D2 counts cells with the […]

Count cells by cell and font color
Dave asks: How difficult would it be to make it count colour alone (so not unique values) and / or use cell font colour or other features also (bold for example) ? Excel has some quick green (with green text) […]

Calculate min and max date among overlapping date ranges and based on a condition
Liz asks: I want to identify the overlap based on a criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve? The following formulas calculates the min and max […]

Select and delete formulas evaluating to an empty text string
Deleting empty cells in a cell range is easy. Select the cell range Press function key F5 Click "Special..." button Double click on "Blanks", see picture above. Right click on a selected cell and click "Delete" Formulas evaluating to an […]

Prevent duplicate records (data validation)
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share it with you. Here is an excel table with example data. Let me show you how to prevent duplicate records […]

Format fill color on a column chart based on cell color
Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson asks: A little off topic question, but I am thinking outside the box a little. I have two charts that […]

Count unique distinct values by cell color
PRASHANT asks: i need to count unique number in a specific coloured cell for eg if there are coloured cells like red yellow green and in want to know unique number in red cell.... The following custom function counts unique […]

Pivot table calendar
Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table and two slicers. Instructions I am going to store my calendar data on another sheet named data. Step 1 - […]

Highlight date ranges overlapping selected record (vba)
The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional formatting highlight date ranges overlapping the selected record in the table. Instructions VBA Code Right click on sheet name Click […]

Basic mathematics in excel for school children
Right now most kids in Sweden are back in school after summer holidays. Their parents have had 4 to 6 weeks vacation during summer so it has been a time of joy and happiness. I have created an excel workbook […]

Find out if excel files in a folder are password protected
rahul jadhav asks: How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening […]

Count text strings in formulas (vba)
Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are starting from perticular word e.g. FDS, FDSB, etc some formula has FDS, FDSB occur in the middle of the formula. […]

Quickly select a data set or an excel defined table [HYPERLINK]
If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, the first one selects a data set, the second one selects an excel defined table. Adding more rows or columns […]

Quickly jump to last row in a data set using excel hyperlink function
Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set. Formula in cell C2: =HYPERLINK("[Quickly jump to last row using […]

IFERROR function
The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR function. IFERROR(value, value_if_error) If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, […]

Locate a particular cell in a table
Find a value and return a corresponding value is really easy with Excel´s INDEX and MATCH functions. Did you know that you also can quickly "jump to" and select that value in a table using the HYPERLINK function? RAJ.A.D asks: […]

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text
Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random number between 0 and 1. Example: 0.600842025092928 RANDBETWEEN function returns values in a range you specify. The function has two arguments: […]

Use drop down lists to compare data series in an excel chart
The two drop down lists below the chart let´s you compare two data series from separate tables. It is easier to follow the tutorial steps if you download the excel *.xlsx file: Compare data series in a chart.xlsx Drop down […]

Hierarchical relationships in an excel chart
The macro in this example adds series to a scatter chart. The first series is the dots and their names. The remaining series are the lines connecting the dots. They originate from the "Connected to" column in the table below […]

Locate a shape in a workbook
A blog reader asks: I need an excel 2010 macro, where I can enter a number (value) into cell B1, click on button below, and the page will automatically go to the corresponding shape, with that same number. Answer: Enter […]

Interactive candlestick chart
Rudy asks in this post Use a mouse hovering technique to create an interactive chart: Is it possible to create this interactive chart into interactive candlestick chart to compare two or more charts ? Hover over a company name and […]

Find consecutive dates in a cell range
Column B contains random dates. The array formula in column D returns consecutive dates from column B. Array formula in cell D3: =IFERROR(SMALL(IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, ""),ROW(A1)), "") How to enter an array formula Select cell D3 Copy […]

How to use FREQUENCY function
FREQUENCY(data_array, bins_array) Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array data_array - is an array of or reference to a set of values […]

Highlight a data series in a line chart
Here is how I did it: Step 1 - Create a drop down list Select cell J2 Go to tab "Data" Click "Data Validation" button Allow: List Source: B19:F19 Step 2 - Copy table Select first table (table1: A19:F31) Copy […]

Heat map using pictures
I made a heat map calendar a few months ago and it inspired me to do this post. The heat map calendar changes background color of each cell unlike the technique used here where I change the brightness of each […]

How to use excel SUMPRODUCT function
SUMPRODUCT(array1,array2,array3, ...) Multiplies corresponding components in the given arrays, and returns the sum of those products. What does that mean? I´ll demonstrate with a few examples. Example 1 - The basics Formula in cell B7: =SUMPRODUCT(B2:B4, C2:C4) Step 1 - […]

Count students
Kiishore asks Hi Oscar, I have the following issues. Any suggestions. Sheet 1 : Grade Male Female Primary Secondary High School College Gender Sheet 2 : Col A : Student Id Col B: Gender : Here we specify whether the […]

Use a map in an excel chart
This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter chart with a background picture. A drop down list let´s you select a city and the chart displays the location. […]

SEARCH and FIND functions
SEARCH function Returns the number of the character at which a specific character or text string is found reading left to rigt (not case-sensitive) SEARCH(find_text,within_text, [start_num]) Arguments find_text - Is the text you want to find. ? and * wildcard […]

Yet another excel calendar
The first sheet contains an overview. You have the option to select a year, date and color. Days with many "events" have a darker color, days with less "events" have a lighter color. The window to the right shows all events […]

MMULT function – Matrix multiplication
MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2. This function must be entered as an array formula. The following examples demonstrates how the function works. […]

SMALL function and LARGE function
SMALL(array, k) Returns the k-th smallest value in a data set. LARGE(array, k) Returns the k-th largest value in a data set. Arguments array - Is a reference to a cell range or is an array of constants. k - The […]

Copy/Rename a file (excel vba)
In this blog article I will demonstrate file copying techniques using excel visual basic for applications. The code below is tested in Excel 2010, Windows 7. Copy a file The following macro copies a file. The file name is in […]

Assign each person with randomly unique objects as a daily activity
Vijay asks: I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects as a daily activity. Would you please suggest me on how I can do it using excel formulae? Answer: The […]

ROW function explained
ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a reference to a cell range. Remember to enter […]

Highlight duplicates in a filtered excel defined table
You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want to find duplicates among filtered visible values. Example, the table below has filtered values from January 2012. Create a new […]

COUNTIFS function
The COUNTIFS function counts the number of cells across multiple ranges that meet all given conditions. It allows you to use up to 254 arguments or 127 criteria pairs. To make the function more useful Microsoft made it possible to […]

IF function explained
Table of Contents Overview Example 1 - Logical test Example 2 - Value if TRUE/FALSE Example 3 - Nested IFs Example 4 - Many conditions Example 5 - Array formula Download excel *.xlsx file Overview IF function =IF(logical_test, [value_if_true], [value_if_false]) […]

COUNTIF function
Table of Contents Introduction Example 1 - Count with single criterion Example 2 - Count cells larger/less than a criterion Example 3 - Count cells containing a text string Example 4 - Absolute and relative cell reference Example 5 - […]

Days contained in a range that overlap another range
Rene asks: Hi Oscar, I need a fomula that gives me the number of days contained in a range that overlap another range... not sure if that is clear enough... Answer: I got this from Carol Weideman, thank you! I think […]

MATCH function
Table of Contents Overview - Match function Lookup_array in ascending order Lookup_array in any order Lookup_array in descending order Array formula Using wildcard character VBA Example Download excel *.xlsx file Quick example The formula in cell C3 searches for […]

INDEX function explained
Table of Contents Overview Example 1 - array argument Example 2 - row_num Example 3 - column_num Example 4 - area_num Example 5 - Return a range of values Example 6 - Index returns a cell reference Overview INDEX(array, row_num, […]

Custom data labels in a chart
You can easliy change data labels in a chart. Select a single data label and enter a reference to a cell in the formula bar. You can also edit data labels, one by one, on the chart. With many data […]

Change picture (vba)
Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter a name and excel shows all details and a photo. Answer: Formula in cell C4: =INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0)) Formula in cell C6: […]

Heat map calendar
David asks: Hi, I would like to use this example with my data set however I'd like to visually show the amount of events per date to understand when are we the busiest, slowest, etc. and be able to forecast […]

Lookup using multiple conditions
Debraj Roy asks: Hi Oscar, I failed to search for "Ask" Section.. so submitting my query here.. Apologize for hijacking someones post,, and also for cross-post.. http://chandoo.org/forums/topic/lookup-using-multiple-condition Can You please help me to create a drag-able FORMULA to get Margin.. […]

Substitute multiple text strings (vba)
The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom function is case insensitive. Formula in cell B2: =SubstituteMultiple(A2,$D$2:$D$3,$E$2:$E$3) VBA code Go to VB Editor (Alt+F11) Click Insert Click Module […]

Sort cell values in corresponding columns
krish asks: I've a query in case of two columns of dates and two columns of data. Calling columns A and B as data(text), C and D as Dates(dd/mm/yy), IF column D's date is not empty and matches to the […]

Count multiple text strings in a cell range
The array formula below counts how many times multiple text strings exist in a cell range. It is easy to add more text strings, adjust cell range D1:E1 horizontally. Text strings "Car" and "bike" exist 5 times in cell range […]

Move a shape (vba)
This post shows you how to move a shape with vba code. Select a cell (button name) and the arrow instantly points to the button. I made a simple picture from an old remote I found. VBA code Right click […]

Sort a table with an array formula
The sorted table to the right is created with an array formula using the data in the table to the left. Array formula in cell F3: =INDEX($B$3:$D$8, MATCH(SMALL(COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, ROW(A1)), COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, 0), COLUMN(A1)) or use this regular […]

2012 website statistics
During 2012 this website had almost 1 200 000 visitors, a 95% increase. It got hit by the google algorithm update in early 2012 but recovered in a few months. June 12, 2012 was the best day in terms of […]

No more array formulas?
Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; My answer: No, not to my knowledge Kamran Mumtaz: This is the formula given by Aladin Akyurek without (CSE)... =INDEX(Sheet3!$B$2:$B$65, […]

Dynamic data labels in a chart
My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values and how to add corresponding data labels. I think the animated picture below says it all. How I created this […]

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 column chart in excel. The google chart is static, only the last column is divided into vertical rectangles. You can […]

Highlight events in a calendar
Steven asks: I got 6 events with different dates... Event - DATE START - DATE END 1 2 3 4 5 6 On sheet 2 i have a Year Calander (365 Days)I need to do a conditional formatting to highlight […]

Highlight a bar in a chart
This post demonstrates how to quickly bring attention to a single bar in a chart. Highlighted bars in two or more charts makes it easier to read and make comparisons. Here is how to do it with and without using […]

Repeat values
Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right article in your blog and therefore I want to ask you in newest post. So I have table similar like […]

Merge two related tables
This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to one table (data source) and I want to calculate the sales figures for each salesperson. The table to the right […]

Highlight events overlapping federal holidays
Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have a schedule table with event in 1st column, start date in 2nd column, end date in 3rd column, and a […]

Lookup multiple values in one cell (vba)
Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is a bit more complicated.. I have multiple info in one cell separated with ";" (example AD1; AD2; AD3) lets say […]

Sum security holdings monthly and yearly in a pivot table
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade securities or work as an accountant this blog post is for you. I am going to demonstrate how to sum […]

Run a macro from a drop down list (vba)
You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. Here is how I did it. Create a drop down list Select cell B2 Go to tab "Data" Click "Data […]

Use a drop down list to change conditional formatting
You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is how I did it. Create a drop down list Select cell D3 Go to tab "Data" Click "Data Validation" button […]

Prevent users from entering overlapping date and time ranges
The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, the following error message appears. How to create the data validation If you apply data validation to a table, all […]

Group a number of rows together by the first column
Mike asks: Oscar, I'm hoping you can help. I am trying to group a number of rows together by the first column, providing a union of the column values. In the example below, we are looking to Vendors V1-V3 to […]

Lookup and return multiple values from a range excluding blanks
Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, […]

Find date range
Elizabeth asks: Hi Oscar, Need help with a formula Please. I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of […]

Find max unique value from a range that have duplicate numbers and blanks
Marc asks: How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values. Array formula in cell […]

Monthly time sheet by project
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can expand each month and see time spend on each project. The pivot table also shows a summary for both months and projects. I […]

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 year, quarter to quarter (previous year), month to month (previous year) Product trends Region trends Introduction to pivot tables A […]

Hide specific columns (vba)
Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am also going to show you how to hide columns in a table. The table is a data source for a […]

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 off. In fact, Warren Buffet recommends investing in an SP500 index fund if you have no knowledge investing in the stock […]

Use multiple conditions in Vlookup
Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down list VLOOKUP and two conditions (date range) INDEX and MATCH VLOOKUP and a condition The animated picture above shows you […]

Locate lookup values in a table [HYPERLINK]
Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When you click the cell that contains the HYPERLINK function, Excel goes to that location in the table. Formula in cell […]

Applying conditional formatting to related tables
It can be really hard trying to follow a lookup in related tables. I will show you how to use conditional formatting for easy identification. If you add more rows to the tables, the conditional formatting expands automatically. Conditional formatting formula applied […]

Working with three related tables
I have written a few posts about two related tables and today I am going to show you how to work with three related tables: Lookups in three related tables and return multiple values Filter unique distinct values from three […]

Extract unique distinct values from a related table
In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a related table. Unique distinct values Duplicate values […]

How to use a table name in data validation lists and conditional formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") You can also use this technique in data validation lists. I used it in the three drop down lists below. The formulas are in […]

Change bar color in charts (vba)
Peter asks: Hello, I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 series of yearly sales. I have defined range names FW for series 1 and SS for Series 2. I would […]

Lookups in a related table (array formula)
The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect tables to each other by relationships. When relationships are made nothing stops you from doing lookups to related values and […]

Inventory consumption
Haroun asks: The problem is with regards to analysing the remaining life of my inventory based on the quantity available and the monthly consumption. Its as follows : Assuming today's date is 1st of September. I have 20 packets of […]

Copy each sheet in active workbook to new workbooks
Table of Contents Copy each sheet in active workbook to new workbooks Copy selected sheets to new workbooks Copy sheets in every open workbook to a master workbook Add workbook name to sheets Copy each sheet in active workbook to […]

Filter values that exists in all three lists
Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also I […]

Copy excel table filter criteria (vba)
Here is how to copy filter criteria from an excel table and use the same table filters on another table. Table1 is on sheet1 and table2 is on sheet2. Both tables must have the same header names and in the […]

Quickly search a table using many criteria
Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i have to find 1500, so it is very difficult to dig out 1500 one by […]

Create name initials
Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials How to enter an array formula Initials from first and last names Formula in cell B2: =MID(TRIM(A2), 1, 1)&" "&MID(TRIM(A2), […]

Search a table and use the returning value to search another table
Nena asks: Hi Oscar,I've been trying to find the solution for my lookup problem for a while now and you seem like the right person to ask... Your lookup code works great (thanks) but I need to do two or […]

Use a calendar to filter a table
My previous post Excel calendar (vba) used vba and formulas to extract events. This post demonstrates how to filter a table using a calendar. Select a date on the calendar and events on that specific day are automatically filtered. I would certainly have […]

Excel calendar (vba)
This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day are shown below the calendar. Today´s date is highlighted yellow. Days with one or more events are also highighted. How […]

Fetch data from another table
Phoneix asks: I have a worksheet(#1) and I want to populate the amount Column with data in another worksheet in the same workbook, Based on 2 criteria: State and Date Worksheet #2 Table A1:D19 = EST ColA ColB ColC ColD […]

Use filtered table values in a drop down list (vba)
I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined function and uses it in a named range. That is something I have never seen before and it inspired me […]

Dynamic chart – Display values from a table row or column
The following animated gif shows you a sheet where you can select column (Region) or a row (Month) and the chart updates correspondingly. I am only using named ranges and a table to create this effect. The beauty with this […]

How slow is ReDim Preserve?
Rick Rothstein commented: Redim Preserve does not execute all that quickly, so it is usually a good idea to avoid using it too often. That made me curious, how slow is Redim Preserve? I am comparing ReDim and ReDim Preserve. […]

Shift Schedule
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., […]

Extract cell references from all cell ranges populated with values in a sheet
Jinesh asks: I have to combine 200 columns into one list. I know. I tried steps from 'Combine cell ranges into a single range while eliminating blanks' UDF, but looks like typing the formula itself is going to be a […]

Use a mouse hovering technique to create an interactive chart
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option Explicit vba blog blogged about this more than a year ago. He made an amazing Periodic Table of Elements. I used […]

Sort values in a cell using a custom delimiter (vba)
The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each cell using the delimiting character. VBA Code I used the "Sort array" function found here: Using a Visual Basic Macro […]

Count and visualize specific weekdays in a custom date range
The sheet demonstrated below let´s you type a custom date range and specific days. The calendar automatically counts and shows you selected days. How to use Type a start date in cell B18 Type an end date in cell B19 […]

Visualize date ranges in a calendar part 2
I have created a new version of Visualize date ranges in a calendar. This excel file let´s you enter names and date ranges (A20:G33). Duplicate names are allowed. Select year and month, days in that month are automatically calculated (row […]

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook
A week ago I posted Create links to all sheets in a workbook and today I want to show you how to create links to all: Worksheets Pivot tables Tables Named ranges in a workbook. How to use Select a cell […]

Count unique distinct values in a filtered table
A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a formula by Laurent Longre found here: Excel Experts E-letter from John Walkenbach's web site. I remember a post I did about extracting unique […]

Create links to all sheets in a workbook
The macro demonstrated below creates hyperlinks to all sheets in current workbook. Select a start cell and then run macro CreateLinksToAllSheets(). VBA code Explaining vba code Step 1 - Open VBA Editor You write macros in the Visual Basic Editor. […]

Quickly create new sheets (vba)
The following macro let´s you select a cell range and then the macro creates sheets with the same names as the selected cells. Now you can easily create many sheets very quickly. VBA macro Explaining code Creating procedures in excel […]

Adjust stock chart axis automatically
I posted a userdefined function a year ago that automatically imported stock prices from yahoo finance. The workbook demonstrated in this blog posts imports stock prices and refreshes a stock chart. The problem is how excel calculates maximum and minimum axis […]

Insert hyperlinks to all files in current folder
The macro creates a new sheet. Inserts all filenames in current folder as hyperlinks except the current workbook. VBA Code Download excel *.xlsm file Insert files in current folder.xlsm

Match a criterion and extract multiple corresponding table headers
Vikas asks: i need to extract the headers from a grid based on value in left most column example row header ---> a b c d e data 1 1 2 2 2 2 1 1 1 1 1 2 […]

Plot buy and sell points in an excel chart using two moving averages
Below is an animated gif demonstrating a stock chart with monthly prices. Two moving averages #1, #2, buy and sell points are plotted in this chart. When the moving averages intersects a buy or sell point is created. If you […]

Add buy and sell points to a stock chart
It is easy to create a stock chart in excel. In this post I am going to describe how to insert buy and sell points. A 50 day moving average will work as an indicator. If the average changes from […]

Interactive chart in excel (vba)
I found a basic interactive chart on the chitika website and my first thought was if I could do this in excel. So now I would like to share this simple chart with three buttons. You can select multiple buttons […]

Highlight values within specific ranges
Merit asks: I have a large array of numbers and a row of numbers. I want each number in the array to become higlighted one color if it is within 4 of any number in the row and another if […]

Sort values in a table (vba)
awall asks: Hey can you do the opposite of this - not random order but this is my situation. i have 3 rows A1 - Item Number B1 - Description C1 - Price These will constantly be having new numbers […]

Normalize data, part 2
Here is another macro to normalize data. Scenario: The user has entered multiple values in the same cell. The macro reorganizes values into a cell each. VBA macro Download excel *.xlsm file Normalize data2.xlsm

Search two related tables simultaneously (vba)
Let´s say you do a lot of searches in two tables. The tables are related so it would be great if the second table is simultaneously filtered depending on the filtered values from the first table. Example, You want to […]

Create comments in cells whose value is larger than the column width
It can sometimes be helpfull having a large cell value in a comment. You can then easily read the value (hover over cell) instead of resizing column widths. The following subroutine adds a comment to cells where the value exceeds […]

Lookup multiple values in different columns and return a single value
Table of contents Lookup multiple values in different columns and return a single value Lookup multiple values in different columns and return multiple values Lookup multiple values in different columns and return a single value S.Babu asks: Dear Oscar, I […]

Learn how to return values depending on udf deployment (vba)
In this vba tutorial I am going to show you how to return values from an udf, depending on where you enter the udf. All udf examples in this post remove blanks but they differ on how values are returned. […]

Select a cell in a table and the chart updates automatically (vba)
The following vba code makes it possible to automatically update a chart when you click a cell in a table. See animated gif. You can also select multiple cells in the table (Press and hold Ctrl when selecting cells). Vba […]

Excel template: Getting Things Done (vba)
In this blog post I am going to demonstrate a simple workbook where you can create or delete projects and add "next" actions to each project. You can also mark actions completed. "The Getting Things Done method rests on the […]

Disable autofit column widths for all pivot tables in a sheet
I read this post Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel automatically making column widths too wide when using urls in pivot tables. Stacey Armstrong demonstrates how to disable this setting. […]

Vba macro: Normalize data
Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes data for excel pivot tables. VBA macro Press Alt+ F11 Right click your workbook in project explorer Click Insert Click […]

Date ranges: Weeks within a month
Anees asks: Hi, The above formula in A9 "Increasing date in a column" works pretty good however I have a small request to change the formula as per my need. As I fill the first column in excel with this […]

Add values to different sheets (vba)
Phil asks: Could you please show me the code to place the copied data into a different tab instead of below the input cells. Its annoying me and I'm rubbish at this. thanks. Answer: Create drop down list Go to […]

Add values to a table (vba)
Cyril asks: I do remember seeing one nice way of populating a table with the use of vba such as : How would it be possible to modify the code to populate a table such as: the first column header […]

Unique distinct records sorted by frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city.. Answer: Array formula in cell E3: =INDEX($A$2:$C$29, MATCH(LARGE(IF(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0, COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, […]

Count the number of groupings of each value
Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" .These alternate across 1800 colunms of data. My question: how do I count the number of groupings of each? In other […]

Use a drop down list to extract and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that contains my data and I want to generate a drop down list based on the input from one column. Which […]

Add or remove a value in a drop down list
Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I am using a dynamic named range. Enter a value in cell E3 and press "Add" button to add the value […]

Count specific weekdays in a date range
NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for example Mondays or any weekday in a date range. Later in this blog post I will show you how to […]

Open excel files in a folder (vba)
This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The last macro opens selected files. List files in a folder The following macro is assigned to the "Refresh list" button. […]

Copy table design settings
The macro provided in this blog post let´s you copy table design settings to other tables in the same workbook. Table style options and table style are copied and applied to other tables. You begin selecting the source table. Run […]

Auto populate a sheet
Rodney Schmidt asks: I am a convience store owner that is looking to make a spreadsheet formula. I want this formula to use information from one spreadsheet to auto-populate another spreadsheet on the next tab. I want the date the […]

Search for a text string and return multiple adjacent values
Table of Contents Search for a text string in a column and return multiple adjacent values Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values Search for multiple text strings […]

Toggle a macro on/off using a button
This post demonstrates using a single button (form control) to turn a macro on or off. Assigned vba macro I am not going to post the add and remove checkboxes vba code again. You can find them here: Excel vba: […]

Calculate dates in each step in a project based on a finish date
Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes take different times to complete (ie. one step could only take a week, another could take up to 4 weeks). […]

Use a drop down list to select company info in header (vba)
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What i would really like to do is have a drop down menu with the three company names: eg: Mcdonalds, Pizza Hut, Subway and then when […]

Automate data entry (vba)
In some cases it can be useful and timesaving to automate data entering. The vba examples here all enter a value or formula in a cell if a value is entered by the user in an adjacent column. Example 1 - […]

Apply data validation lists dynamically (vba)
Table of Contents Applying data validation lists dynamically using a table Applying data validation lists dynamically (vba) Applying data validation lists dynamically using a table Create a table Select cell range A1:C11 Go to tab "Insert" Click "Table" button Select […]

Workbook log (vba)
This post demonstrates how to automatically create log entries when a workbook opens or closes. VBA code Where to copy code? Launch Visual Basic Editor or press Alt+F11 Double click "This workbook" in project explorer Paste code into code window […]

Working with formulas in conditional formatting
In this post I am going to try to explain formula basics in conditional formatting. It is really good if you have some knowledge about absolute and relative cell references but it is not necessary. I think the examples shown here explains […]

How to create random numbers, text strings, dates and time values
In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining the RAND() function with a few other functions makes it possible to create random numbers, text, dates and time values. […]

Categorize data entry values (vba)
In a previous post I described how to simplifiy data entry. Now it is time to put values in separate categories. VBA code Final note My example is not the best way to organize data but it gives you a […]

Dependent data validation lists in multiple rows
This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent column. There is no vba code in the example demonstrated here. Named ranges The following named ranges expands automatically when […]

Holiday planner
Todays template is a holiday planner. It is a stacked bar chart and the worksheet contains a few lines of vba code to self adjust min and max date in the chart. The chart adds new names as you type […]

Advanced Gantt Chart Template
This Gantt chart template is a stacked bar chart and new tasks are added as you type them. Completed (green) and remaining (red) days are shown in the chart. The template uses dynamic named ranges. You may have to adjust the […]

Auto resize columns as you type
Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, You can also copy and paste cells and the columns resize automatically . Example, VBA code Private Sub Workbook_SheetChange(ByVal Sh As […]

Search with conditional formatting
I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and rows. I am going to explain how to create the highlighting and the conditional formatting formulas behind. Setting up the […]

Tracking calls in excel
Bill Truax asks: hello Oscar, i am building a spreadshet for tracking calls for my local fire depatrment. i have column "a" as incident number. the incident number is a one timme yearly number usage. column "c" is apparatus name and […]

Free School Schedule Template
This template makes it easy for you to create a weekly school schedule. The template has hours divided into 10 minute intervals. You can easily change the conditional formatting color. Download template excel 2007 *.xlsx school schedule.xlsx How I created […]

Copy a cell range whose size is likely to change from time to time (vba)
In this blog post I will demonstrate some vba copying techniques. Example 1, This example code copies cell range A1:B2 to A5:B6 in the active sheet. You can make the code even shorter: Range("A1:B2").Copy Range("A5"). Example 2, The current region […]

Easily identify groups of duplicate rows
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small “d” + a running number for the duplicates which are duplicated the same. If no duplicates only to put in […]

List all open workbooks and corresponding sheets (vba)
In this post I am going to demonstrate how to create a new sheet in the current workbook and list all open workbooks and their sheets using visual basic for applications. Example, Macro code How to copy the macro to […]

Basic data entry (vba)
In this small tutorial I am going to show you how to create basic data entry with a small amount of vba code. Cell B3 and C3 are input cells. When you click button "Add", the data in cell B3 […]

List all tables and corresponding headers in a workbook (vba)
This macro creates a new sheet and lists all tables and corresponding table headers in a workbook. Example Sheet1, 2 and 3 contain three tables. Run macro Go to "Developer" tab Click "Macros" button Click "ListTables" Click "Run" Create macro […]

Basic invoice template in excel
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a table, tblCustomer, where I add new customers. Similarly, in the Vendor sheet I have a table, tblVendor, where I add new […]

Filter weeks from a date range
Deeks asks: need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap […]

Working with combo boxes (Form Control) using vba
This blog post demonstrates how to create, populate and change comboboxes (form control) programatically. Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab. […]

Excel charts: Multiple series and named ranges
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. However, something happened with the chart and now it is not updating when new data is added to the table. […]

Use a drop down list to search and return multiple values
Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50 I then need to be able to look up whatever […]

Change chart data range using a drop down list (vba)
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) above the chart and selected input range:$E$2:$E$4. Cell range $E$2:$E$4 contains the table names from sheet 2011, 2010 and 2009. See […]

Change pivot table data source using a drop down list
In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using a drop down list. The tutorial workbook contains three different tables (Table1, Table2 and Table3) with identical column headers. In […]

How many of a specific weekday falls between a start date and an end date except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie Wednessdays,fall between a start date and stop date. Here is the formula: =SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(ROW(INDIRECT(D3&":"&D4)),"dddd"),D15,0)))) The only thing is this doesn't take […]

Separate numbers from a text string
Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in different cells. This is a monster size array formula in cell C5 and it was created in excel 2007. The […]

Select numbers in each permutation
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in the number i want it to automatic permutate the numbers and list in details, example if i key in 1234 […]

Dynamic Gantt charts in excel 2007
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps you plan and track various elements of a project. A dynamic chart automatically adds new values to the chart. Let´s start! […]

Excel vba: Filter a table using the selection change event
In this post I am going to demonstrate how to quickly apply a filter to a table. I am using the selection change event to apply the filter. Click on a cell in a table and the cell value is instantly […]

Extract unique distinct values from a filtered table (udf and array formula)
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection Change AutoFilter on multiple colums. Is there a way to make the list only return the unique values that are […]

Zen archery problem
vicktor schausberger writes: Zen Archery In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer […]

Excel vba: Populate a combobox with values from a pivot table
In this post I am going to demonstrate two things: How to fill a combox with table headers Populate a combobox with unique values using a pivottable Populate a combobox with table headers In the picture below you can see […]

Excel array formula: Enter a value in a cell and instantly populate adjacent cells
Stephanie asks: I have been working on a worksheet and cannot figure out the formula that would work to copy to the other cells and result in the duplicate entries being shown. I want to enter the phone number in […]

Excel: Look for values containing a lookup value and return multiple values horizontally
Jenny L asks: I have the following lookup values: pen eraser paper paper clip and the following item list: Allan's pen eraser Frances' eraser Jenny's pen paper paper clip pen red pen What I’d like to do is search for […]

Copy selected rows (checkboxes) (2/2)
This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove checkboxes to a sheet: Excel vba: Add checkboxes to a sheet (1/2) Sheet1 Sheet2 VBA code Where to copy vba […]

Excel vba: Add checkboxes to a sheet (1/2)
In this post I will demonstrate how to create checkboxes in nonempty rows. VBA code Where to copy vba code? Copy above code Press Alt+F11 in excel Insert a module Paste code into code window Return to excel I […]

Excel: List intervals between two values
jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the answer must be for the value 1 the spaces are 2,2,6. thank you Answer: Array Formula in cell E2: =SMALL(IF($A$1:$A$14=$C$2, […]

Add values to a data validation list (vba)
In this tutorial I am going to show you how to create a drop down list (data validation) in cell C2. When a value is added, changed or deleted from column A, the drop down list is instantly refreshed. Explaining […]

Follow hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise the cell is a hyperlink. In addition, when I update the indivital pivot tables, it looks at the data in […]

List five smallest numbers, excluding zeros
Muhammad Saleem asks: I a column with over 400 entries. Most of them are 0s. I would like to list the 5 smallest numbers excluding 0s. What is the best possible formula? Answer: Array formula in cell C1: =SMALL(IF($A$1:$A$400<>0, $A$1:$A$400, […]

Quickly filter a column in an excel table
In this tutorial I am going to demonstrate how to filter an excel table very quickly. There is not much vba code to make this possible. How it works Type a value in cell range A2, B2 or C2. Press […]

Excel udf: Reorganize data
Sean asks: Sheet1 A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA 13 Fuel 40 14 Diesel 200 15 16 Europe Lights Type A 100 17 USA Fuel Diesel 40 Oscar,is […]

Populate a list box with visible unique values from an excel table (vba)
Excel tables, introduced in excel 2007, sort, filter and organize data any way you like. You can also format data and choose from many professional looking styles. In this vba tutorial I will show you how to populate a list […]

Excel vba: Populate a combo box (form control)
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and populate a combo box (form control) Copy selected combo box value to a cell Refresh combo box using change events […]

Excel table: Filter unique distinct rows and use additional filters at the same time
This blog post describes how to filter unique distinct rows using an excel table. This post shows you how to do it using array formulas: Filter unique distinct records with a condition in excel 2007 The difference with today´s blog post is […]

Excel table: Filter unique distinct values (array formula)
This blog post demonstrates how to filter unique distinct values from an excel table dynamically. When you change or add a filter to an excel table, the array formula extracts unique distinct values instantly. Example, to the left is a […]

Count unique distinct values in a large dataset with a date criterion
davidlim asks : i have ~100K rows, and Excel is literally stalled when running the formula. for the time being, i'm using a Pivottable and using a COUNTA function to count unique distinct value. Not automated but it's near-instantaneous to […]

Excel udf: Looking up data in multiple cross reference tables
This post describes how to lookup values in multiple cross reference tables using a user defined function. This custom function accepts an arbitrary number of range arguments (table ranges). The custom function returns the first match in each table. Array […]

Count entries between date and time criteria in excel
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count how many entries are between date and time. So I have a shift that starts at 6:30:00 PM and leaves […]

Vlookup visible data in a table and return multiple values in excel
This post describes how to search visible values and return multiple values from a table. Some rows are hidden because of table filters. I am not using the vlookup function in this formula. Example, Array Formula in cell B14: =INDEX($C$2:$C$9, SMALL(IF($B$11=(IF(SUBTOTAL(3, […]

Compare two tables in excel: Remove common rows
Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract non common records from two tables. The formulas in this post contains the COUNTIFS function and is introduced in excel […]

Excel 2007 vba: Create a custom-made item on the shortcut menu
This post describes how to add a new custom-built item to the shortcut menu in excel 2007. In excel 2007 when you right click a cell, a cell context menu appears. Let´s add a new item to the cell context […]

Excel udf: Sumif across multiple sheets
This post describes a basic user defined function that searches multiple ranges and adds corresponding values. You can also use multiple search values. VBA code: Function SumifAMS(lookup_value As Range, ParamArray […]

Insert rows for missing values in excel
HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. opposite the name of customers are OR No. issued to various customers. OR No. is in broken sequence. My question […]

Excel vba: Select cell A1 on all sheets before you close a workbook
This post demonstrates how to automatically select cell A1 on each sheet in a workbook before you close a workbook. The vba code also makes cell A1 the upper left cell on all sheets. VBA code Private Sub Workbook_BeforeClose(Cancel As […]

Filter unique distinct and duplicate values from a large data set in excel 2007
In this post I am going to describe how to filter duplicate and unique distinct values from a really large dataset. Both array formula and advanced filter are too slow. In this example, cell range A1:A1 000 000 contains seven […]

Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed
Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple columns) not […]

Excel udf: Import historical stock prices from yahoo – added features
This post describes how to import historical stock quotes from yahoo. This custom function is more advanced than the previous one: Excel udf: Import historical stock prices from yahoo You can choose: Start date End date Interval (daily, weekly and monthly) […]

How to sort a table in a custom order in excel
Your boss wants you to sort the company´s products by a new criterion, quality. You receive a list from your boss and now you have to sort your products by this list. You copy […]

Vlookup across multiple sheets in excel
This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and sheet2 contains table 2. The search value is Pen and is in cell B9. Array Formula in cell C9: =IFERROR(INDEX(tbl_1, […]

Count unique distinct values that meet multiple criteria in excel
This post demonstrates how to calculate unique distinct products (Column E) and meeting a criterion or multiple criteria. Table of Contents How many unique distinct products did Salesperson Jennifer sell? How many unique distinct products did Jennifer sell in January? […]

Excel udf: Import historical stock prices from yahoo
Here is how to quickly import historical stock quotes from yahoo. First you need to know the company ticker. Go to Yahoo Finance and type the company name here. Microsoft has symbol ticker msft. Use the argument msft in the custom […]

Sum the cells in a range that meet multiple criteria in excel
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from one sheet (A) into another sheet (B). I need excel to search through the dates in sheet A to find […]

Identify duplicate files in excel
Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to show you how to identify duplicate files from the list we created yesterday. This can be useful if you have […]

Excel recursive udf: List files in a folder and subfolders
This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? Press Alt-F11 to open visual basic editor Click Module on the Insert menu Copy and paste vba code, both functions […]

Vlookup a range in excel
wackyboy asks: My database is as shown, where I have company abc sells siemens, omron and mitsubishi and company qwe sells omron n siemens. Company Products abc Siemens Omron Mitsubishi qwe Omron Siemens asd Omron Moeller zxc Mitsubishi Omron So […]

Count unique distinct values in a pivot table in excel
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a […]

Merge matching rows in excel (text values)
Rui Costa asks: I'm using Excel 2007 and I have 2 Sheets: Sheet1: ____A B C D E F bla X X X ble X X Sheet2: Name Letter1 Letter2 bla___A______B ble___A______C bla___B______A I'm trying to make a formula to […]

Excel toolbox: Save your custom functions and macros in an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, save user defined functions and macros in a personal add-in. When you open a new excel spreadsheet, these functions and […]

Auto refresh a pivot table in excel
In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

Date ranges for 7 days in excel
Shannon asks: I need a formula that if I enter a start date in field B1 such as 6/8/11 it will give me the date ranges for 7 days in fields B3-B14. Does that make sense? Basically I want a […]

Lookup with multiple criteria and display multiple unique search results (array formula)
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" but, if either of the criteria for "Middle Name" or "Last Name" will suffice. Also, i don't want repeated values […]

Compare pricelists in excel 2007
This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting formulas and highlight: new products yellow prices higher than previous year green prices lower than previous year red Sheet1 - […]

Quickly compare two tables in excel 2007
This blog post demonstrates how to quickly compare two tables in excel 2007. Table1 Table2 Create a fourth column and use this formula in first cell: Table1 =COUNTIFS(Table2[GivenName], Table1[[#This Row], [GivenName]], Table2[StreetAddress], Table1[[#This Row], [StreetAddress]], Table2[City], Table1[[#This Row], [City]])>0 This […]

Excel 2007 pivot table: Count unique distinct records (rows)
Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The table I am working with is in cell range B2:D7. I have then added another column (E) to count unique […]

Sum unique distinct invoices in excel 2007
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key (like rows 3:4 or rows 8:10). I'd like to sum data in column D and to consider same key rows […]

Count unique distinct records with a date and column criteria in excel 2007
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 date and 1 criterion. i slightly modify the formula to =SUMPRODUCT(--($B$1:$B$9=$E$2), --($A$1:$A$9=$E$3)) + ENTER [assuming E2 = 9-2-2010] the result […]

Google spreadsheets: Pivot tables
This week Google Docs introduced Pivot Tables in Google Spreadsheets. I am not that familiar to google spreadsheets but I had to try the new pivot table feature. I added some random data to a spreadsheet. I also created a new column […]

Filter unique distinct records with a condition in excel 2007
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions […]

Excel charts: Use dynamic ranges to add new values to both chart and drop down list
This post demonstrates how to automatically add new values to a drop down list and a chart. This tutorial contains three steps: Create two named ranges Create a drop down list Set up a chart Create two named ranges Click […]

Vlookup – Return multiple unique distinct values in excel
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? my sheet is setup as follows A B C D E 1 Section Category item flavor size 2 food Coffee Espresso none Single 3 food […]

Chandoo’s excel VBA classes are now open!
What is vba? Visual Basic for applications (VBA) programming. With excel vba skills you can: Create macros Automate your work Improve your productivity What is this VBA Class? The aim of VBA Classes is to make a beginner an expert […]

Calculate average of last 10 data (possible blanks) in excel
Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. Array formula in cell F18: =AVERAGE(INDEX(A:A, LARGE(IF($A$1:$A$25<>"", ROW($A$1:$A$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, $A$1:$A$25))) How to create an array formula Copy (Ctrl […]

Excel udf: Filter unique distinct records (case sensitive)
How to use udf (array formula) Select cell range F3:G6 Type =UniqueRecords((C3:D8) in formula bar. Press and hold CTRL + SHIFT Press Enter once Release all keys Excel user defined function: Function UniqueRecords(rng As Variant) As Variant() ' This udf filters […]

Excel udf: Filter unique distinct values (case sensitive)
How to use udf Select cell range D3:D10 Type =CSUnique(B3:B10) in formula bar. Press and hold CTRL + SHIFT Press Enter once Release all keys Excel user defined function: Where to copy vba code? Press Alt-F11 to open visual basic […]

Excel vba: Create dynamic conditional formatting
I have noticed when I apply conditional formatting on larger cell ranges, file size increases rapidly. This post demonstrates how to create dynamic conditional formatting. What is dynamic conditional formatting? As new rows or columns are added conditional formatting is […]

Create a dynamic named range in excel
In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named range. This makes the named range dynamic meaning you don´t need to adjust cell references every […]

How to create a dynamic pivot table and refresh automatically in excel
David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to automatically refresh a pivot table when sheet is activated. Excel 2003 and earlier versions: Create a dynamic named range This […]

Excel udf: Combine cell ranges into a single range while eliminating blanks
This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes blanks. Later in this post I will also demonstrate how to sort these values. Userdefined function in cell range B3:B70, […]

Bill reminder in excel
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where you enter your bills due date and their frequency. However what makes mine different than yours is I'd love for […]

Dependent drop down lists – Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know of a way to see additional information in drop down #4 if there are no selections in drop down #2 […]

Running totals within date range in excel
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, I have to match a client name, and add all the sales totals: clientA 10 clientA 10 clientA 10 clientB […]

How to quickly compare pricelists in excel
Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2010 and year 2011. To make things more interesting, price list 2011 is not sorted. […]

Excel udf: Word frequency
This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, position)+ Ctrl + Shift + Enter Udf in cell E3:E30: =FreqWords(B2:C11, 1)+ Ctrl + Shift + Enter How to create […]

Excel udf: Fuzzy lookups
In this post I will describe a basic user defined function with better search functionality than the array formula in this post: Fuzzy vlookup. The user defined function searches for a cell with as many characters matching as possible. It is […]

Filter records within two dates and search for a text string in excel
Murlidhar asks: How i search text in cell within two dates i.e st.Dt D1 end dt. D2 Search "soft" in entire column for" Microsoft" Answer: Array formula in cell F10: =INDEX($B$3:$D$29, SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1)) How to […]

Fuzzy vlookup (excel array formula)
The array formula in this blog article has no "Fuzzy logic" nor vlookup function. But it can return names or words arranged differently and with minor misspellings just like a user defined function with "Fuzzy logic". There are too many […]

Text to columns: Split words in a cell (excel array formula)
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to columns. Text to columns separates the contents of one cell into separate columns. See Rick Rothstein's formula in the comments! […]

Quickly highlight records containing text strings in excel (AND Logic)
This blog post shows you how to highlight rows where text string criteria are found. Example, continents criterion (cell B3) is only searched in column Continents (B7:B28). Color criterion is searched for in column Color, and so on. Conditional formatting […]

Excel udf: Filter emails from an excel range
This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some random emails in this range. VBA code Where to do I copy the code? Press Alt-F11 to open visual basic […]

Quickly highlight records in a list in excel (AND logic)
This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4. Conditional formatting formula: =SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7)))) How […]

Quickly highlight records in a list using multiple criteria in excel
This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in row 3. A row is highlighted if: Date criterion is found in column B or Color criterion is found in column […]

Cash drawer bill extractor template in excel
Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back in the cash drawer in small bills, change and rolls of change. I need to remove exactly $ 1178.29 in […]

Find duplicate records in excel 2007
Formula in cell G3: =IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "") + ENTER Copy cell G3 and paste down as far as needed. How the formula works in cell G13 Step 1 - Understand how relative and […]

Highlight closest value in excel
This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the second closest value (or more). Conditional formatting formula =OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2), ROW($A$1:INDEX($A:$A, $C$3)))) This is not working in excel 2010 and later […]

Merge matching rows in excel
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there prizes daily (1 week). take note that in col. A fruits name may randomly repeated in col A. What I […]

Excel udf: Find positive and negative amounts that net to zero
I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero and clear […]

Excel udf: Find numbers in sum
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see how quickly excel solver finds the numbers using the instructions from this post: Identify numbers in sum using solver in excel. […]

Filter overlapping date ranges in excel 2007
Introduction This blog article describes how to extract coinciding date ranges. Example, Array formula Cell range B3:C25 contains example date ranges. Overlapping date ranges Array formula in cell E4: =SMALL(IF(COUNTIFS($B$3:$B$25, "<="&$C$3:$C$25, $C$3:$C$25, ">="&$B$3:$B$25)>1, $B$3:$B$25, ""), ROW(A1)) + CTRL + SHIFT […]

Count digits and ignore duplicates in any line in excel
Question: I have a question that I can’t seem to find an answer to: I want to make a full count of digits 0 to 9 while ignoring duplicates in any line B C […]

Concatenate a cell range without vba in excel
Joining many text strings in excel is not easy. The Concatenate function accepts only a single cell reference in each argument. Example, =Concatenate(A1, A2, A3) + ENTER To quickly select cells you can press CTRL and click on cells you […]

Tracking a stock portfolio #2 in excel
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to calculate cost basis and returns. The calculations are simplified, commissions, stock splits and dividends are removed from calculations. In the […]

Excel udf: List permutations without repetition
This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in excel Press Alt-F11 to open visual basic editor Click Module on the Insert menu Copy and paste the above user […]

Tracking a stock portfolio in excel (auto update)
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row. He also wants the range extended down to include new rows as they […]

Create a drop down calendar in excel
This drop down calendar uses a "calculation" sheet and a named range. You can copy drop down lists and paste anywhere in workbook but they have to be in the same order and adjacent. You can select a year and […]

Weekly appointment calendar in excel
This weekly calendar is easy to customize. You can change calendar settings in sheet "Settings": Start date (preferably a monday) Start and end time Time interval Download excel calendar template Appointment calendar template.xls (Excel 97-2003 Workbook *.xls) Download excel calendar […]

Monthly calendar template #2 in excel
I have created another monthly calendar template for you to download. Download excel calendar template Monthly-calendar-template #2.xls (Excel 97-2003 Workbook *.xls) How the template works Select a month and year in cells A1 and B1. They are drop down lists. The […]

Monthly calendar template in excel
I have created a monthly calendar template for you to download. Download excel calendar template Week starts with sunday Monthly calendar template.xls (Excel 97-2003 Workbook *.xls) Week starts with monday Monthly-calendar-template weekstart mon.xls (Excel 97-2003 Workbook *.xls) How the template […]

Count unique distinct records in excel 2007
This example sheet has 4 columns with some random data. It is quite complicated trying to count unique distinct records from this table but Excel can help us with that. A record is an entire row in the table below. […]

Compare two lists of data: Filter records existing in only one list in excel
In this example we are going to use two lists with identical columns in excel 2007. It is easy to modify the countifs function if your columns are not ordered. This is the first example list on sheet: List 1 […]

Compare two lists of data: Highlight records existing in only one list in excel
Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on sheet "List 2" Type Year in Name Box Press Enter Repeat with remaining ranges: Sheet: List 2 , Range:B2:B13, Name: […]

Compare two lists of data: Highlight common records in excel
Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and reference cells outside current sheet Highlight common records in two lists Sheet: List 1 Sheet: List 2 Create named ranges […]

Compare two lists of data: Filter common row records in excel
This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: List 2 Comparing the two lists We need to know what columns to compare before we can create the array […]

Filter and highlight duplicate column records in excel 2007
This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell range B2:E5. See picture below. The array formula and conditional formatting formula in this article contain "Countifs", a function introduced […]

Filter duplicate rows in excel 2007
This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in excel 2007. Countifs applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Array […]

Highlight duplicate rows in excel 2007
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 + ENTER How to use conditional formatting formula Select cells A1:C30 Click "Home" tab Click […]

Excel udf: List permutations with repetition
This blog post describes how to create permutations. Repetition is allowed. Vba code: Function ListPermut(num As Integer) 'Permutations with repetition Dim c As Long, r As Long, p As Long Dim rng() As Long p = num ^ num ReDim […]

Count matching cells in date range in excel
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables uses either "PASS" or "FIAIL". All I want to do is to count the "PASS" in individual month range. Can […]

Excel udf: Lookup and return multiple values concatenated into one cell
The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Automate excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values. Sheet 1 is the old price list. It contains 5000 products and amounts. Sheet2 is the […]

Excel vba: Use text qualifiers to make text to columns conversion easier
This blog post describes how to create text qualifers and make "text to columns" conversion easier. Example I copied a table from Wells Fargo annual report (pdf). Pasted it into an excel sheet. Converted text to columns. The table is […]

Excel vba: Create a Print button
This post descibes how to create a print button on a excel sheet. Create button (Form Control) Select sheet "Invoice" Click "Developer tab" on the ribbon Click "Insert" button Click Button (Form Control) Create button "Print Invoice" Create macro Press […]

Excel vba:Select and view invoice
A previous post described how to create a listbox filled with unique invoice numbers: Excel vba: Populate listbox with unique invoice numbers This post describes how to view saved invoices. Cancel Command button Press Alt-F11 to open visual basic editor Right […]

Excel vba: Populate listbox with unique invoice numbers
This post describes how to fill a listbox with unique values. Create button (Form Control) Select sheet "Invoice" Click "Developer tab" on the ribbon Click "Insert" button Click Button (Form Control) Create button "View Invoice" on sheet "Invoice" Create macro […]

Excel vba: Edit invoice data
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing values with new values. If invoice number already exists, a message box asks if you want to overwrite old values […]

Excel vba: Save invoice data
This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows containing values. Blank rows are not copied. Values are copied from sheet "Invoice" to sheet "Invoice data". How to create […]

Invoice template with dependent drop down lists in excel
Overview This post descibes how to use a basic invoice template I created. The invoice template let´s you use dropdown lists to quickly select products on a price list. You can easily add/remove data on "price list" sheet. Features Formula calculates […]

Create random dates, Mon to Fri, within a year in excel
H.G asks: I need to create lots of random dates within a certain year, for which I simply use =randbetween(). However, only Mondays to Fridays are required, no weekend days. How could I possibly adapt a function / formular to […]

Return multiple matches with wildcard vlookup in excel
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup […]

Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple rows and let people enter detailed records. Answer: In my example workbook, I have created three sheets: Multiple rows Data […]

Excel udf: Filter values existing only in one out of two ranges
This post describes how to filter values existing only in one out of two cell ranges. See picture. This udf is not case sensitive Excel user defined function Function Filter_Values(rng1 As Variant, rng2 As Variant) As Variant ' This udf […]

Excel udf: Filter common values between two cell ranges in excel
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to extract common values between two cell ranges. 40000 random cell values in each cell range. As you might have guessed, […]

Excel vba: Select value based on time and date
Introduction This blog post describes how to automatically select a value based on time and date using vba in excel. Example, This workbook is automatically recalculated each minute. Array formula in cell F4: =INDEX($B$2:$B$34, MATCH(TEXT($E$2, "yyyy-mm-dd tt:mm"), TEXT($A$2:$A$34, "yyyy-mm-dd tt:mm"), […]

Excel udf:Filter duplicates in a large dataset in excel
Overview This blog post shows you how to filter duplicate values in a large list. We are going to create and implement an user defined function (udf). This udf also counts duplicates. Example, Filtering duplicate values from a big range […]

Compare and filter values between two lists in excel
Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. my problem is the following : I have 2 lists of items in col A and B not necessarely sorted […]

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel
Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a player chooses six numbers from 1 to 49 (no duplicates are allowed). If all six numbers on the player's ticket […]

Generate list of random dates with criterion in excel
Overview Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion is to generate random weekdays within a specified interval. From today and ten days forward. We are going to learn […]

Remove duplicate text strings based on the 4 last characters in a cell in excel
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on […]

Apply dependent combo box selections to a filter in excel 2007
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. Answer: Table of contents Overview Create auto expanding named ranges Array Formula on Calculation sheet Create another two auto expanding […]

Create a drop down list containing alphabetically sorted values in excel
Overview This article describes how to create a drop down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them. Here is a list containing some random […]

Excel udf: Count unique distinct values in a large dataset
Overview This blog post describes how to count unique distinct values in list. We have created excel formulas before to accomplish this task: Count unique distinct values in a column in excel Count unique distinct values in two columns in […]

List people with the highest scores based on criteria in a pivot table (Excel 2007)
Overview This post describes how to create a list of people, sorted by occurrence, based on criteria in a pivot table. In a previous post we created an array formula to accomplish the task. Andre asks: I am tryng to […]

List people with the highest scores based on criteria in excel
Andre asks: I am tryng to list the people with the highest scores based on certain criteria. My data: column A B C D Mike 207 Yes Life Greg 207 Yes Life Sid 207 Yes Life Greg 207 Yes Life […]

Remove duplicates and sort dates by each row in excel
I might have missunderstood Aamers question: I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates […]

Excel udf: Remove duplicates from a large dataset
Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates are in row A1:F1 […]

Sort a list in random order in excel
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array formula in C2: =INDEX(List, LARGE((ROW(List)-MIN(ROW(List))+1)*(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)), RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)))))) + CTRL + SHIFT + ENTER. How to create […]

Create a random playlist in excel – 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 draw? i.e. using a 6 team scenario, for any round there will be three games/matches, but obviously Team A […]

Highlight duplicate values and overlapping dates in excel
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out? eg Serial | Start date | End […]

Dynamic team generator in excel
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so the maximum number of team members looks to a cell (say E9) so the number of people per team could […]

Create a random playlist in excel
Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each team plays matches both "Home" against all remaining teams and "Away" against all remaining teams. Example: Team A plays "Home" […]

Team Generator in excel
This blog article describes how to create teams randomly. There are twenty names in column B and four teams in column E. There will be five team members in each team. In column C a team is randomly selected in […]

Count records between two dates with multiple parameters in excel
Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. I need to count how many times JA appears in a given period 1-Sep-10 and 10-Sep-10 A B 1 JA […]

Sort values in parallel in excel, part 2
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, say: Joel, AA, 7 Mark, FF, 31 Nick, AA, 7 with the possibility of matching sort criteria (columns 2 & […]

Random row from a boolean matrix in excel
Question: It’s the kind of thing I do regularly in matrix oriented languages (APL, K, Gauss etc), but I can’t make it work in excel. It would be a very handy construct to have around (if indeed, it’s possible). I […]

Search case sensitive and return multiple values in excel
Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and paste it down as far as needed. Array formula in E4: =INDEX($B$1:$B$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + […]

Filter unique words from a range in excel (udf)
Overview This blog post describes how to create a list of unique words from a cell range. Unique words are all words not having duplicates. Cell range A2:A14 contains words, see picture. Rick Rothstein (MVP - Excel) helped me out […]

Vlookup with multiple matches returns a different value
Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs. Here […]

Filter unique distinct words from a cell range in excel (udf)
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words are all words but duplicate words are only listed once. Cell range A2:A14 contains words, see picture. Rick Rothstein (MVP […]

Filter duplicate words from a cell range in excel (udf)
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate values in each rows. SAMPLE COLUMN A contains these data: 3M - Asia 3M South America 3M - Africa 3M […]

User defined function to split words in a cell range into a cell each in excel
This post describes how to split words in a cell range into a cell each using a custom function. I hope this picture explains it all: The cell range is A1:A10 and contain words. The array formula in C2:C27 contains […]

Consolidate sheets in excel, part 2
Neville Ash asks in this post: Consolidate sheets in excel (vba) I have down loaded the consolidated file and it does not appear to work the way I expected. I am looking to combine cashflow worksheets for multiple projects. The […]

Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table
Anura asks: I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those […]

Extract a unique distinct list by matching items that meet a criterion in excel
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a […]

Extract dates from a cell block schedule in excel, part 3
In this post we are going to extract all "not empty" dates in a simple calendar. In a previous blog post we extracted only date ranges and names. Here is a picture of the calendar. Here is a picture of […]

Extract dates from a cell block schedule in excel, part 2
The obvious question from yesterdays blog post is: How to customize formula to extract dates for all months in calendar? (I have added more data to the calendar since yesterday) Answer: Array formula in A2: =SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(Sheet1!$C$5:$AG$16<>""), DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), […]

Extract dates from a cell block schedule in excel
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula which will give us a below table StarWk EndWk Name 1 2 G 4 6 G 7 15 R ... […]

Concatenate cell values in excel
Arielle asks: i have a formula in row a from A1:Z1 that displays "" if there is an error. I need to then take that row and combine or concatenate all the cells into one cell and have each cell […]

Filter emails spread over several columns in excel
magneticone asks: Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns? You can find magneticone´s question […]

Search for a cell in a table and then display the column title in excel
Arielle asks: I have to search for a cell in a table and then display the column title. search value in cell e1: AA table in cells A1:C6 A1:x B1:y C1:z A2:BB B2:CC C2:DD A3:AA B3:GG C3:AA A4:CC B4:BLANK C4:EE […]

Schedule recurring events in a weekly schedule in excel
In our sequel about weekly schedule it is now time to create a recurring events. This is what we have created in earlier posts: Weekly schedule template How to highlight specific time ranges How to find empty hours How to […]

Setting up your work hours in a weekly schedule in excel
Hours outside workhours are filled with grey using conditional formattting, except weekends. Conditional formatting formula applied on cell range C6:I29: =AND(OR($B6<$C$31, $B6>=$C$32), AND(WEEKDAY(C$4)<7, WEEKDAY(C$4)>1)) This formula checks if the weekday in C4:I4 is 2,3,4,5 or 6. (Monday to Friday) and […]

Populate cells dynamically in a weekly schedule in excel
Table of Contents Populate cells dynamically in a weekly schedule Populate multiple cell values in a single cell in a weekly schedule (vba) Populate cells dynamically in a weekly schedule In this post we are going add one more function […]

How to filter numbers inside (and outside) number ranges in excel
How to filter numbers inside ranges in column E and F Array formula in A2: =SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)<=End)*(COLUMN($A1:$U1)>=Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))>0, ROW($1:$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and paste it down as far a sneeded. Alternative […]

Find empty hours in a weekly schedule in excel
In this post, I have created som random time ranges. We are going to use these time ranges to extract empty hours between ranges. Random date/time ranges: I have reused the same weekly schedule as in this post: Highlight specific […]

Count cell blocks in excel
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if the green blocks had the letter G and the Red blocks had the letter R and I had to return […]

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 easily add/delete records without changing the formulas, in other words cell refs to the table are dynamic. I am going to […]

Create dependent drop down lists containing unique distinct values in excel
Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Count overlapping dates in excel
Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range Count all overlapping days in any number of date ranges I have created some random overlapping date ranges in E17:E21 […]

Create a unique distinct sorted list containing both numbers text removing blanks in excel
Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted list containing both numbers text removing blanks with a condition Create a unique distinct sorted list containing both numbers text […]

Filter overlapping dates from date ranges in excel
Array formula in A2: =SMALL(IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)>1, MIN(Start)+ROW(OFFSET($A$1, , 0, MAX(End)-MIN(Start)))-1, ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and […]

Highlight specific time ranges in a weekly schedule in excel
In a previous post I created a simple weekly schedule with dynamic dates. In this post I am going to highlight hours using date and time ranges. Here are some random ranges: Here is a picture of the weekly schedule […]

Weekly schedule template in excel
I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell F2 and press Enter. Now what? All dates in chosen week are dynamically updated. See cell range C4:I4. Why? You […]

Convert date ranges into dates in excel
In a previous post I created a formula to convert dates into date ranges. Now it is time to create dates from date ranges. The date ranges are in cells C2:D6. The date list in column A is created by […]

Convert dates into date ranges in excel
Question: How do I convert a list of dates into date ranges? Answer: Create date ranges from a list of dates Array formula in cell C2: =IF(ROWS($A$1:A1)=1, INDEX($A$2:$A$29, 1), INDEX($A$2:$A$29, SMALL(IF($A$3:$A$29-$A$2:$A$28>1, ROW($A$3:$A$29)-MIN(ROW($A$3:$A$29))+2, ""), ROW(A1)-1))) How to create an array formula […]

List dates outside specified date ranges in excel
I don´t think I have explained how to filter dates outside date ranges. Example, Table 1 contains start and end dates for each date range. Cell B13 is the start date and B14 is the end date. The array formula […]

Visualize date ranges in a calendar in excel
Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible overlapping dates (red) not exisiting dates (black) Here are the date ranges: How I created "invisible" dates in cell range […]

Absolute and relative references in excel
What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total number of columns in Excel 2007 are 16384. Rows have numbers, 1 to 1 048 576. You guessed it right, […]

Highlight overlapping date ranges using conditional formatting in excel
How to highlight overlapping date ranges (Excel 2007) Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula to determine which cells to format" Type =SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 in "Format values where this formula is TRUE" window. Click […]

Find overlapping date ranges in excel
Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell E6: =SUMPRODUCT((B6< =$C$6:$C$12)*(C6>=$B$6:$B$12))>1 Copy cell E6 and paste it down as far as needed. Here is a picture of the […]

Use a number range when trying to find the frequency of a number in excel
Question: Is there a way I can use a data range when trying to find the frequency of a number? The number ranges are: larger or equal to 21% and smaller than 22%, larger or equal to 22% and smaller […]

How to return the total for orders with more than one item in excel
Question: I have given up trying to figure out the following problem as it seems over my head, and am hoping there is a kind soul out there can help me out with the solution. I am trying to setup […]

Merge two columns with possible blank cells in excel (formula)
Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my "List 1" is auto updated and the number of entries in this list will fluctuate. Since the number of entries […]

Count unique and duplicates text values in a closed workbook in excel (formula)
Here is a picture of a closed workbook How to count unique distinct text values in a closed workbook This formula works with possible blank cells in range A1:A6. =SUM(IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 0, IF(MATCH('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 0)=ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)-MIN(ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6))+1, 1, […]

Extracting unique distinct text values from a closed workbook in excel (formula)
In this post I am going to use the same formula found in this post: How to automatically create a unique distinct list and remove blanks. I have only changed the cell references to include a full path to the […]

Count unique and duplicate numbers from a closed workbook in excel (formula)
Here is a picture of the closed workbook. There can´t be any blank cells or text values in your range. As you can see, there are five unique distinct values (1, 2, 3, 5 and 6) and one duplicate value […]

Extract unique distinct numbers from closed workbook in excel (formula)
Introduction In this post I will show you how to extract unique distinct numbers from a closed workbook. There can´t be any blank cells in the cell range. You can only extract numbers with this method. Unique distinct numbers are […]

List all the unique events for a month in excel (array formula)
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to row 1000. What I need to be able to do is look at today's date. Determine the month and year […]

Sort numeric values by closest to farthest to a specific number in excel (array formula)
Example 1 This example demonstrates how to sort values with sort on right click menu. Formula in C5: =ABS($C$2-B5) + ENTER copied down as far as needed. Sort selection B5:C17 by column C, order smallest to largest (Excel 2007) Select […]

How to quickly select a large non contiguous range with keyboard in excel
Here is a picture of a non contigous list in excel. The list has 3000 rows with occasional blanks. How do we quickly select the entire list? The first thing that comes to mind is selecting this list using CTRL […]

Highlight duplicates on same date, week or month using conditional formatting in excel
Highlight duplicates on same date Conditional formatting formula: =SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1 Highlight duplicates on same week Conditional formatting formula: =SUMPRODUCT(--($B16&"-"&YEAR($C16)&"-"&$D16=$B16:$B$16&"-"&YEAR($C16:$C$16)&"-"&$D16:$D$16))>1 Highlight duplicates on same month Conditional formatting formula: =SUMPRODUCT(--(YEAR($C29)&"-"&MONTH($C29)&"-"&$D29=YEAR($C29:$C$29)&"-"&MONTH($C29:$C$29)&"-"&$D29:$D$29))>1 Download excel sample file for this article. Highlight-duplicates-within-same-date-week-month-year.xls (Excel 97-2003 Workbook *.xls) Functions […]

Filter duplicates within same date, week or month in excel
Filter duplicates on same date Array formula in F3: =INDEX($C$3:$C$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed. Array formula in G3: =INDEX($D$3:$D$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, […]

Count unique distinct values within same week, month or year in excel
Introduction What is unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count unique distinct values within same week Formula in B3: =WEEKNUM(C3) + ENTER Array formula in E3: =SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0)) […]

How to count unique distinct occurrences for each date in excel
Question: How to count unique distinct values on the same date? Answer: Array formula in D3: =SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), (COUNTIF(Item, "<"&Item)))>0)) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed. Named ranges […]

Remove duplicates within same month or year in excel
Table of contents Remove duplicates within same month and year in excel Remove duplicates within same year in excel Remove duplicates within same month and year in excel Array formula in B15: =INDEX(Date, SMALL(IF(MATCH(YEAR(Date)&"-"&MONTH(Date)&"-"&Item, YEAR(Date)&"-"&MONTH(Date)&"-"&Item, 0)=ROW(Date)-MIN(ROW(Date))+1, ROW(Date)-MIN(ROW(Date))+1, ""), ROW(A1))) + […]

Remove duplicates in same week in excel
In a previous post we created a unique distinct list of dates and data removing any duplicates on same date. In this post we will remove duplicates within same week. Array formula in B15: =INDEX($B$4:$B$12, SMALL(IF(MATCH(YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, 0)=ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ""), […]

Remove duplicates on same date in excel
Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 : 1/1/2010 : 5000 A4 : 5/1/2010 : 5000 A5 : 6/1/2010 : 4000 From column B the values which […]

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
In this post we are going to extract multiple text values. We are looking for names and the criteria are two or more states (cell B18:B19) and two or more months (cell D18:D19). But first there is something we can […]

Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
I found a question in the comments section. You can find the question in this post: Lookup values in a range using two or more criteria and return multiple matches in excel Question: Unfortunately, I can't make enough sense of […]

Calculate your stock portfolio performance with Net Asset Value based on units in excel
In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying prices. Dividends, interest, deposits and withdrawals are not calculated in this post. In another previous related post we created a […]

Consolidate sheets in excel (vba)
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The columns I am interested in each workshets are "Date Plan", "Date Compelted" and "variance" and "Project Code" I then want […]

Lookup using two criteria in excel
Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 8.38 3.03 6.76 3.04 5.33 3.06 6.36 Lets say i have a cell with number 3. I need to find […]

Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am calculating net asset value (NAV). Net asset value is the value of each stock and the account balance summed, calculated […]

Calculate your stock portfolio performance in excel
Track your stock investments in excel and use a web query to import current stock prices from yahoo. Setup excel sheet Concatenate stock symbols Import current stock share prices using a web query Refresh web query to update stock prices […]

Pass multiple conditions dynamically in excel
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all these are single conditions -- you can't pass multiple conditions, say for example: USA or China or France in column_countries […]

Categorize values into multiple columns (excel formulas)
In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column (vba). In this post I´ll show you how to do the same using only excel formulas. Create unique distinct column […]

Categorize values into multiple columns using vba in excel
I am fairly new to vba and I am amazed of how much you can automate in excel. In this post I am going to categorize values from a list into unique columns.I am sure there are more efficent ways […]

Count unique distinct numbers across multiple sheets (3D range) in excel
Count unique distinct numbers in a 3D range Array formula in E8: =SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER Count unique numbers in a 3D range Array formula in E10: =SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)=1)) + CTRL + SHIFT + ENTER Count […]

Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel (See comments). I have no clue if that is true or not but my intention now is to develop that […]

Split data across multiple sheets in excel (vba)
In this post I am going to show how to create a new sheet for each airplane using vba. The macro copies airplane and model values into each new sheet. Before: After: The Code Download excel tutorial file Remember to […]

Combine data from multiple sheets in excel
Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each […]

Calendar with scheduling in excel 2007 (vba)
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both vba and formula. I will explain how I created this calendar in an upcoming post. You can download the excel […]

Dynamic stock chart in excel – Add date ranges
Table of contents Introduction Create a lookup table Create a drop down list Change web query parameter Add dynamic ranges to stock chart Introduction I have previously posted how to create a dynamic stock chart in excel. It is really […]

Create unique distinct year and months from a long date listing in excel
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the question in this post: Extract dates using a drop down list in excel Answer: Array formula in C2: =TEXT(INDEX($A$2:$A$135, MATCH(0, […]

Create a dynamic stock chart using a web query and a drop down list in excel
In this blog post I´ll show you how to update a stock chart in excel using a drop down menu and a web query connection. Create list of companies and corresponding tickers Create a dynamic range Create a drop down […]

Match two criteria and return multiple rows in excel
Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Create a unique distinct list of a long list without sacrificing performance using vba in excel
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product). e.g. […]

Scan stock markets in excel
You can make Excel scan stocks automatically. In this post I´ll show you how to: Automate Excel to download historical stock prices using a web query Scan historical stock prices to identify trends using latest 50 day average Iterate through […]

Extract negative values and adjacent cells in excel
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array formula in B23: =INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER copied down as far as […]

Stock alerts in excel
In this post I will show you how excel can monitor stocks prices. Table of contents Introduction Create stock quotes separated by comma (vba) Setup a web query to import stock prices from msn money Create stock alerts when a […]

Quickly select a range of values in excel
Copying or moving cells is probably one of the most common activities in excel. Here is how to quickly select cells and adjacent cells containing values. The selection ends when there are blank rows or columns. This is useful when […]

Create number sequences in excel 2007
Create number sequences (Autofill) Example 1 Type "1" in cell B3 + Enter Right click on black dot and drag down as far as needed. (See arrow) Click "Fill series" Example 2 Type "1" in cell A3 and "2" in […]

Excel formula to count cells between two values
Count cells between two values in a column Formula in D15: =ABS(MATCH(C12, A1:A10, 0)-MATCH(C13, A1:A10, 0))-1 Count cells between two values in a row Formula in D23: =ABS(MATCH(C20, A18:E18, 0)-MATCH(C21, A18:E18, 0))-1 Download excel sample file for this tutorial. formula […]

Return multiple values if above frequency criterion in excel
Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for more than 2 courses? Answer: Array formula in B25: =INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ROW(tbl)-MIN(ROW(tbl))+1, ""), 1), (SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1, […]

Filter unique distinct values using “contain” condition of a column in excel
Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced filter) Filter unique distinct values beginning with a letter Filter unique distinct values using "contain" condition of a column in […]

Return multiple values if in range in excel
Array formula in D12 (cell references): =INDEX($D$4:$D$8, SMALL(IF(($B$4:$B$8<$D$10)*($C$4:$C$8>$D$10), ROW($D$4:$D$8)-MIN(ROW($D$4:$D$8))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed. Array formula in D12 (named ranges): =INDEX(Value_col, SMALL(IF((Rng_1<Inp_val)*(Rng_2>Inp_val), ROW(Value_col)-MIN(ROW(Value_col))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER […]

Return value if in range in excel
Question: Hi, What type of formula could be used if you weren't using a date range and your data was not concatenated? ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 […]

Sort values in parallel (array formula)
Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to sort a table by Column 1 and then by Column 2 (Right-click / Sort /Custom sort...) 1. How to sort […]

Vlookup of three columns to pull a single record
Question: Does anyone know how to do a vlookup of three columns to pull a single record? Answer: I am guessing that you want to lookup a value in any of three columns to pull a single record. In this […]

Using Excel Solver to schedule employees
This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 processes checks 7 days a week. The number of workers needed each day to process checks is 17 workers are […]

Find positive and negative amounts that net to zero in excel
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, etc.) with related parties but seldom send payments. Once in a while, I would like to reduce the number of […]

Identify numbers in sum using solver in excel
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from the list A1:A11 in C1. How do I find those summed numbers in C1? I am going to use Excel […]

Sequencing and numbering of batches in excel
Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i get everyday. Now i just want under each head i.e. L1 & L2 the respective volume whether it's 4000 or […]

Sum adjacent values from a range using multiple lookup values in excel
In a previous blog post Sum adjacent values using multiple lookup text values in a column in excel I created a formula to sum specific values using two or more conditions in a column. In this blog post I will […]

Lookup values in a range using two or more criteria and return multiple matches in excel
In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using one criterion and return (if possible) multiple adjacent values. In this blog post I´ll […]

Sum adjacent values using multiple lookup text values in a column in excel
Here is a follow up to this previous post: Vlookup with 2 or more lookup criteria and return multiple matches in excel Sum search criteria price values Cell references in array formula in B14: =SUM(IF(COUNTIF(B10:B11, B3:B7)>0, C3:C7)) + CTRL + […]

Vlookup with 2 or more lookup criteria and return multiple matches in excel
In previous posts I provided formulas on how to lookup one value in a list and return multiple matches. Using array formula to look up multiple values in a list How to return multiple values using vlookup in excel Search […]

Filter duplicate values in a range using “contain” condition in excel
Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(SEARCH($D$9, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0)+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<=1), 0), 1) + CTRL […]

Filter unique text values in a range using “contain” condition in excel
Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)=1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(SEARCH($D$9, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0)+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<>1), 0), 1) + CTRL […]

Filter unique distinct text values in a range using “contain” condition in excel
Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(SEARCH($D$9, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0), 0), 1) + CTRL + SHIFT + ENTER copied down as far as needed. Named […]

Filter duplicate text values in a range using “begins with” criterion in excel
Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9)*(COUNTIF(tbl, tbl)>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(LEFT(INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9)*(COUNTIF(tbl, tbl)>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1), LEN($D$9))<>$D$9), 0), 1) + CTRL + SHIFT + ENTER copied down as far […]

Filter unique text values using “begins with” criterion in a range in excel
Unique values are values existing only once in a range. BB and CC exists once in the "List" above. They are unique values. Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)=1)*(COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9)*(COUNTIF(tbl, tbl)=1), […]

Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(LEFT(INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1)), , 1), LEN($D$9))<>$D$9), 0), 1) + CTRL + SHIFT + ENTER Copy cell B12 and paste it down […]

Formula for matching a date within a date range in excel
Table of contents Match a date when a date range is entered in a single cell Match a date when a date range is entered in two cells Use VLOOKUP to search date in date ranges and return value on […]

Count unique values in two lists combined in excel
Unique values Unique values are values existing only once in a list or range. See picture below. Count unique values in two lists combined Array formula in D12: =SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, 1))+SUM(IF(COUNTIF(List2, List2)+COUNTIF(List1, List2)=1, 1)) + CTRL + SHIFT + […]

Count matching values in one or more columns in excel
In previous articles, I have talked about how to count unique/unique distinct/duplicates in a column/columns/range. In this article I count matching values using criteria. Formula in B16: =SUMPRODUCT(--(A4=A8:A14)) + ENTER Formula in B32: =SUMPRODUCT(--(A20=A24:A30),--(B20=B24:B30)) + ENTER Formula in B51: =SUMPRODUCT(--(A37=A43:A49),--(B37=B43:B49), […]

Find records using two numerical criteria in excel
Filter records between 13 and 16 can also be done using Advanced filter or in Excel 2007 creating a table. Array formula in A19: =INDEX($A$2:$D$9, SMALL(IF(($D$2:$D$9<=$B$14)*($D$2:$D$9>=$B$13), ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1), ROW(1:1)), COLUMN(A1:A1)) + CTRL + SHIFT + ENTER copied right to D19 and […]

Lookup a value and find max date
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download excel file Lookup all values and find max date Lookup and find last date using multiple conditions Lookup and find […]

Filter unique distinct values where adjacent cells contain search string in excel
Question: How do I create a unique distinct list where adjacent cell values contains a search string? AA102 CA AA103 BC AA104 CA AD103 SD AA201 CC Search string: 1 Unique distinct list CA BC SD Answer: Filter unique distinct […]

Validate duplicates in excel
Validate "All duplicate values" list (D2:D7) Array formula in E13: =AND(COUNTIF(list, all_duplicates)=COUNTIF(all_duplicates, all_duplicates)) + CTRL + SHIFT + ENTER Validate "Values having duplicates" list (F3:F4) Array formula in E17: =AND(AND(COUNTIF(list, Duplicates)>1), AND(COUNTIF(Duplicates, Duplicates)=1)) + CTRL + SHIFT + ENTER Named […]

Validate unique distinct list in excel
I created three columns. The first column contains "Error values", values that are not unique distinct values. Missing values are values that are unique distinct but missing in the unique distinct list. Duplicate values are values that are unique distinct […]

Validate unique list in excel
Question: My list contains text values and I have filtered a new list with unique text values. I would like to know if my unique list is valid? Answer: Unique values are values occuring only once in a list or […]

Sum values in a range where adjacent cell value equals a criterion in excel
Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is in cell B19 (Pen). Array formula in C19: =SUM(IF(B4:F14=B19, C4:G14, 0)) + CTRL + SHIFT + ENTER Download excel file […]

Search for multiple text strings in multiple cells and use in data validation in excel
Question: can you please modify the function (f2:f11) to display the text strings in CELL G.. disregard the ROW NUMBER Display thnx the function will be in data validation.. and it will display 2 outputs in the list but if […]

Highlight odd/even months using conditional formatting in excel 2007
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: =MOD(YEAR($B6),2) Color even years Conditional formatting formula: =NOT(MOD(YEAR($B6),2)) Border between months Conditional formatting formula: =MONTH($F7)<>MONTH($F6) Border between years Conditional formatting […]

Lookup a value in a list and return multiple matches in excel
Question: Hi, The formula here works great but I can't figure out how to change it to work with data in columns. Here is what I have: =INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN())) A B C D E 1 A B A C D 2 […]

How to calculate missing months in a given date range in excel
Question: I have dates in a list. I would like to know how to identify missing months in this list and in a given date range? The given date range would, in this case, be the smallest date value and […]

Count date records between two dates in a range in excel
In a previous article Count records between two dates in excel I counted dates that were between two dates in a column. In this article I will provide an array formula that counts dates in a range using a start […]

Sum values between two dates with criteria in excel
In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two date criteria and an additional criterion in an adjacent column (Product). I have colored the cells in column Qty that […]

Most common value between two dates in a range in excel
Array formula in D17: =INDEX(C3:G12, MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1), 0))), (MAX(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1)+((COLUMN(C3:G12)-MIN(COLUMN(C3:G12))+1)/16384), 0))-MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1), 0))))*16384) + CTRL + SHIFT + ENTER Download excel file for this tutorial. Most common value […]

Most common value between two dates in excel
In this article I will show you how to extract the most frequent value (text or number) between two dates in a column. Array formula in C17: =INDEX(Values_col, MATCH(MAX(FREQUENCY(IF((Dates_col<=$B$15)*(Dates_col>=$B$14), COUNTIF(Values_col, "<"&Values_col), ""), COUNTIF(Values_col, "<"&Values_col))), FREQUENCY(IF((Dates_col<=$B$15)*(Dates_col>=$B$14), COUNTIF(Values_col, "<"&Values_col), ""), COUNTIF(Values_col, "<"&Values_col)), […]

« previous entries

Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.
Don´t forget to add my RSS feed and subscribe to new blog articles by email.
Sign up for free blog updates

Email
Search website
Popular articles

1. How to return multiple values using vlookup

2. How to extract a unique distinct list from a column

3. How to create a dynamic chart (excel 2003 and 2007)

4. Match two criteria and return multiple rows

5. How to create a list of random unique numbers

6. Create dependent drop down lists containing unique distinct values

7. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

8. Concatenate a cell range without vba

9. Auto refresh a pivot table

10. Excel udf: Lookup and return multiple values concatenated into one cell
10,292 subscribers

Archive for the ‘Excel’ Category

Subcategories