Looking for more? Try chatting with the Exceljet Chatbot.
The Excel TOCOL function transforms an array into a single column. By default, TOCOL will scan values by row, but TOCOL can also scan values by column.Read more
The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or a syntax problem. The examples below...Read more
Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will automatically return the first day of the current month.
In this example, the goal is to generate a dynamic calendar for any given month, based on a start date...Read more
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function expecting a number receives text value, or when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of...Read more
In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be included in the final...Read more
In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below.
Note: Both...Read more
In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. However, the result needs to ignore any zeros that appear in the data. This formula can be easily solved with the AVERAGEIF function...Read more
The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the ...Read more
Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and repeated on an on-going basis. However, you can also use the...Read more
The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem. The...Read more
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead.
To pull a random value out of a list or table, we'll need a random row number. For that, we'll use the RANDBETWEEN...Read more
A dynamic named range, also called simply a "dynamic range" is a specially constructed range that expands automatically to accommodate new data. In the example shown, we have a small set of data in B5:B13, and two formulas calculating a minimum and maximum...Read more
In this example, the goal is to average the last n columns in a set of data, where n is a variable entered in cell K5 that can be changed at any time. Since more data may be added, a key requirement is to average amounts by position....Read more
Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this:
=INDIRECT($B$5&"!"&"A1")
If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet....Read more
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.Read more
The RANDBETWEEN function takes two numbers, a bottom and top number, and generates a random integer in between. Dates in Excel are serial numbers, so you can use the DATE function to create the lower number and the upper number. RANDBETWEEN then generates a number that falls between these two...Read more
Concatenation refers to the operation of joining values together to create a text string. In Excel, you can concatenate with the concatenation operator, which is an ampersand (&) or by using one of several functions for concatenation.
In the screen above, the formulas used for...Read more
Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more
In this example, the goal is to extract the numbers from a set of property listings which describe the number of bedrooms and bathrooms, the size of the house in sq. ft., and the size of the lot in acres. Traditionally, this kind of problem has been quite difficult in Excel because each number...Read more
The Excel MINIFS function returns the smallest numeric value in cells that meet multiple conditions, referred to as criteria. To define criteria, MINIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can apply conditions to cells that contain dates, numbers,...Read more
The #N/A error appears when something can't be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that doesn't exist, etc.
However...Read more
Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter.
The example shown uses several...Read more
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...Read more
In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = "A" and (2) the Score is greater than 80. At first glance, it's not obvious how to do this with the FILTER function. Unlike older...Read more
In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to...Read more
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.
The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array...Read more
In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e., not empty). A good way to solve this problem is to use the SUMIFS function. However, you can also use the ...Read more
At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.
When given a date,...Read more