Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Count if row meets internal criteria
SUMPRODUCT is designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. As a result, you can use SUMPRODUCT to process arrays that result from criteria being applied to a range of cells. The result of such operations will be arrays,...Read more
Excel formula: INDEX and MATCH descending order
This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this: MATCH ( F4 , B5:B9 , - 1 ) Using the lookup value in cell F4, MATCH finds the first value in B5:B9 that is greater than or equal to the lookup value...Read more
Excel formula: FILTER with multiple OR criteria
In this example, criteria are entered in the range F5:H6. The logic of the formula is: item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle) The filtering logic of this formula (the include argument) is applied with the ISNUMBER and MATCH functions, together with...Read more
Excel formula: Maximum if multiple criteria
This example uses the following named ranges : "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. The goal is to find the maximum price for a given color and item. This formula uses two nested IF functions, wrapped inside MAX to return the maximum price with two criteria. Starting with a...Read more
Excel formula: Conditional message with REPT function
This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns "low". If not, the formula returns an empty string (""). Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated...Read more

Videos

In this video, we'll look at how to create a list of random dates with the RANDARRAY function.
Run time: 2:53
In this video, we demo how to join different values together in one cell with line breaks. Also includes a clever way to add spaces and commas without using double quotes.
Run time: 3:15
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes. VLOOKUP is a key Excel function.
Run time: 3:10
In this video, we'll look at how to plot average daylight hours per month in a chart, using sunrise and sunset data.
Run time: 3:49
We'll show you a cool way to delete rows that are missing values in one step, even when your list contains hundreds or thousands of rows.
Run time: 3:46

Blog

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 :)
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.
Tracking COVID-19 with Excel
A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.
Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.

Feedback

Your website is an absolute gem! I recently had to learn Excel after using statistical software. I even recruited my husband to help me find some ADHD-friendly Excel tutorials and all we found was a bunch of fluff. I stumbled on exceljet and after one tutorial was sold; for the first time I didn't feel more annoyed than informed at the end of a tutorial. Everything is logically organized on here and the instructions are thorough without any bull. -Ruby
I appreciate all the help your website provides, it has definitely been essential to me in the last couple of months. -Myuran
I have been considered a spreadsheet expert for many years, but I believe my total Excel knowledge is likely less than 30% of what I've seen in your weekly e-mails and website...In today's fast-paced office, when I need to know "how", ExcelJet is absolutely the best resource I've found for keeping my skills up-to-date without spending much time searching for answers. Thank you!!! -Suzanne