Newsletter
Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Formulas
Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month. This date goes into WORKDAY function as the "start date", along with -1 for "days". The WORKDAY function automatically steps back 1...Read more
TEXTJOIN is a new function in available in Office 365 and Excel 2019. Working from the inside out, the MID formula is used to extract the text in B5, one character at a time. The key is the ROW/INDIRECT piece: ROW ( INDIRECT ( "1:100" )) which spins up an array containing 100 numbers like...Read more
At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hardcoded as array constant: MID ( B5 , { 10 , 9 , 8 , 7 , 6 , 5 , 4 , 3 , 2 , 1 } , 1 ) The text argument comes B5,...Read more
The OFFSET function can be used to construct dynamic rectangular ranges based on a starting reference and given rows, columns, height, and width. The rows and columns arguments function like "offsets" from the starting reference. The height and width arguments (both optional), determine how many...Read more
This formula relies on the FILTER function to retrieve data based on a logical test created with a boolean logic expression. The array argument is provided as B5:D15, which contains the full set of data without headers. The include argument is based on two logical comparisons: ( C5:C15 >= F5...Read more
Videos
In this video, we'll show how to select visible cells only.
In this video, we'll take a look at a few reasons why you shouldn't hardcode values that may change into your formulas.
In this video, we show you how to create a basic chart in Excel.
In this video, I'm going to show you how you can use multiple Excel functions to split, manipulate, and rejoin values inside a single formula.
In this video, we'll look at how to calculate the nth smallest or largest values in a range using the SMALL and LARGE functions. This would be for example the 1st, 2nd, and 3rd...
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 love your excellent site and it is my goto for excel questions. -Dallas
Love this website! It was so useful! Thank you! And your explanations are simple. -Monica
I am just loving exceljet. Honestly THE best money I ever spent! What is so good about this resource is that it is in short, specific video clips, with both audio and text and with practice downloads for hands-on experience. I like the way you highlight what you are talking about in orange, and it isn’t too fast that the eye can’t see it before you move to the next thing! I love how you provide instructions for the absolute beginner right through to really advanced things. -Anne
We offer free resources and paid training.