Explanation The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the interval between two dates in years, months, and days. In the example shown, the goal is to calculate age in years....

Explanation Dates in Excel are serial numbers that start on 1/1/1900, which is represented by the number 1. In the example shown, the formula in cell D6 simply subtracts the numeric value of 1/1/1999 (36161) from the numeric value of 1/1/2000 (36526) to get a result of 365. The steps look like this: =C6-B6 =36161-36526 =365 Working with today To count the number of days between an earlier date and today, you can use the TODAY function :...

Transcript Excel provides both single and double underlining. It also provides a special variant of each for numbers using the Accounting format. Let’s take a look. For most basic underlining, you can just use the Underline button on the ribbon. The Underline button toggles underlining on and off. It also provides a menu for selecting single and double-underlining. The Underline button remembers your last selection. For example, if you apply a Double Underline, the button will keep that selection until you select Single Underline again....

Transcript In this video, we’ll look at options for customizing a category axis in an Excel chart. To recap, a value axis is used to plot numeric data on a scale, while a category axis is used to group text or dates into specific categories. In a previous video, we created a line chart to show average 30 year mortgage rates over a 5 year period. We already customized the value axis, so let’s make some changes to the horizontal category axis....

Explanation About the #REF! error The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid. In the example shown, the formula in C10 returns a #REF! error when copied to cell E5: =SUM(C5:C9) // original C10 =SUM(#REF) // when copied to E5 Preventing #REF errors The best way to prevent #REF!...

Transcript In this lesson, we’ll look at the difference between finding what is stored in a cell and finding what is displayed in a cell. Let’s take a look. The naming is a little confusing, so let’s take a look at some examples. Excel’s default setting for “look in” is formulas, and this makes sense in most cases. For example, we can easily search for and find names like Cecil …or Hana....

Transcript In this video we’ll take a quick look at the information provided by the status bar . The status bar at the bottom of the Excel window provides real-time data about currently selected cells and can give you a lot of good information without the need to write any formulas. Here we have test score data for a group of students. Let’s use the status bar to look at the information shown in column J....

Purpose Return value Syntax =HSTACK(array1,[array2],...) array1 - The first array or range to combine. array2 - [optional] The second array or range to combine. Using the HSTACK function The HSTACK function combines arrays horizontally into a single array . Each subsequent array is appended to the right of the previous array. The result from HSTACK is a single array that spills onto the worksheet into multiple cells. HSTACK works equally well for ranges on a worksheet or in-memory arrays created by a formula....

Explanation The goal is to identify records where the color is “Red” or “Green” and the quantity is greater than 10. If a row meets all conditions, the formula should return “x”. If any condition is not true, the formula should return an empty string (""). This problem can be solved with the IF function together with the OR function and the AND function. IF function The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result....

Explanation The goal is to mark rows where the color is “Red” AND the size is “Small” or “Medium”. To perform this task, you can use the IF function in combination with the AND function and the OR function . IF function The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:...

Purpose Return value Syntax =IMARGUMENT(inumber) inumber - The complex number in the form “x+yi”. Using the IMARGUMENT function The Excel IMARGUMENT function returns the angle of a complex number measured in radians. For example, given the complex number “3+4i” the function returns the angle 0.927295218. =IMARGUMENT("3+4i") // returns 0.927295218 Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number....

About This Shortcut This shortcut will insert columns as long as at least one column is selected. With a laptop keyboard, use Control Shift + . With a full keyboard, use Control + Note: In Mac 2016, this shortcut was changed to Command Shift +, and now Control + I changes text to italic. In previous Mac Excel versions, Control + I inserts a row (when a row is selected) or brings up the Insert Cells dialog when the selection does not include an entire row....

About This Shortcut If there is no comment in the active cell, a new threaded comment will be created. If a comment already exists in the cell, the comment box will open. About This Shortcut In Windows you can delete a comment on the keyboard as follows: Shift + F10 to open right-click menu Type “m” to Delete comment In Excel 2016 on a Mac, you can actually type letters to select menu items in the right-click menu....

Explanation When you are working with a large table that extends beyond the visible area of a worksheet, you might want to see the most recent data in the table without scrolling to the bottom of the table. Examples of this kind of data include things like: Recent deposits or expenses Recent transactions Recent stock prices Recent orders Recent invoices In this example, the goal is to dynamically retrieve the last n rows of a table or range, so that you can quickly check the entries at the bottom, where n is a value that can be easily changed....

Transcript In this video, we’ll look at how to list duplicate values. In other words, values that appear more than once in a set of data. In this worksheet, we have a list of the Wimbledon Men’s Singles Champions since 1968, in a table called “data”. How can we list names that appear more than once, along with a count? To start off, let’s look at how we might count the names in the data with the COUNTIF function in column H....

Purpose Return value Syntax =MAP(array1,[array2],...,lambda) array1 - The array to be mapped. array2 - [optional] Additional arrays required by the LAMBDA. lambda - The custom LAMBDA function to apply. Using the MAP function The MAP function “maps” a custom LAMBDA function to each value in a supplied array . The LAMBDA is applied to each value, and the result from MAP is an array of results with the same dimensions as the original array....

For writing formulas, Excel has a standard set of math operators for performing addition, subtraction, multiplication, and exponentiation (raising to the power of). In addition, Excel also provides operators for cell ranges, range intersects, and implicit intersection . The symbols used for these operations are summarized in the table below. Symbol Operation Example + Addition =2+3=5 - Subtraction =9-2=7 * Multiplication =6*7=42 / Division =9/3=3 ^ Exponentiation =4^2=16 () Parentheses =(2+4)/3=2 & Concatenation =“Z”&100=“Z100” : Range =SUM(F5:F14)=55 space Range Intersect =F1:F14 A10:G10 = 6 @ Implicit intersection @F5:F14 = 10 Logical operators are summarized here ....

Explanation The MATCH function returns the position or “index” of the first match based on a lookup value in a range. MATCH supports wildcard matching with an asterisk “*” (one or more characters) or a question mark “?” (one character), but only when t he third argument, match_type, is set to FALSE or zero. In the example, we pick up the value in cell E7 and use concatenation to combine this value with asterisks (*) on either side....

About This Shortcut This shortcut will select all cells with formulas that depend on the cells in the current selection. When you use this shortcut, Excel looks for formulas that refer to currently selected cells. If found, Excel selects the cells that contain these formulas. About This Shortcut This shortcut will select all cells with formulas that refer directly or indirectly to cells in the current selection.

About This Shortcut Inside a cell, this shortcut will extend the selection by one character to the right each time the right arrow key is pressed. About This Shortcut Inside a cell, this shortcut will extend the selection by one character to the left each time the left arrow key is pressed.