Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Count cells that do not contain errors
SUMPRODUCT accepts one or more arrays and calculates the sum of products of corresponding numbers. If only one array is supplied, it just sums the items in the array. The ISERR function is evaluated for each cell in rng. Without the NOT function, the result is an array of values equal to TRUE or...Read more
Excel formula: Data validation only dates between
Data validation rules are triggered when a user adds or changes a cell value. The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because we want to allow...Read more
Excel formula: One or the other not both
In this example the XOR function contains two expressions, one to test for an "x" in column C, and one to test for an "x" in column D. C5 = "x" // TRUE if coffee is "x" D5 = "x" // TRUE if tea is "x" With two logical criteria, XOR has a particular behavior,...Read more
Excel formula: Sort and extract unique values
Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter . The example shown uses several formulas, which are described below. At a high level, the MMULT function is used to compute a numeric rank in a helper column (column C), and this rank...Read more
Excel formula: Square root of number
To get the square root of a number, you can use the SQRT function. In the example shown, the formula in C5 is: = SQRT ( B5 ) How this function works The SQRT function is fully automatic and will return the square root of any positive number. If for some reason you need to get the square root of a...Read more

Videos

In this video, we'll show how to select visible cells only.
Run time: 2:33
In this video, I'm going to show you the basics of boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements.
Run time: 3:33
In this lesson, we cover shortcuts you can use when working with Excel Tables.
Run time: 3:17
In this video, we'll look at shortcuts that let you work with the Ribbon using only your keyboard.
Run time: 3:02
By their nature, nested IF formulas are hard to read. In this video, we show you how to easily make a nested IF formula more readable, by adding line breaks.
Run time: 3:06

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 fantastic! I've looked at dozens of Excel tutorial sites. None compare favorably. Thank you for your assistance. -Marco
Thanks Dave for helping us so many times. I absolutely love your method of explaining Excel formulas and tips. -Mahendra
Let me tell you that I am uber happy to have found your classes on the internet – they combine huge knowledge with a great talent to explain and teach! I watched some of your free lessons before I invested money, and from what I have seen I can say that it's worth every penny! -Carolin