Explanation In this example, the goal is to apply AND and OR logic to an array using the AND function and the OR function . The challenge is that the AND function and the OR function both aggregate values to a single result. This means you can’t use them in an array operation where the goal is to return more than one result. One workaround to this limitation is to use the MAP function, as explained below....

Purpose Return value Syntax =MODE.MULT(number1,[number2],...) number1 - A number or cell reference that refers to numeric values. number2 - [optional] A number or cell reference that refers to numeric values. Using the MODE.MULT function The Excel MODE.MULT function returns a vertical array of the most frequently occurring number(s) in a numeric data set. The mode is the most frequently occurring number in a set of data. When there is just one mode in a set of data, MODE....

About This Shortcut This shortcut will move to the next ribbon control from the currently active control. Use SHIFT-TAB to move backwards. About This Shortcut This shortcut will activate the selected command or control in the Ribbon. For example, it will check and uncheck a checkbox.

About This Shortcut Paste will paste everything on the clipboard, both content and formatting. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + V also works. You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard. About This Shortcut This shortcut performs two important actions in Excel: It converts formatted text (i.e., italic, bold, font, etc....

Purpose Return value Syntax =ROWS(array) array - A reference to a cell or range of cells. Using the ROWS function The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array , which can be a range or array . Examples Use the ROWS function to get the row count for a given reference or range....

Explanation At the core, this formula uses INDEX to create an expanding reference like this: INDEX([Color],1):[@Color] // expanding range On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column. INDEX([Color],1) // first cell in color This works because, the INDEX function returns a reference to the first cell, not the actual value. On the right side of the colon, we get a reference to the current row of the color column like this:...

About This Shortcut The first time you use this shortcut, Excel will select the current region around the active cell, if one can be detected, otherwise the whole sheet is selected. The second time you use the shortcut (consecutively), Excel will select the entire worksheet. About This Shortcut Select the current array, which is the array that the active cell belongs to. This shortcut is useful for editing multi-cell array formulas ....

Explanation The goal is to generate a series of dates by month based on a given start date. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the EDATE function like this: =EDATE(B5,SEQUENCE(12,1,0)) The result is a series of 12 dates, incremented by one month, beginning on June 1, 2023, and ending on May 1, 2024. At a high level, this formula uses the EDATE function to return 12 dates one month apart, and it uses the SEQUENCE function to create the numeric array needed to perform this operation in one step....

A structured reference is a special syntax for referencing Excel Tables . Structured references work like regular cell references in formulas, but they are easier to read and understand. Structured references are also dynamic, and adjust automatically when data is added or removed from an Excel Table. Excel will enter structured references automatically when you reference parts of a table with point and click. This behavior is controlled by the preference “Use table names in formulas”....

Explanation In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. For convenience, the range B5:B16 is named data . At a high level, the solution breaks down into two steps: (1) extract the n largest values from the data set and (2) sum the extracted values. There are several ways to approach this problem depending on what version of Excel is available....

Purpose Return value Syntax =TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]) text - The text string to extract from. delimiter - The character(s) that delimit the text. instance_num - [optional] The instance of the delimiter in text. Default is 1. match_mode - [optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0. match_end - [optional] Treat end of text as delimiter. 0 = disabled, 1 = enabled. Default is 0. if_not_found - [optional] Value to return when no match is found....

Transcript In this video, we’ll look at how to use TEXTSPLIT with multiple delimiters. Sometimes, you’ll want to configure TEXTSPLIT to use multiple delimiters. On this worksheet, we have some comma-separated text. If I configure TEXTSPLIT to split the text using a comma… It works fine for most of the data, but notice it fails in rows 8 to 12. This happens because the delimiter in these rows is actually a semi-colon (;) instead of a comma....

Explanation The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10: =ROWS(B5:C10) // count rows ROWS counts the number of rows in any supplied range and returns a number as a result. For example, if we provide all of column A in a range, Excel returns 1,048,576 the total number of rows in an Excel worksheet....

Explanation When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. In this example, the goal is to remove the #N/A error that VLOOKUP returns when it can’t find a lookup value. In general, the best way to do this is to use the IFNA function. However, the IFERROR function can also be used in the same way. Both options are explained below. VLOOKUP function The VLOOKUP function performs a lookup operation on vertical data....

An absolute reference in Excel refers to a reference that is “locked” so that rows and columns won’t change when copied. Unlike a relative reference , an absolute reference refers to an actual fixed location on a worksheet. To create an absolute reference in Excel, add a dollar sign before the row and column. For example, an absolute reference to A1 looks like this: =$A$1 An absolute reference for the range A1:A10 looks like this:...

Transcript In this video, we’ll set up the XLOOKUP function with a basic example. The XLOOKUP function is a more flexible replacement for VLOOKUP , and it’s just as easy to use. In this worksheet, we have population data for some of the largest cities in the world. Let’s configure the XLOOKUP function to retrieve the country and population for a given city by matching on the name. To start off, put the cursor in G5, enter an equals sign (=) and type “xl”....

Purpose Return value Syntax =BINOM.DIST(number_s,trials,probability_s,cumulative) number_s - The number of successes. trials - The number of independent trials. probability_s - The probability of success on each trial. cumulative - TRUE = cumulative distribution function, FALSE=probability mass function. Using the BINOM.DIST function The BINOM.DIST function returns the individual term binomial distribution probability. You can use BINOM.DIST to calculate probabilities that an event will occur a certain number of times in a given number of trials....

Purpose Return value Syntax =BITRSHIFT(number,shift_amount) number - The number to be bit shifted. shift_amount - The amount of bits to shift to the right, if negative shifts bits to the left instead. Using the BITRSHIFT function Integer underflow results in loss of the least significant bits. For example, if the number 3 is shifted right by one, then the right-most binary bit is truncated and lost. For any bit shift that results in integer overflow, where the result is larger than 2^48 -1, the function returns the #NUM!...

Articles Conditional formatting with formulas (10 examples) Test conditional formatting with dummy formulas Formulas See Conditional Formatting section here Demo files CF examples.xlsx CF demo.xlsx Videos How to highlight rows with conditional formatting Perfect conditional formatting with dummy formulas How to highlight exact match lookups How to build a search box to highlight data Webinar Replay Below is the replay of our Conditional Formatting webinar, held February 21. Thanks to all of you who attended!...

Explanation The DATE function creates a valid date using three arguments: year, month, and day: =DATE(year,month,day) In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from a text string, and feed the results into the DATE function: =DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2)) The LEFT function extracts the leftmost 4 characters for year, the MID function extracts characters in positions 5-6 for month, and the RIGHT function extracts the rightmost 2 characters as day....