Explanation In Excel, you can’t easily create formulas that skip columns following a certain pattern, because the references in the formula will automatically change to maintain the relationship between the original source cell and the new target cell. However, with a little work, it’s possible to construct formula references that will follow a specific pattern using the OFFSET function. The OFFSET function is designed to create references by using “offsets” from a starting cell....

Explanation The goal of this example is to count the number of values recorded over 5 days that do not fall between two numbers, a low value, and a high value. In other words, to count values that are “out of range”. Note that each row, labeled A-G, has its own low and high limit, in columns I and J. You might at first think to use the COUNTIFS function with two criteria....

Explanation In this example, the goal is to count cells in a range that contain text values. This could be hard-coded text like “apple” or “red”, numbers entered as text, or formulas that return text values. Empty cells and cells that contain numeric values or errors should not be included in the count. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both approaches are explained below....

The acronym “CSE” stands for “Control + Shift + Enter”. A CSE formula in Excel is an array formula that must be entered with control + shift + enter. When a formula is entered with CSE, Excel automatically wraps the formula in curly braces {}. In the example shown, the formula in F5 is: {=SUM(C5:C14*D5:D14)} This array formula provides “total sales” in one cell, without a separate column and formula to multiply quantity times price....

About This Shortcut This shortcut will delete columns that are selected. Note: In Mac 2016, Control - also works (same as Windows). About This Shortcut This shortcut will display the Delete dialog box, as long as whole columns or rows are not selected. If one or more rows are selected, this shortcut will delete the selected rows. If one or more columns are selected, this shortcut will delete the selected If one or more rows are selected, this shortcut will delete the selected rows....

Dynamic Excel refers to versions of Excel that offer Dynamic Array Formulas , currently Excel 365 and Excel 2021. We use the term Legacy Excel to refer to all other (non-dynamic) versions. In fall 2018, Microsoft announced new functionality in Excel called “dynamic array formulas”. Dynamic array formulas have the ability to natively work with multiple values and can “spill” these results onto the worksheet. The new functionality is provided in two parts:...

About This Shortcut This shortcut alternately expands and collapses the ribbon. Another useful option is to double click on any tab of the ribbon. This will also expand and collapse the ribbon. About This Shortcut This shortcut displays the access keys needed to drive the ribbon with a keyboard only. On Windows F10 also works. No Mac equivalent.

Explanation The general goal is to return the first numeric value in a row or column. More specifically, in the worksheet shown, we have dates in column B and a numeric value in the range C5:C16. Notice that all of the cells in this range have numeric values. Some are blank and some contain text values. We want the first number that appears in the range C5:C16. This problem can be solved using the XLOOKUP function or, in older versions of Excel, an INDEX and MATCH formula....

Explanation In this example, the goal is to work out the total of all expenses using a known percent of total of any one expense . If we know groceries are $200 and we know groceries represent 10.3% of total expenses, we want to calculate the total of all expenses ($1945). In other words, $200 is 10.3% of what number? With “x” as the number we want to find, we have:...

Explanation The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. WEEKNUM takes two arguments: a date , and (optionally) return_type , which controls the scheme used to calculate the week number. By default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1)....

Transcript The default behavior for pivot table is to automatically subtotal fields when they are added to row or column label areas. However, you can turn subtotals on or off on a field-by-field basis. Let’s take a look. When you first add Row or Column labels to a pivot table, you won’t see any subtotals. For example, in this pivot table, let’s add Category as a row label, and Region as a column label field, and Total Sales as a value....

Transcript In this video, we’ll look at how to create and apply a chart template. Chart templates are useful when you want to save and re-apply colors, layouts, font formatting, and other chart settings. In this worksheet, we have the same two charts we looked at earlier in a video about copying and pasting chart formatting. Let’s assume we want to create a template based on the left chart, and then apply it to the chart on the right....

Transcript One thing you’ll need to do in Excel is check and edit formulas to keep them in sync with other worksheet changes. Editing a formula in Excel is similar to editing other content. One difference is that you can update cell references using drag and drop. Let’s take a look. Here we have a worksheet with some incorrect formulas. Let’s check these formulas against the instructions to understand what’s wrong....

Transcript In this video we’ll look at how to use the AVERAGEIFS function to calculate an average from numbers that meet multiple criteria. Here we have a list of 16 properties with prices and other information. Let’s calculate some averages based on the criteria shown in column K. Note that this data already contains a number of named ranges . We have “prices,” “beds,” “size,” “listed,” and “status.” The AVERAGEIFS function can calculate an average for cells that meet multiple criteria....

Explanation In the example worksheet, column D contains a date when a task is completed. If the task is not yet complete, the cell in column D will be empty (blank). In column E, the goal is to display the word “Open” when there is no date in column D. If there is a date in column D, the formula in column E should display nothing. The most common way to solve a problem like this is with the IF function ....

Purpose Return value Syntax =IMSECH(complex_num) complex_num - The number to get the inverse hyperbolic secant of. Using the IMSECH function The Excel IMSECH function returns the hyperbolic secant of a complex number. Given “1+1.5707963267949i” as input, the function returns “-3.9E-15-0.85092i” as output. =IMSECH(COMPLEX(1, PI()/2)) // returns -3.9E-15-0.85092i Explanation The complex hyperbolic secant function is the reciprocal of the complex hyperbolic sine function. In Excel, the function’s output is equivalent to the following formula....

Explanation The goal in this example is to use a formula to report which things exist in a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string. The first step in creating a custom function with the LAMBDA function is to verify the logic needed to solve the problem. The formula below will do the job and return the result seen in column C:...

Explanation In this example, the goal is to list the next n upcoming birthdays from a larger set of 25 birthdays based on the current date. The set of birthdays are in an Excel Table named data in the range B5:C29. In the example shown, we are using 7 for n , so the result will be the next 7 upcoming birthdays, but this number can be changed as desired. The formula in E5 is:...

Purpose Return value Syntax =MINA(value1,[value2],...) value1 - Number, reference to numeric value, or range that contains numeric values. value2 - [optional] Number, reference to numeric value, or range that contains numeric values. Using the MINA function The MINA function returns the smallest numeric value in a range of values. Like the MIN function , MINA ignores empty cells. However, unlike the MIN function, MINA evaluates the logical values TRUE and FALSE as 1 and 0, and evaluates text as zero when these values appear in a range or cell reference....

About This Shortcut This shortcut will move the active cell around the four corners of a selection in a clockwise direction. It’s especially useful when you have a large number of cells selected, and you want to navigate to the top or bottom of the selection without losing the selection . About This Shortcut This keyboard shortcut will cycle through every cell in a selection, moving from top left to bottom right....