Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Last row in mixed data with no blanks
To get the last relative position (i.e. last row, last column) for mixed data that contains no empty cells, you can use the COUNTA function. In the example shown, the formula in D5 is: = COUNTA ( B4:B100 ) Last *relative* position When constructing more advanced formulas, it's often necessary to...Read more
Excel formula: Count specific characters in a range
If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain words and txt represents the character you need to...Read more
Excel formula: Add decimal minutes to time
To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. In the example shown, the formula in D5 is: = B5 + ( C5 / 1440 ) Note: make sure results are formatted as time. How this formula works Times in Excel are factional values of 24 hours. One hour...Read more
Excel formula: Data validation allow weekday only
Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility. To allow a user to enter only dates that are weekdays (i.e. Monday, Tuesday, Wednesday, etc.) you can use...Read more
Excel formula: Extract text between parentheses
To extract text between parentheses, braces, brackets, etc. you can use a formula based on the MID function, with help from SEARCH function. In the example shown, the formula in C5 is: = MID ( B5 , SEARCH ( "(" , B5 ) + 1 , SEARCH ( ")" , B5 ) - SEARCH ( "(" , B5 ) - 1...Read more

Videos

Once you have one pivot table set up, you might want to see a different view of the same data. In this video, I'll show you how to base one pivot table on another.
Run time: 3:11
In this short video, we look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements, VLOOKUP is simpler and more transparent. It's also...
Run time: 3:55
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
Run time: 2:47
Some Excel formulas are complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
Run time: 3:07
In this video, we look at how to use VLOOKUP to lookup values in an Excel Table.
Run time: 3:46

Blog

Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.
Pivot tables are one of the most powerful and useful features in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.
Formula errors are useful, because they tell you clearly that something is wrong. This guide shows examples of each of the 9 formula errors you may run into, with an information on how to investigate and correct the error.
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.
Excel Formula and Functions
Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel.

Feedback

I find your site a fantastic resource. I have dramatically improved my Excel skills simply by looking at your emails and videos! -Stuart
Consistently one of the best and clearest resources out there for well-explained Excel tips. -Russ
I just wanted to tell you how very much I enjoy these emails! I’ve learned SOO much from you, that now I know more than the Accounting Department’s resident Excel ‘expert’ LOL! -TS