Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To count matches in entire workbook, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in D5 is: = SUMPRODUCT ( COUNTIF ( INDIRECT ( "'" & sheets & "'!A1:Z10000" ), B5 )) where "sheets" is the named range B8:B10...Read more
To flip a table in Excel from rows to columns (i.e. to change orientation from vertical to horizontal) you can use the TRANSPOSE function. In the example shown the formula in E5:K6 is: { = TRANSPOSE ( B5:C11 ) } Note: this is a multi-cell array formula and must be entered with Control + Shift +...Read more
To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in H6 is: { = MAX ( IF ( names = H5 , ROW ( names ) - MIN ( ROW ( names )) + 1 )) } Where "names" is...Read more
To add a line break with a formula, you can use the concatenation operator (&) along with the CHAR function . In the example shown the formula in E4 is: = B4 & CHAR ( 10 ) & C4 & CHAR ( 10 ) & D4 How this formula works This formula "glues together" the pieces of text that appear in...Read more
To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions. In the example shown, the formula in C5 is: = LEFT ( B5 , FIND ( "." , B5 ) - 1 ) How this formula works The core of this formula is the LEFT function which simply extracts text from...Read more
Videos
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...
In this video, I'm going to show you how you can use multiple Excel functions to split, manipulate, and rejoin values inside a single formula.
In this video, we walk you through Excel's interface at a high level.
In this brief video, we look at how to debug a formula using the F9 key. If you work with formulas a lot, this is one of the most useful skills you can learn.
By their nature, nested IF formulas are hard to read. In this video, we show you how to easily make a nested IF formula more readable, by adding line breaks.
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
By the way I think your site is an awesome reference tool for excel, especially the section with formula examples. I have found that to be incredibly helpful. -Brett
I regularly use excel a lot in my work. Your tricks & shortcuts have helped me enormously. -Biplab
I really appreciate your site and your helpfulness. I think you, your videos and your site are very valuable tools. -Jason
We offer free resources and paid training.