In the example, the active cell contains this 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 the example shown, the formula in cell B5 is: By definition, the value returned by DAY is always equal to the number of days in the month, since the date supplied to DAY is always the last day. 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....

Why Dynamic Arrays? Dynamic Arrays are the coolest and most useful addition to Excel formulas in years, maybe ever. Dynamic Arrays make it easy to build array formulas to handle multiple values at the same time, solving many complex problems in an elegant way. You can now build formulas that filter, sort, and extract data with a clean, logical syntax. Dynamic Arrays are the future of reports, models, and dashboards that need to respond to changes instantly....

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. Basis specifies the method used to count days (see below). In the example shown, the formula in F6 is: COUPDAYSNC returns an integer, so use a number format and not a date format to display properly....

In the example shown above, data validation is used to provide a dropdown menu with four choices: Red, Blue, Green, and Purple. The validation is applied to the range B5:B16, and the dropdown menu appears when any cell in this range is selected: For a detailed overview of the data validation, see our Excel Data Validation Guide. Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

The DECIMAL function takes two arguments: number and radix. Number should be the text representation of a number in a known base. Radix is the number of digits used to represent numbers (i.e. the base) and should be an integer between 2 and 36. The characters given in number need to conform to the numbering system specified with radix. Examples In the hexadecimal number system, the number 255 is represented as “FF”....

Examples ISERROR will return TRUE if A1 contains an error: You can use the ISERROR function together with the IF function to test for an error and display a custom message if found: To trap an error and perform a different calculation, the IFERROR function is a cleaner approach. Other error functions Excel provides a number of error-related functions, each with a different behavior: The ISERR function returns TRUE for any error type except the #N/A error....

The MEDIAN function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MEDIAN ignores empty cells, text values, and the logical values TRUE and FALSE. The MEDIAN function will accept up to 255 separate arguments. Examples When the number of values provided is odd, MEDIAN returns the middle number: When the number of values provided is even, MEDIAN returns the average of the two middle numbers: In the worksheet shown above, the formulas in H5 and H6 are: Note that MEDIAN ignores the empty cell in D5:D16 and returns the middle number in the eleven values provided....

Excel offers many types of number formatting. 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.

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 the example shown, the slicer is set up to filter on the Department field in the data. Because there are five values in the data (Engineering, Fulfillment, Marketing, Sales, and Support) the slicer makes provides buttons with these names. Author 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....

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”. Examples To get the total rows or columns in an Excel table: To count or sum items in a column: To count visible rows in a filtered table: Current row Inside an Excel table, you’ll see the following syntax for “current row”: Absolute references References to single columns in a table are relative by default - when formulas are copied across columns, you’ll see the column references change....

VSTACK works equally well for ranges on a worksheet or in-memory arrays created by a formula. The output from VSTACK is fully dynamic. If data in the given arrays changes, the result from VSTACK will immediately update. VSTACK works well with Excel Tables, as seen in the worksheet above, since Excel Tables automatically expand when new data is added. Use VSTACK to combine ranges vertically and HSTACK to combine ranges horizontally....

where the values in column D (start) and the range E4:J4 are valid Excel dates. Translated: if the date in column D is less than or equal to the date in row E. Note these are mixed references. The column in $D5 is locked, and the row in E$4 is locked. This allows the formula to be copied across the table. If the result of this test is TRUE, the IF function returns the expense from column C, otherwise IF returns zero (0)....

Notes: Exact-match VLOOKUP is slow When you use VLOOKUP in “exact match mode” on a large set of data, it can really slow down the calculation time in a worksheet. With, say, 50,000 records, or 100,000 records, calculation can take minutes. Exact match is set by supplying FALSE or zero as the fourth argument: The reason VLOOKUP in this mode is slow is because it must check every single record in the data set until a match is found....

This formula returns the “Group” column plus data for the year in J4, sorted in descending order by the values in that year. The year in J4 is a dropdown menu created with data validation. In this example, the goal is to filter the data shown in B5:G15 by year, then sort the results in descending order. In addition, the result should include the Group column, sorted in the same way....

The result returned by FILTER includes only rows where group is “red” or “blue”. After each expression is evaluated, we have the following two arrays: The math operation (addition) coerces TRUE and FALSE values to 1s and 0s: The result is a single array like this: This final array is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a 1. Or, to put it another way, FILTER removes rows that are zero....