Dave Bruns

In this example, we have a table of employee locations like this on Sheet2:

...Read more

First, a little context. Normally, if you have just a couple things you don't want to count, you can use COUNTIFS like this:

=COUNTIFS(range,"<>apple",range,"<>orange")

But this doesn't...Read more

The REPT function simply repeats values. For example, this formula outputs 10 asterisks:

=REPT("*",10) // outputs **********

You can use REPT to repeat any character(s) you like. In this example, we use the CHAR function to output a...Read more

In this example, the goal is to perform a case-sensitive lookup on the name in column B, based on a lookup value entered in cell E5. By default, Excel is not case-sensitive. This means that standard lookup functions like VLOOKUP,...Read more

In this example, the goal is to categorize various expenses using the categories shown in column F and the keywords shown in column E. This is a case where it seems like we should perform a lookup operation of some kind, but the problem is that the keywords appear embedded in the text and the...Read more

In this example the goal is to count numbers longer than 15 digits with a formula. The COUNTIF function may seem like this logical choice. However, if you try to count very long numbers (16+ digits) in a range with the COUNTIF function, you may see...Read more

One of the key skills you need to be good with Excel formulas is concatenation. Put simply, concatenation is just a fancy name for joining text together. In Excel formulas, the primary operator for concatenation is the ampersand (&). A good example of a simple concatenation task is...Read more

In this example, the goal is to calculate the "original" value when the current value and percentage change are known. For example, if we know the current value is 1100, after an increase of 10%, we want to calculate the original value (1000) with an Excel formula. Although the language may...Read more

Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more