Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Name of nth largest value with criteria

Name of nth largest value with criteria

The LARGE function is an easy way to get the nth largest value in a range: = LARGE ( range , 1 ) // 1st largest = LARGE ( range , 2 ) // 2nd largest = LARGE ( range , 3 ) // 3rd largest In this example, we can use the LARGE function to get a highest score, then use the score like a "key" to...Read more
Excel formula: Average top 3 scores

Average top 3 scores

The LARGE function is designed to retrieve the top nth value from a set of numbers. So, for example LARGE(A1:A10,1) will return highest value, LARGE(A1:A10,2) will return the 2nd highest value, and so on: LARGE ( range , 1 ) // 1st largest value LARGE ( range , 2 ) // 2nd largest value LARGE (...Read more
Excel formula: Sum top n values

Sum top n values

In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula: = LARGE ( B4:B13 , 2 ) will return the 2nd largest value in the range B4:B13 which, in the example above, is the number 9. However, if you supply an "array constant" (e.g. a constant in the form {1...Read more
Excel formula: Total rows in range

Total rows in range

The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10: = ROWS ( B5:C10 ) // count rows ROWS counts the number of rows in any supplied range...Read more
Excel formula: VLOOKUP if blank return blank

VLOOKUP if blank return blank

To test the result of VLOOKUP directly, we use the IF function like this: = IF ( VLOOKUP ( E5 , data , 2 , 0 ) = "" , "" Translated: if the result from VLOOKUP is an empty string (""), return an empty string. If the result from VLOOKUP is not an empty string, run VLOOKUP again...Read more
more formulas

Videos

How to perform a random sort

In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
Run time: 2:31

The IF function

In this video we'll look at how to use the IF function, one of the most powerful and popular functions in Excel.
Run time: 3:09

How to test conditional formatting with dummy formula

In this video, I'll show you how to quickly test your conditional formatting rules with dummy formulas.
Run time: 3:28

Dynamic min and max data labels

In this video, we'll look at how to dynamically highlight high and low values in an Excel chart using data labels.
Run time: 3:14

How to fill in missing data with a simple formula

In this video we'll look at a way to quickly add missing data to a worksheet. It's a simple technique using a very basic formula and a cool example of relative cell references.
Run time: 3:19
more videos

Blog

What is an array formula?

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

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.

Download Coronavirus data to Excel

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

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.

Alternatives to Dynamic Array Functions

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.

more articles

Feedback

Just wanted to drop in and say thank you! You've helped me solve SO MANY PROBLEMS over the years. Today, I learned the difference between Excel's FIND and SEARCH function and was able to write a formula that must have saved me hours of manual work. -Amanda
I'm a retired corporate guy who used to use Excel regularly, long before we had an internet and sources like your website. Now I use the program infrequently, and only to do special projects. In that work I often need to do things that are far beyond my skill level. After trying a variety of assistance websites, I found yours to be the only one that is consistently "quick, clean, and to the point". Your work is a model of excellence... -Dave
I really enjoy your site. I'm fairly proficient with excel, but I always use your site as a resource for those formulas that I don't use on a regular basis. Keep it up! -Ryan
more feedback
We offer free resources and paid training.