Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
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
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
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
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
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.
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.
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.
In this lesson, we cover shortcuts you can use for the many features provided by Paste Special.
In this video, we look at how to configure a slicer to control more than one pivot table at once.
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
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
We offer free resources and paid training.