Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula above, rng represents a range of cells that contain words. In the example above, we are using: = SUMPRODUCT ( LEN...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
To randomly sort existing values with a formula, you can use an INDEX and MATCH formula together with helper columns as shown in the screenshot. In the example shown, the formula in E5 is: = INDEX ( names , MATCH ( ROWS ( $D$5:$D5 ), sort , 0 )) where "names" is the named range B5:B11, "rand" is...Read more
To calculate an estimated mortgage payment in Excel with a formula, you can use the PMT function. In the example shown, the formula in F4 is: = PMT ( C5 / 12 , C6 * 12 , - C9 ) When assumptions in column C are changed, the estimated payment will recalculate automatically. How this formula works The...Read more
To set a check register formula that calculates a running balance, you can use a formula based on simple addition and subtraction. In the example shown, the formula in G6 is: = G5 - E6 + F6 How this formula works The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts...Read more
Videos
In this video, I'm going to show you the basics of boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements.
In this video, we'll look at how you can use the CHOOSE function. We also compare a formula based on CHOOSE vs. a formula based on VLOOKUP.
In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function, including random dates, random prices, and random workdays.
In this video, we'll look at how to compare two lists using conditional formatting. This is a great way to visually highlight missing items in a list.
In this video, we'll look at how to copy and paste a pivot table without bringing along the source data.
Blog
Pivot tables are one of the most powerful and useful features in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.
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.
Feedback
I love to use excel and use it a lot in my work. Your tips, tricks and examples have helped me enormously. -Lisa
You have been such a great big help and want to thank you for all your training/teaching tools. -Gloria
I really like Exceljet and have already referred a few colleagues to it. Your shortcuts and tips are relevant and very easy to follow/learn. Thanks for providing such a great resource! -Theresa
We offer free resources and paid training.