Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Value exists in a range
To determine if a value exists in a range of cells, you can use a simple formula based on the COUNTIF function. In the example shown, the formula in D5 is: = COUNTIF ( rng , B5 ) > 0 where "rng" is the named range F4:F10. How this formula works The COUNTIF function counts cells that meet...Read more
Excel formula: Calculate original loan amount
To calculate the original loan amount, given the loan term, the interest rate, and a periodic payment amount, you can use the PV function. In the example shown, the formula in C10 is... = PV ( C5 / 12 , C7 , C6 ) How this formula works Loans have four primary components: the amount, the interest...Read more
Excel formula: Extract unique items from a list
To extract only unique values from a list or column, you can use an array formula based on INDEX, MATCH, and COUNTIF. In the example shown, the formula in D5 is: { = INDEX ( list , MATCH ( 0 , COUNTIF ( $D$4:D4 , list ), 0 )) } where "list" is the named range B5:B11. Note: this is an array formula...Read more
Excel formula: Random value from list or table
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. In the example shown, the formula in G7 is: = INDEX ( data , RANDBETWEEN ( 1 , ROWS ( data )), 1 ) How this formula works Note: this formula uses the named range "...Read more
Excel formula: COUNTIFS with variable table column
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. In the example shown, the formula in H5 is: = COUNTIFS ( INDEX ( Table1 , 0 , MATCH ( G5 , Table1 [ #Headers ], 0 )), "x" ) How this formula works First, for context, it...Read more

Videos

Tip: How to check and debug a formula with F9
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:10
Tip: How to highlight approximate match lookups
In this video we show how to highlight approximate match lookups with conditional formatting and the LOOKUP function. Highlighting approximate matches is tricky, because you must replicate...
Run time: 3:55
Tip: Perfect conditional formatting with dummy formulas
Conditional formatting with formulas can be tricky because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave...
Run time: 3:28
Tip: How to use VLOOKUP instead of nested IFs
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.You can change...
Run time: 3:57
Tip: How to build all-in-one formulas
This video shows you how to build a more complex formula using helper formulas, then bring them all together in a single, all-in-one formula at the end.
Run time: 3:45

Blog

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.
Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.

Feedback

Thanks again, Dave. It works! YOU ROCK!!! :) -Sandy
My Lord where have you been all my life?...I've learned more awesome tricks/functions in one afternoon from you than in the last 10 years. -Robert
You guys are amazing. I am a gradstudent -- I have a weird data set - this has helped! -Bryant
We offer free resources and paid training.