## 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.