Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Extract last two words from cell
To extract the last two words from a cell, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is: = MID ( B5 , FIND ( "@" , SUBSTITUTE ( B5 , " " , "@" , LEN ( B5 ) - LEN (...Read more
Excel formula: Count values out of tolerance
To count values that are out of tolerance in a set of data, you can use a formula based on the SUMPRODUCT and ABS functions. In the example shown, the formula in F6 is: = SUMPRODUCT ( -- ( ABS ( data - target ) > tolerance )) where "data" is the named range B5:B14, "target" is the named range F4...Read more
Excel formula: Highlight column differences
If you want to highlight the differences between two columns of data with conditional formatting you can do so with a simple formula that uses the" not equal to" operator (e.g. ) and mixed references. For example, if you have similar data in B2:B11 and C2:C11, and you want to highlight cells where...Read more
Excel formula: If cell equals
If you want to do something specific when a cell equals a certain value, you can use the IF function to test the value, then do something if the result is TRUE, and (optionally) do something else if the result of the test is FALSE. If color is red, mark with "x" In the example shown, we simply want...Read more
Excel formula: Convert text to numbers
To convert simple text values to numbers, you can use the the VALUE function, or simply add zero as described below. In the example shown, the formula in C5 is: = VALUE ( B5 ) Background Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might...Read more

Videos

Tip: How to use VLOOKUP
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes.
Run time: 3:10
Tip: How to drive the ribbon with your keyboard
Why is the Excel ribbon cool? Because it gives you a way to use nearly every feature in Excel without touching the mouse. Watch this video to learn how.
Run time: 3:02
Tip: Is the format painter worth your time? Yes.
A quick video overview of the Format Painter, which transfers formatting to one group of cells to another with a single click.
Run time: 3:13
Tip: How to generate random values
In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function. You can use this approach to generate random numbers, random prices, random dates, and...
Run time: 3:39
Tip: How to chart generations with floating bars
In this video, we show how to plot American generations in a floating horizontal bar chart. This seems like a simple problem, but it requires a formula, clever formatting, and a special...
Run time: 3:37

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

Feedback

I'm an intermediate-level Excel user - I only know as much as I need to get the job done. As I imagine is the case for most people, I learned Excel by asking myself questions like, "I wonder if there's a way to do [X]?" and then Googling the answer. Your site has already taught me so much in the short time I discovered it and, as a Learning & Development professional, I admire your approach. Great work, and thanks for supporting all us Excel geeks! -Will
Thanks for the resources you provide. I've bumped into it as an avid Excel user (not at your level though) and have immediately seen the value - wow. -Antonie
Kudos to you for your very helpful site. I have gotten into the habit of Googling to solve problems I am having with Excel, and your site invariably is the one that provides the clearest explanations. -Jeff
We offer free resources and paid training.