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

## Archive for the ‘Excel’ Category

### Subcategories

- Advanced filter
- Automate
- Charts
- Combine/Merge
- Compare
- Conditional formatting
- Count values
- Dates
- Drop down lists
- Duplicate values
- Finance
- Functions
- misc
- Overlapping
- Permutations
- Pivot table
- Random
- Records
- Regular expressions
- Search/Lookup
- Sort values
- table
- Templates
- Unique distinct values
- Unique values
- User defined functions (udf)
- vba
- Vlookup

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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)

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

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:

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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"), […]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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), ""), […]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, ""), […]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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