Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions. In the example shown, the formula in C11 is: = SUMPRODUCT (( LEN ( B5:B8 ) - LEN ( SUBSTITUTE ( B5:B8 , C2 , "...Read more
To rank a set of race times, where the lowest (fastest) time is ranked #1, you can use the RANK function. In the example shown, the formula in D6 is: = RANK ( C6 , times , 1 ) Where times is the named range C6:C13. How this formula works You can use the RANK function to rank numeric values. RANK...Read more
To get the nth MATCH with VLOOKUP, you'll need to add a helper column to your table that constructs a unique id that includes the count. If this isn't practical, you can use an array formula based on INDEX and MATCH instead. = VLOOKUP ( id & "-" & I6 , data , 4 , 0 ) How this...Read more
One way to extract multiple matches in Excel is to use INDEX and MATCH with a helper column that flags matching data. This avoids the complexity of a more advanced array formula. In the example shown, the formula in H6 is: = IF ( $G6 <= ct , INDEX ( data , MATCH ( $G6 , helper , 0 ), 1 ),...Read more
If want to extract the domain from an email address, you can do so with a formula that uses the RIGHT, LEN, and FIND functions. In the generic form above, email represents the email address you are working with. In the example, we are using this formula: = RIGHT ( C4 , LEN ( C4 ) - FIND ( "@...Read more
Videos
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows - groups of 3 rows, groups of 4 rows, and so on.
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.
In this video, we show you how to select one or more cells, including cells that are not next to one another.
In this video, we walk you through Excel's interface at a high level.
In this video, we'll use MATCH to find the position of an item in a table, and the INDEX function to retrieve the value at that position in a two-way lookup.
Blog
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.
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.
The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.
Excel contains over 450 functions. Where should you start? This guide is a walkthrough of over 100 important functions in Excel. Click function names for details and linked examples.
Feedback
Your pages offer clear writing and simple explanations that I'm sure are immensely helpful to many. Thanks for making the internet a better place. -David
It is only because of your website that I have the interest to learn how to use Excel. I can only say a huge burden was lifted off my head when I discovered your site. I used to hate Excel before. I found it complex and was disinterested too, but all that has changed now. I'm grateful. -RB
I've taken a couple of your courses and absolutely love your teaching format! -Barbara
We offer free resources and paid training.