Posts

In the example shown, the formula is solved like this: Days remaining from today If you need to calculate days remaining from today, use the TODAY function like so: The TODAY function will always return the current date. Note that after the end_date has passed, you’ll start to see negative results, because the value returned by TODAY will be greater than the end date. You can use this version of the formula to count down days to an important event or milestone, count down days until a membership expires, etc....

Where “data” in an Excel Table in the range B5:D15. The result is the quantity associated with “Red” (5), excluding “RED”. Note that if there is more than one match (i.e. exact match duplicate colors), SUMPRODUCT will sum matching results. Note: while this is an array formula, it does not need to be entered with Control + Shift + Enter, since SUMPRODUCT handles arrays natively. The SUMPRODUCT function multiplies arrays together and returns the sum of products....

Column charts work best where data points are limited (i.e. 12 months, 4 quarters, etc.). With more data points, you can switch to a line graph. Pros Easy to read Simple and versatile Easy to add data labels at ends of bars Cons Become cluttered with too many categories Clustered column charts can be difficult to interpret Tips Add data labels where when it makes sense Avoid all 3d variants...

Here we have a table that contains employee data. Let’s say we want to highlight people in this table who belong to group A. Conditional formatting works well in a table, but you can’t use structured references directly when you create the rule. I’ll use a helper column to help illustrate. To highlight people in group A using structured references, we’d want to use a formula like this: Translated, this means: the value in the current row of the Group column equals “A”....

Which copies values from every 3rd row in column B as the formula is copied down. In Excel, it’s difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula is copied in 1-step increment across cells. However, with a little work it’s possible to construct formula references that follow specific patterns. In this example, we are using the OFFSET function, which is designed to create references to other cells, or cell ranges, based on a given starting point, or origin....

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....