Looking for more? Try chatting with the Exceljet Chatbot.

Puzzle

Formula challenge - list names by teams

In the worksheet shown, the goal is to list the names associated with each team, based on the data in columns B and C. The result should be a list of team members separated by commas. The solution should be dynamic. If the data changes, the result should update accordingly....Read more

Function

VLOOKUP Function

The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number. VLOOKUP supports approximate and exact...Read more

Video

Formulas to query a table

Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table...Read more
Function

XMATCH Function

The Excel XMATCH function performs a lookup and returns a position of a value in a range. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. ...Read more

Function

AVERAGEIFS Function

The Excel AVERAGEIFS function returns the average of cells that meet multiple conditions, referred to as criteria. To define criteria, AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.Read more

Function

SUBTOTAL Function

The Excel SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see complete list below), and SUBTOTAL function can either include or exclude values in hidden...Read more

Function

SORTBY Function

The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to be in the source data or in the output.Read more

Video

Excel Table Options

In this video, we'll look at the options available for Excel Tables, including automatic range expansion and a setting to automatically fill formulas...Read more
Function

NOT Function

The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.Read more

Formula

Count cells that are blank

In this example, the goal is to count cells in a range that are blank. Counting blank cells in Excel can be tricky because cells can look blank even when they are not actually empty. The article below explains three different approaches.

...Read more

Formula

Count unique text values with criteria

This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in the data:

MATCH(B5:B11,B5:B11,0)...Read more
Formula

Split text string to character array

In this example, the goal is to use a formula to split a text string into an array of characters. For example, if the text string is "Apple", the resulting array should be {"A","p","p","l","e"}. For a long time, this was quite a difficult problem that...Read more

Formula

Sum formulas only

In this example, the goal is to calculate a sum of the values in a range that are generated with a formula. In other words, we want to sum values in a range while ignoring the values that have been entered manually. In the context of this example, the hardcoded values in C5:C12 represent ...Read more

Course

Core Charts

A picture is worth a thousand words. Nothing beats an effective chart for presenting data in a way people can instantly understand. Excel has a powerful and deep charting engine built right in, so you already have the tools you need to create a wide range of charts and visualizations. This...Read more

Pivot Table

Pivot table month over month

In the example shown, a pivot table is used to show the month-over-month variance in sales for each month of a given year.  The variance is displayed both as an absolute value and also as a percentage. The year is selected by using a global filter.

Source data

The source...Read more

Formula

Rank values by month

This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.

Note there is no actual rank in the source data. Instead, we are using the LARGE function to...Read more

Function

FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.Read more

Function

SUM Function

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.Read more

Pages