Transcript In this video, we’ll look at how to generate a sequence of dates with the SEQUENCE function . The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel dates are just numbers, SEQUENCE works well for generating dates. In this first worksheet, we have a couple cells set up to collect input for a start date, and days. I’ll enter the SEQUENCE function in cell E5 and configure it to use these inputs....

Explanation In this example, we are sorting a table with 10 rows and 3 columns. In the range J5:J7 (the named range custom ), the colors “red”, “blue”, and “green” are listed in the desired sort order. The goal is to sort the table using values in the Group column in this same custom order. The SORTBY function allows sorting based on one or more “sort by” arrays, as long as dimensions are compatible with the source data....

Explanation In this example, the goal is to split the names in column B into three separate parts (First, Middle, and Last) with a single formula. In cases where there is no middle name, the Middle column should be blank. In cases where there are two middle names, the Middle column should contain both names. The challenge The main challenge is that a middle name is not always present and in some cases, there is more than one middle name....

Explanation In this example the goal is to sum the numbers in the range F5:F16 when cells in the range C5:C15 contain “Red”. To solve this problem, you can use either the SUMIFS function or the SUMIF function . The SUMIF function is an older function that supports only a single condition. SUMIFS on the other hand can be configured to apply multiple criteria. Both options are explained below. SUMIFS solution In the example shown, the solution is based on the SUMIFS function....

Pie charts are one of the simplest chart types in Excel, good for showing “part-to-whole” relationships with data in a small number of categories. Pie charts get a lot of criticism in the professional data visualization world, but they are compact and effective when the number of categories is small (2-5) and the relative size of each category is clear. In this example, a pie chart is used to plot the results of the survey question “What’s your favorite flavor of ice cream?...

About This Shortcut While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again: A1 –> $A$1 –> A$1– > $A1– > A1 This is much faster and easier than typing the $ character manually. To convert an existing formula, enter cell edit mode, place the cursor in or next to the reference you’d like to convert, then use the shortcut....

About This Shortcut This shortcut toggles bolding on and off for the current selection. On Windows, Ctrl 2 also toggles bolding. About This Shortcut This shortcut toggles italics on and off for the current selection. On Windows, Ctrl 3 also toggles italics.

Explanation The COLUMNS function is fully automatic. When you provide a range to COLUMNS, it will return a count of all columns in the range. In the example, the formula in F6 returns 2, because there are 2 columns in the range B5:C10: =COLUMNS(B5:C10) // count columns COLUMNS counts the number of columns in any supplied range and returns a number as a result. For example, if we provide all of row 1 in a range, Excel returns 16,384 the total number of columns in an Excel worksheet....

Purpose Return value Syntax =UNIQUE(array,[by_col],[exactly_once]) array - Range or array from which to extract unique values. by_col - [optional] How to compare and extract. FALSE = by row (default); TRUE = by column. exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default). Using the UNIQUE function The UNIQUE function extracts a list of unique values from a range or array . The result is a dynamic array that spills onto the worksheet, automatically updating when source data changes....

Explanation In geometry, the formula for calculating the volume of a cylinder is: The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3.14159265358979, accurate to 15 digits: =PI() // returns 3.14159265358979 To square a number in Excel, you can use the exponentiation operator (^): =A1^2 Or, you can use the POWER function :...

Explanation One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH , but the syntax is more complex. In the example shown, we want to retrieve all values associated with Q3. The formula in H5 is: =XLOOKUP(H4,C4:F4,C5:F8) The lookup_value comes from cell H4, which contains “Q3” The lookup_array is the range C4:F4, which quarters in a header The return_array is C5:F8, which contains all data The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last) XLOOKUP finds “Q3” as the second item in C4:F4 and returns the second column of the return_array , the range E5:E8....

Purpose Return value Syntax =YEARFRAC(start_date,end_date,[basis]) start_date - The start date. end_date - The end date. basis - [optional] The type of day count basis to use (see below). Using the YEARFRAC function YEARFRAC returns a decimal number representing years between two dates. For example: =YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1 =YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5 =YEARFRAC("1-Jan-2019","1-Jan-2021") // returns 2 Although the generic syntax for YEARFRAC shows the start date followed by the end date, you can provide the dates in any order with the same result....

A 100% stacked column chart is an Excel chart type meant to show the relative percentage of multiple data series in stacked columns, where the total (cumulative) of stacked columns always equals 100%. The 100% stacked column chart can show the part-to-whole proportions over time, for example the proportion of quarterly sales per region or the proportion of monthly mortgage payment that goes toward interest vs. principal. Although stacked column charts can show the change over time of a part-to-whole relationship, it’s generally difficult to compare the relative size of the components that make up each column....

Purpose Return value Syntax =AND(logical1,[logical2],...) logical1 - The first condition or logical value to evaluate. logical2 - [optional] The second condition or logical value to evaluate. Using the AND function The AND function is one of Excel’s logical functions. It is designed to test multiple conditions simultaneously and return TRUE only if all the conditions are met. If any condition is not met AND will return FALSE. You can use the AND function to check if a series of values all meet certain criteria before performing a calculation or action....

“Array of arrays” is a name for a limitation of Excel’s calculation engine that can affect certain formulas that return multiple results. Excel’s current formula engine will not return an “array of arrays” and the result may be a truncated version of the data (as in the example) or an error in some cases. “Array of arrays” is not limited to the TEXTSPLIT function , but TEXTSPLIT is an easy way to see the limitation in action....

Explanation In this example, the goal is to build a “cash denomination calculator.” A cash denomination calculator is a tool for counting and verifying cash amounts. It can calculate the denominations needed to achieve a certain cash value. It can also perform the reverse calculation and determine the cash value of a group of bills and coins of different denominations. Since we are building this tool in Excel, we also want to make it easy to customize the denominations to account for different currencies....

Explanation In this example, the goal is to count numbers in the range B5:B15 ( named data ) that begin with the numbers shown in column D. You would think this would be a good problem to solve with the COUNTIF function but for reasons explained below, COUNTIF won’t work. Instead, you can use the SUMPRODUCT and Boolean logic . See below for a full explanation. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?...

Explanation Working from the inside out, the logical criteria used in this formula is: (data)<70 where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data , and the double negative coerces the TRUE FALSE values to 1 and 0 to yield an array like this: {0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0} Like the original data, this array is 10 rows by 7 columns (10 x 7) and goes into the MMULT function as array1 ....

About This Shortcut This shortcut will display the Delete dialog box when whole columns or rows are not selected . Be careful if rows or columns are selected because this shortcut will delete them in one step.. Note: In Mac 2016, Control - also works (same as Windows). About This Shortcut This shortcut will delete rows that are selected. Note: In Mac 2016, Control - also works (same as Windows).

Explanation Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will automatically return the first day of the current month. In this example, the goal is to generate a dynamic calendar for any given month, based on a start date entered in cell J6, which is named “start” We assume that start is a valid first-of-month date like 1-Jan-2022, 1-Feb-2022, 1-Mar-2022, etc....