Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
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
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
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
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
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
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes.
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.
A quick video overview of the Format Painter, which transfers formatting to one group of cells to another with a single click.
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...
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...
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.
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.
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