Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Build hyperlink with VLOOKUP
The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name . Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range "link_table" (B5:C8). The lookup value comes...Read more
Excel formula: Unique values ignore blanks
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data: FILTER ( B5:B16 , B5:B16 <> "" ) The symbol is a logical operator that means "does not equal". For more...Read more
Excel formula: Get project start date
This formula is uses the WORKDAY function, which returns a date in the future or past, based on start date and required work days. WORKDAY automatically excludes weekends, and can also exclude holidays if provided as a range of dates. In the example shown, the project end date is in column C, and...Read more
Excel formula: Last row in text data
This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults to 1. The lookup value is a so-called "big text" (sometimes abbreviated "bigtext...Read more
Excel formula: 3D sum multiple worksheets
The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example Sheet1:Sheet3 ! A1 Means: cell A1 from Sheet1 to Sheet3. In the example shown: = SUM ( Week1:Week5 ! D6 ) Will sum cell D6 from Week1 to Week5, equivalent to: = SUM...Read more

Videos

In this video we look at how to simulate a random drawing to pick winners in a contest with Excel using the RAND and RANK functions together.
Run time: 3:07
In this video you'll learn how to use multiple Excel functions to split, manipulate, and rejoin values inside a single formula.
Run time: 2:45
Each character you see displayed in Excel has a number. In this video we look at two functions that work with these numbers directly: CODE and CHAR.
Run time: 3:04
In this video I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions in a single formula.
Run time: 3:15
In this video, we'll look at how to list duplicate values with the FILTER function. In other words, values that appear more than once in a set of data.
Run time: 2:52

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

Thank you for creating this website. Outstanding! -Stefan
Exceljet.net is a really good resource and has helped me numerous times move from a beginner Excel user to a high end intermediate user. -Isaac
Your site, by far, is the most helpful site I have ever been on for Excel. -Bill