Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
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
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
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
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
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
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.
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...
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...
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...
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.
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.
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.
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.