Looking for more? Try chatting with the Exceljet Chatbot.

Video

Boolean algebra in Excel

In this video, we’ll look how Boolean algebra is used for AND and OR logic in formulas. In Boolean algebra, there are only two possible results for a math operation: 1 or 0.Read more
Video

What is an array formula?

In this video, we'll answer the question: What is an array formula? The term "array formula" is probably responsible for more confusion than just about any other concept in Excel...Read more
Video

How to see arrays in formulas

In this video, we'll look at a few ways to see or visualize arrays in a formula. One of the best things about the new dynamic array formula engine in Excel is that it's much easier to see and visualize arrays. ...Read more
Formula

Sum by fiscal year

The goal of this example is to sum amounts by fiscal year, when the fiscal year begins in July. The first approach is a self-contained formula based on the SUMPRODUCT function. The second method uses SUMIF with column D as a helper column. Either approach will work correctly, and the best option...Read more

Pivot Table

Pivot table remove deleted items

One of the quirks of pivot tables is that they may hold on to items that have been previously removed from the source data, even after refreshing the data. You may see these deleted "ghost" items when filtering a pivot table. In the example shown, the source data originally contained three...Read more

Video

Excel TEXTAFTER function

This video provides an overview of the TEXTAFTER function, which extracts text that occurs after a specific delimiter. The video works through three different examples, including a demo of a negative instance number.Read more
Video

Excel TEXTBEFORE function

This video provides an overview of the TEXTBEFORE function, which extracts text that occurs before a specific delimiter. The video works through three different examples, including a demo of a negative instance number.Read more
Video

TEXTAFTER with TEXTBEFORE

You can't always get what you want with the TEXTAFTER function or the TEXTBEFORE function alone. This video walks through two examples that explain how to combine the TEXTAFTER with TEXTBEFORE in a single formula. This might seem like a strange thing to do, but it's a handy trick when you...Read more
Video

TEXTAFTER and TEXTBEFORE advanced options

In this video, we look at how to configure TEXTAFTER and TEXTBEFORE to handle situations where the delimiter is not found. One option is to use the match_end argument to treat the end of the text like a delimiter. Another option is to use the if_not_found argument to supply a custom value. The...Read more
Video

Excel TEXTSPLIT function

In this video, we introduce the TEXTSPLIT function, which is designed to split text into separate cells using a given delimiter. The video shows how to use TEXTSPLIT in three different ways: (1) to split email addresses into two parts, (2) to split comma-separated text into five columns, and (3) to...Read more
Video

TEXTSPLIT with multiple delimiters

In this video, we look at two examples of configuring TEXTSPLIT with multiple delimiters. In the first example, we use three different delimiters to clean up some messy data. In the second example, we use multiple delimiters in an interesting and clever way. The first delimiter splits the text. The...Read more
Video

TEXTSPLIT with numbers

In this video, we'll take a look at how to handle numbers with the TEXTSPLIT function. TEXTSPLIT always returns text, and this can cause a problem if you need numeric values as a final result. This video explains how to use the TEXTSPLIT function together with the VALUE function and the...Read more
Article

Excel Tables

Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.Read more

Formula

XLOOKUP latest by date

XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date. If data were sorted by date in ascending order, this would be very straightforward....Read more

Formula

Max value ignore all errors

In this example, the goal is to return the maximum value in a set of data while ignoring any errors that might exist. This problem can be solved with the AGGREGATE function or with the MAXIFS function, as explained below.

MAX with errors

The standard way to retrieve the maximum...Read more

Function

MEDIAN Function

The Excel MEDIAN function returns the median (middle number) in the supplied set of data. For example, =MEDIAN(1,2,3,4,5) returns 3.Read more

Formula

IF with boolean logic

The goal is to sum the quantity for rows where the color is "Red", the region is "East", and the quantity is greater than 7. Although there are a number of ways to solve this problem in Excel purpose of this example is to demonstrate how to replace a nested IF...Read more

Formula

List sheet names with formula

In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:

  1. Define a name called "sheetnames" with an old
    2. ...Read more
Formula

Highlight bottom values

In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2.

This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make...Read more

Formula

nth largest without duplicates

In this example, the goal is to retrieve the largest 3 (top 3) values in the named range data, which appears in the range B6:B16. The standard solution to get "nth largest values" is the LARGE function. However, one potential problem with LARGE is that it will return duplicate...Read more

Formula

Minimum value if unique

The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data.

The UNIQUE function, new in Excel 365, will return a unique list of values from a set of data. By default, this is a list of...Read more

Pages