where range1 (B5:B8) and range2 (D5:D9) are named ranges. The formula appends the second range to the first range and the result spills into F5:F13. See below for details. Note: While this is a good example of what can be done with dynamic array formulas, Excel now contains two new functions that make this approach unnecessary: the HSTACK function and the VSTACK function. Single column ranges The formula to combine single column ranges is based on INDEX function, the SEQUENCE function, the IF function, and the LET function....

The ribbon How the ribbon is organized, and why tabs on the ribbon mysteriously appear and disappear when you select certain objects. A simple way to add your own buttons to the ribbon, to keep frequently used commands at your fingertips. How to avoid using the ribbon for many commands, and how to quickly hide the ribbon when you just want it gone…this gives you space to see 4 extra rows of data....

Note: The formula on this page counts instances of a word in a range. For example, if a cell contains two instances of a word, it will contribute 2 to the total count. If you just want to count cells that contain a specific word, see this simple formula based on the COUNTIF function. In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count....

which returns an array like {1;2;3;4;5}. Note: when entered in a single cell, Excel will display only the first item in the array. Use F9 in the formula bar to see the actual array result. Normally, you will use this formula inside a larger array formula, entered with control + shift + enter. The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this: The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array....

which displays first item, based on the index provided in the helper column. The same approach is used to display associated sales in column G. For convenience, the worksheet contains the following named ranges: item = B5:B11, sales = C5:C11, sort = D5:D11. At the core, this is a simple INDEX and MATCH formula, where INDEX retrieves a value based on a specified row number: The trick is that the row is calculated with the MATCH function based on values in the sort column: The lookup value in match is generated with the ROWS function and an expanding reference....

The CELL function takes two arguments: info_type and reference. Info_type is a text string that indicates the type of information requested. See the table below for a full list of info types. Reference is a cell reference. Reference is typically a single cell. If reference refers to more than one cell, CELL returns information about the first cell in reference. For certain kinds of information (like filename) the cell address used for reference is optional and can be omitted....

The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. In the example shown, the formula in F4 is: COUPDAYS returns an integer, so use a number format (not a date format) to display properly....

Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. Example #1 In the example shown, the formula in E5 copied down is: This formula catches the #DIV/0! error that occurs when Qty is empty or zero, and replaces it with zero. Example #2 For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0!...

Notes Be consistent with inputs for rate. For example, for a 5-year loan with 4.5% annual interest, enter the rate as 4.5%/12. By convention, the loan value (pv) is entered as a negative value. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

The ISNONTEXT function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is not text, the ISNONTEXT function will return TRUE. If value is text, ISNONTEXT will return FALSE. Examples The ISNONTEXT function returns TRUE for numbers and FALSE for text: If cell A1 contains the number 100, ISNONTEXT returns TRUE: If cell A1 is empty, ISNONTEXT returns TRUE: If a cell contains a formula, ISNONTEXT checks the result of the formula: Note: the ampersand (&) is the concatenation operator in Excel....

Times can be supplied to the MINUTE function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function. The MINUTE function will “reset” to 0 every 60 minutes (like a clock). To work with minute values larger than 60, use a formula to convert time to decimal minutes....

To open the Name Manager, navigate to Formulas > Name Manager. Or, you can use the keyboard shortcut Control + F3. Names ranges A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box. The screen below shows three of the four names seen above in in the Name Manager in use in a formula based on the FILTER function....

In the example shown, the formula in D6, copied down, is: Format the result as a percentage to display with % symbol. Notes Npery should be an integer (Excel will truncate if not). The effect_rate should be a number between 0 and 1. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Explanation One radian is equal to the amount of rotation required to travel one radius along the circumference of the circle, as seen in the image above. A full-rotation or 360° in radians is equal to the value of 2π. A half-rotation or 180° in radians is equal to the value of geometric constant π (pi). Notes To convert radians to degrees, see the DEGREES function. A full-rotation (360°) in radians is equal to 6....

Note: the HYPERLINK function can be used to create a link with a formula. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

In Mac Excel 2016, you can use the same shortcut as Windows, Alt ; Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

On Windows, Ctrl 3 also toggles italics. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

VAR ignores text and logicals passed into as cell references. For example, VAR will ignore FALSE when it appears in a range like A1:A10. However, VAR will evaluate logical values, and text representations of numbers hardcoded directly as arguments. Note: Microsoft classifies VAR as a “compatibility function”, now replaced by the VAR.S function. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VAR assumes arguments a sample of data, not entire population....

where “list” is the named range B5:B11. This is an array formula and must be entered using control + shift + enter in Legacy Excel. Note: In Excel 365 and Excel 2021, the UNIQUE function provides a better, more elegant way to list unique values and count unique values. These formulas can be adapted to apply logical criteria. In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list....

where “range1” is the named range B5:B8, “range2” is the named range D5:D7. The core of this formula is INDEX and MATCH. The INDEX function retrieves a value from range2 that represents the first value in range2 that is found in range1. The INDEX function requires an index (row number) and we generate this value using the MATCH function, which is set to match the value TRUE in this portion of the formula: Here, the match value is TRUE, and the lookup array is created with COUNTIF here: COUNTIF returns a count of the range2 values that appear in range1....