Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Data validation must contain specific text
To allow only values that contain a specific text string, you can use data validation with a custom formula based on the FIND and ISNUMBER functions. In the example shown, the data validation applied to C5:C9 is: = ISNUMBER ( FIND ( "XST" , C5 )) How this formula works Data validation...Read more
Excel formula: Find longest string in column
To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is: { = INDEX ( names , MATCH ( MAX ( LEN ( names )), LEN ( names ), 0 )) } Where "names" is the named range C5:C14...Read more
Excel formula: Count specific characters in a range
If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain words and txt represents the character you need to...Read more
Excel formula: Get fiscal year from date
To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. In the example shown, the formula in D5 is: = YEAR ( B5 ) + ( MONTH ( B5 ) >= C5 ) How this formula works By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year...Read more
Excel formula: Summary count with COUNTIF
When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by category, color, size, status, etc. The COUNTIF function is a good way to generate these kinds of totals. If you have a limited number of values to count...Read more

Videos

In this video, we show you how to select one or more cells, including cells that are not next to one another.
Run time: 2:21
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 dynamic array behavior is native and deeply integrated in Excel. Even older functions can use this new feature.
Run time: 2:52
In this video, we show you how and why to use a table as a data source for your pivot table.
Run time: 3:14
In this video, we'll look at how to build a formula that calculates a 401k match using several nested IF statements.
Run time: 3:17

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

I've been learning from exceljet.com for a while now and I think it's about time I contributed to your cause. Thank you very much. Your excel knowledge helped me in my job many times in the past. When I saw your index/Match today I realized how much I still need to learn. I am a Mechanical engineer. I will be using your file in my work. I've shared it with the other engineers here. -Sam
I would highly recommend that anyone seeking Excel advice turn to this resource as their first stop. You can spend hours on Google trying to find a way to do something and get nowhere, at Exceljet I tend to find the answer very quickly and it is easily implemented. -Tony
This site just saved my life today while importing data to a new database. THANK YOU!!! -Mary