Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
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
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
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
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
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.
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.
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.
In this video, we show you how and why to use a table as a data source for your pivot table.
In this video, we'll look at how to build a formula that calculates a 401k match using several nested IF statements.
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
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
We offer free resources and paid training.