Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Lookup number plus or minus N
In this example, the goal is to lookup a number with a certain amount of allowed tolerance, defined as n . In other words, with a given lookup number we are trying to find a number in a set of data that is ± n . In the worksheet shown, the number to find is in cell G4 and the number used for n is...Read more
Excel formula: Filter on dates expiring soon
In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was replaced. The...Read more
Excel formula: Strip non-numeric characters
In this example, the goal is to remove non-numeric characters from a text string with a formula. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here: ROW (...Read more
Excel formula: Minimum value
The MIN function accepts one or more arguments , which can be a mix of constants, cell references, and ranges. The MIN function returns the maximum value in data provided. Text values and empty cells are ignored. In this example, each student has five test scores in the same row, and the goal is to...Read more
Excel formula: Highlight cells that end with
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12, and the reference to B4 will change to the address of each cell being evaluated...Read more

Videos

In this video, we introduce you to the pivot table and explain why pivot tables are useful.
Run time: 2:44
In this video, we'll build a dropdown list using dynamic arrays to filter data by color. The dropdown list is created with data validation.
Run time: 2:55
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula.
Run time: 2:24
In this video, we’ll set up the XLOOKUP function to perform an approximate match in order to calculate a quantity based discount.
Run time: 3:22
In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list.
Run time: 2:53

Blog

In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated Excel problems. In the current version of Excel, you can use the SUM instead, but SUMPRODUCT is better for backwards compatibility.

How to concatenate in Excel

Concatenation is one of the most important operations in more advanced formulas. This article explains how to perform concatenation manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.

What is an array formula?

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

Excel's RACON functions

There are eight functions in Excel that work differently than you might realize. The same features that were supposed to make them easier to use, can actually make them harder to use :) Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS,  AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.

This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.

Feedback

I am new to your website, but must say that I find your material most informative. -Chris
I just wanted to let you guys know that I think your site is incredible. You've helped me learn so many useful techniques that I previously struggled with. Your thorough articles are extremely valuable! -Tyler
Just wanted to say that after searching google for 20 minutes, your formula for difference between two dates and times was the one that finally worked. so, THANK YOU!! -Edan