Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Count occurrences in entire workbook
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
Excel formula: Flip table rows to columns
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
Excel formula: Get last match
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
Excel formula: Add a line break with a formula
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
Excel formula: Remove file extension from filename
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...
Run time: 3:55
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.
Run time: 2:45
In this video, we walk you through Excel's interface at a high level.
Run time: 2:34
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.
Run time: 3:12
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.
Run time: 3:06

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.
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.
The double negative in Excel formulas
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.
101 Excel Functions
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.