Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Count cells not equal to
To count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells, and X represents the value you don't want to count. All other values will be counted. In the example, the...Read more
Excel formula: How to fix the #DIV/0! error
About the #DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors when data is being entered...Read more
Excel formula: Find closest match
To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is: { = INDEX ( data , MATCH ( MIN ( ABS ( data - E4 )), ABS ( data - E4 ), 0 )) } where "data" is the named range B5:...Read more
Excel formula: Highlight cells that equal
Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that contain a specific value. However, if you want more flexibility, you can use your own formula, as explained in this article. If you want to highlight cells...Read more
Excel formula: Lookup with variable sheet name
To create a lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function. In the example shown, the formula in C5 is: = VLOOKUP ( $B5 , INDIRECT ( "'" & C$4 & "'!" & "B5:C11" ), 2 , 0 ) How this formula works The "...Read more

Videos

In this video, we look at how to configure VLOOKUP to look up values based on an approximate match. This is good for tax rates, postage, commissions, and the like.
Run time: 2:53
In this video, I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions in a single formula.
Run time: 3:15
In this video, we'll look at how to simplify some formulas we created in a previous video, by replacing IF statements with the MIN function and a bit of boolean logic.
Run time: 3:43
In this lesson, we cover shortcuts you can use for the many features provided by Paste Special.
Run time: 4:05
In this video, we look at how to configure a slicer to control more than one pivot table at once.
Run time: 2:30

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

I found your video highly impressive and clear to understand for a foreigner as well! -Fazal
Just a short note to thank you for all of the free info you’ve put out there. Once again, I was stuck on something and just looked it up in your library of lessons. I was able to figure out the problem immediately. -Jeff
Thank you for saving me so much time! -Jenny