Newsletter
Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Formulas
Working from the inside out, the core of this formula is the inner MATCH expression: ISNA ( MATCH ( complete , $D$5:D11 , 0 ) Here, the MATCH function is used to compare all "complete" values against the partial list. The named range "complete" is used for lookup values, and the partial list is...Read more
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use absolute references for both of these ranges in the formula. This formula is based on the LARGE function , which returns the nth...Read more
The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess. Working from the inside out, the original text in B5 is flooded with spaces using SUBSTITUTE:...Read more
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of...Read more
By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, all conditions must match in order to generate a count. One solution is to supply multiple criteria in an array constant like this: { "complete" , "pending" } This will cause COUNTIFS to...Read more
Videos
In this video, we'll look at how to plot average daylight hours per month in a chart, using sunrise and sunset data.
In this video we'll look how to use the XLOOKUP function with Boolean logic to apply multiple criteria.
In this video, we'll look at how to create an Excel Table from source data on a worksheet.
In this video, we'll look at how to use the FILTER function together with the UNIQUE function to limit results using logical criteria.
In this lesson, we demonstrate a shortcut recipe you can use to remove blank rows quickly.
Blog
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.
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.
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
Just wanted to say thank you for the many clear, concise, and informative articles. I've gone from complete novice to regularly using array formulas thanks almost entirely to your site. -Scott
Looking good so far. I love the concise and to-the-point nature of the videos. -Mario
Hi! It's not a question. I just wanted to thank you for this awesome website! It has a lot of useful formulas that I didn't even know existed (arrays, etc.), and that have helped me a lot! -Alejandro
We offer free resources and paid training.