When the link is clicked in Excel, the default email client will create a new email with the information supplied. The link text (“link”) can be customized as desired. Note: the formula above is entered with line breaks for better readability. Mailto link protocol The mailto link protocol allows five variables as shown in the table below: Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required....

Excel has many useful shortcuts for selecting cells. This includes shortcuts for selecting rows, columns, groups of cells, and even the entire worksheet. Let’s take a look. When you’re selecting individual cells, you can use the arrow keys to move the selection around. If you hold down the shift key, and then press an arrow key, you can extend this selection in any direction without using the mouse. To select an entire column, press control-spacebar....

where “lower” is the named range I5, and “upper” is the named range I6. Thanks to Robert Johnson for his fix to the original formula, which broke in certain cases. Current version should work when upper > lower. To calculate total hours between start and end time, the formula in D5 is: This formula is explained in more detail here. The formula in E5 works in two parts, using IF to control flow....

The main reason SUMPRODUCT appears so often in Excel formulas is that it supports array operations natively, and array operations combined with Boolean logic are a very good way to solve many problems in Excel. In the past (Excel 2019 and older) Excel’s formula engine did not handle most array operations without special handling. As a result, SUMPRODUCT has always been a simple way to create an array formula that “just works”....

which performs a wildcard match with the value in H4 and returns all 4 fields as the result. The TRANSPOSE function is optional and used here only to convert the result from XLOOKUP to a vertical array. The lookup_value comes from cell H4 The lookup_array is the range D5:D15, which contains Last names The return_array is B5:E15, which contains all all fields The not_found argument is set to “Not found” The match_mode is is 2, to allow wildcards The search_mode is not provided and defaults to 1 (first to last)...

In the example shown, the formula in C5 is: In this part of the formula, MID, ROW, INDIRECT, and LEN are used to convert a string to an array or letters, as described here. MID returns an array of all characters in the text. {“W”;“i”;“l”;“l”;“i”;“a”;“m”;" “;“S”;“h”;“a”;“k”;“e”;“s”;“p”;“e”;“a”;“r”;“e”} This array is fed into the CODE function, which outputs an array of numeric ascii codes, one for each letter. Separately, ROW and INDIRECT are used to create another numeric array: This is the clever bit....

In this part of the formula, COUNTIF will return a positive number if any cell in the range is less than 65, and zero if not. In the range B5:F5, there is one score below 65 so COUNTIF will return 1. The NOT function is used to convert the number of from COUNTIF into a TRUE or FALSE result. The trick is that NOT also “flips” the result at the same time:...

Here is the data used to plot this chart: How to make this chart 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.

Which retrieves data where the State = “TX”. Since there are 11 cells in the range E5:E11, this expression returns an array of 11 TRUE and FALSE values like this: This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output. Finally, the if_empty argument is set to “not found” in case no matching data is found....

COUNTIF returns 1, since there is one cell in C5:C16 with a value less than 75. The test scores in the range C5:C16 and we want to count scores less than 75, so we configure COUNTIF like this: Since there is only one score in C5:C16 that is less than 75, COUNTIF returns 1 as a result. Notice that criteria is given as a text value in double quotes ("")....

The result is 9, since nine cells in the range C5:C16 contain values. COUNTA function While the COUNT function only counts numbers, the COUNTA function counts both numbers and text. This means you can use COUNTA as a simple way to count cells that are not blank. In the example shown, the formula in F6 uses COUNTA like this: Since there are nine cells in the range C5:C16 that contain values, COUNTA returns 9....

Example In the example shown above, the formula in cell E13 is: where sales (C5:C12), periods (B5:B12), and confidence (J4) are named ranges. With these inputs, the FORECAST.ETS.CONFINT returns 198.92 in cell E13. This formula is copied down the table, and the resulting confidence interval values in column “CI” are used to calculate the upper and lower bounds of the forecast, as explained below. The forecast value in cell D13 is calculated with the FORECAST....

The MROUND function takes two arguments, number and significance. Number is the numeric value to round. The significance argument is the multiple to which number should be rounded. In most cases, significance is provided as a numeric value, but MROUND can also understand time entered as text like “0:15” or “0:30”. Number and significance must have the same sign, otherwise MROUND will return a #NUM! error. Examples Below are some examples of MROUND formulas with hardcoded values: To round a number in A1 to the nearest multiple of 5, you can use MROUND like this:...

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.

Note: this shortcut is unusual on a Mac because the Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys. 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.

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.

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.

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 used range is continually updated as changes are made to a worksheet, and includes any cell that has ever been used. For example, if A1 contains a value, and that value is deleted, cell A1 is still considered used. Sometimes, a worksheet will have a used range that is much larger than expected. This can cause performance problems and other unexpected behavior. One way to “reset” a used range is to delete all unused columns and rows....

In general, there is no need to use the VALUE function because Excel automatically converts text to numbers as needed. However, VALUE is provided for compatibility with other spreadsheet applications. Use the VALUE function to convert text input to a numeric value. The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, Excel automatically converts text to numeric values as needed, so the VALUE function is not needed....