Explanation In this example, the goal is to generate a list of leap years between a given start year and end year. The worksheet is set up so that the start year is an input in cell B5 and the end year is an input in cell B8. If either value changes, the formula should generate a new list of leap years. In the current version of Excel, the easiest way to do this is with the SEQUENCE function and the FILTER function in a formula like this:...

Transcript In this video, we’ll look at shortcuts for working with formulas. Even basic formulas require absolute and relative references, so one of the most useful formula shortcuts is the toggle for references. For example, to complete this multiplication table, we need to multiply the left column by the top row. This means we have to lock both the column and row references to make sure they don’t change. Use F4 on Windows and Command + T on a Mac to toggle through the 4 options for absolute and relative references....

Purpose Return value Syntax =SKEW(number1,[number2],...) number1 - A range or reference that contains numeric values. number2 - [optional] A range or reference that contains numeric values. Using the SKEW function The SKEW function returns the “skewness” of a distribution. SKEW measures the symmetry of a distribution. A positive skew result indicates a distribution that tails off to the right. A negative skew result indicates a distribution that tails off to the left....

Purpose Return value Syntax =SLOPE(known_ys,known_xs) known_ys - An array or range of numeric data points (dependent values). known_xs - An array or range of numeric data points (independent values). Using the SLOPE function The SLOPE function returns the slope of a regression line based on known y values and known x values. A regression line is a “best fit” line based on known data points. The slope of a line is a measure of steepness....

Explanation In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts by position , starting with cell C5. In other words, we want to sum the first n values starting at cell C5....

Explanation This example shows how the SUMIFS function can sometimes be used to “lookup” numeric values, as an alternative to more complicated multi-criteria lookup formulas. This approach is less flexible than more general lookup formulas based on INDEX and MATCH (or VLOOKUP) but it’s also more straightforward since SUMIFS is designed to easily handle multiple criteria. It’s also very fast. In the example shown, we are using the SUMIFS function to “look up” the price of an item based on the item name, color, and size....

Purpose Return value Syntax =TAKE(array,[rows],[col]) array - The source array or range. rows - [optional] Number of rows to return as an integer. col - [optional] Number of columns to return as an integer. Using the TAKE function The TAKE function returns a subset of a given array. The size of the array returned is determined by separate rows and columns arguments. When positive numbers are provided for rows or columns, TAKE will retrieve values from the start or top of the array....

Purpose Return value Syntax =TEXTJOIN(delimiter,ignore_empty,text1,[text2],...) delimiter - Separator between each text. ignore_empty - Whether to ignore empty cells or not. text1 - First text value or range. text2 - [optional] Second text value or range. Using the TEXTJOIN function The TEXTJOIN function concatenates multiple values together with or without a delimiter. TEXTJOIN can concatenate values provided as cell references, ranges, or constants, and can optionally ignore empty cells. The TEXTJOIN function takes three required arguments : delimiter , ignore_empty , and text1 ....

About This Shortcut This shortcut will ungroup selected rows or selected columns. Select whole rows or columns first before using. About This Shortcut This shortcut will display the Group Dialog Box as long as no whole rows or columns are selected.

Explanation In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the weight of the item. The challenge is that we also need to filter by service. This means we need to apply criteria in two steps: (1) match based on Service, and (2) match based on Weight. The screen below shows the basic idea: One way to solve this problem is with XLOOKUP + the IF function to perform the required filtering....

About This Shortcut This shortcut zooms out on the current worksheet, making items smaller. Note: you can also use Control + mouse scroll wheel to zoom in and out on both Windows and Mac. On Windows, the shortcut appears to be new with Excel 2016, and decreases the zoom level shown in the lower right of the worksheet by 15% each time Control - is used. Use Ctrl Alt + to zoom out by 15%....

About This Shortcut This shortcut will add a border around the outside edge of the current selection. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a border added or removed from the border preview area. On the Mac, this shortcut works directly on the worksheet, and adds a border to each cell in the selection....

An array in Excel is a structure that holds a collection of values. Arrays can be mapped perfectly to ranges in a spreadsheet, which is why they are so important in Excel. An array can be thought of as a row of values, a column of values, or a combination of rows and columns with values. All cell references like A1:A5 and C1:F5 have underlying arrays, though the array structure is invisible in most contexts....

Explanation When no argument is provided, the ROW function returns the “current row”, that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range. In the example shown, the formula in B5 is: =ROW()-ROW(Table1[#Headers]) The first ROW returns 5, since ROW is provided no argument, and resides in cell B5....

Explanation For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 per - the period we want to work with. Supplied as 1 since we are interested in the principal amount of the first payment....

Explanation In this example, the goal is to calculate the number of years between a start date in column B and an end date in column C. An easy way to solve this problem is to use the YEARFRAC function, which returns the number of years between any two dates as a decimal number. YEARFRAC function The YEARFRAC function returns a decimal number representing the fraction of a year between two valid Excel dates ....

Explanation In this example, the goal is to test the text in a cell and return TRUE if it contains one or more specific words, and FALSE if not. Notice the emphasis here is on words, not substrings. For example, if we are testing for the word “green” and the text contains the word “evergreen” but not the word “green” the formula should return FALSE. Traditionally, this has been a difficult problem in Excel because there has not been a simple way to parse text into words....

Explanation The ABS function is fully automatic. All you need to do is supply a number and ABS will return the absolute value. Convert negative numbers in place If you only need to convert negative numbers once, you can convert in-place with Paste Special : Add -1 to a cell and copy to the clipboard Select the negative numbers you want to convert Use Paste Special > Values + Multiply The video on this page shows this technique and many other paste special shortcuts....

Explanation Times in Excel are fractional values of the number 1 . So, 12 PM is 12/24 = .5, 6:00 AM is 6/24 = .25, and so on. So, to convert a time by a given number, you need to divide the number of hours by 24 to get required decimal value: E5/24 // convert adjustment to Excel time We add the result to the starting time: C5+(E5/24) To make sure we have a true time value, we need to ensure that we have only a decimal value....

Explanation In this example, the goal is to count errors in the range B5:B15, which is named data for convenience. The article below explains several different approaches, depending on your needs. For background, this article: Excel Formula Errors . COUNTIF function One way to count individual errors is with the COUNTIF function like this: =COUNTIF(data,"#N/A") // returns 1 =COUNTIF(data,"#VALUE!") // returns 1 =COUNTIF(data,"#DIV/0!") // returns 0 This is an odd syntax since technically errors are not text values ....