Posts

About This Shortcut Cut puts everything on the clipboard: text, formulas, formatting, borders, fills, etc. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + X also works About This Shortcut Paste will paste everything on the clipboard, both content and formatting. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + V also works. You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard....

Explanation The EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. When 1 is given for months , EDATE returns the same date in the next month. Notice that EDATE automatically handles end-of-month dates properly, adjusting the day when needed to return a valid date. Same date in the previous month To get the same date in the previous month, use -1 for months:...

Explanation This formula uses 4 named ranges, defined as follows: width=K6 height=K7 widths=B6:B11 heights=C5:H5 Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell B5 in this case. To highlight the matching row, we use this logical expression: $B5=LOOKUP(width,widths) The reference to B5 is mixed , with the column locked and row unlocked, so that only values in column B (widths) are compared to the value in K6 (width)....

Explanation This formula uses the TEXT function to concatenate the month and year of each date. Then, the two dates are tested for equality. TEXT is a useful function that allows you to convert a number to text in the text format of your choice. In this case the format is the custom date format “myyyy”, which translates to: month number without leading zeros & 4-digit year. For example, if A1 contains the date 9-Jun-2015, TEXT(A1,“myyyy”) will produce the text string “62016”....

Transcript In this lesson we’ll look at how to apply subscript, superscript, and strikethrough formatting. Let’s take a look. If you need to apply subscript or superscript formatting, you won’t find these options on the ribbon. Instead, they’re located on the Font tab of the Format Cells dialog box in the Effects group. To apply either subscript or superscript, you need to follow a particular process. You can’t just select a cell normally and apply one of these formats....

Explanation The “hashtag” error, a string of hash or pound characters like ####### is not technically an error, but it looks like one. In most cases, it indicates the column width is too narrow to display the value as formatted. You might run into this odd looking result in several situations: You apply number formatting (especially dates) to a value A cell containing a amount of text in a cell is formatted as a number A cell formatted as a date or time contains a negative value To fix, try increasing the column width first....

Transcript Each character you see displayed in Excel has a number. Excel has two functions that work with these numbers directly: CODE and CHAR (Character). Let’s look first at the CODE function . The CODE function accepts one argument, which is the text for which you’d like a numeric code. If I use CODE with a capital A (“A”) it returns 65. I’ll get the same result if I supply a reference to B6, which also contains a capital “A....

Transcript In this video, we’ll look at how to use the COUNTIFS function to count cells that meet multiple criteria. Let’s take a look. In this first set of tables, we have two named ranges , “number” and “color.” In column G, I’ll enter a formula that satisfies the conditions in column E. The COUNTIFS function accepts arguments in pairs. The first item in the pair is the range, and the second item is the criteria....

For simplicity, we use the term “Legacy Excel” to refer to any version of Excel before dynamic array formulas were introduced. Practically speaking, this means Excel 2019 and older, since Excel 365 and Excel 2021 are the only versions of Excel that include dynamic array formulas and new functions like SORT , UNIQUE , FILTER , SEQUENCE , and XLOOKUP . Dynamic array formulas are a huge change to the Excel formula engine because they let you easily work with multiple values at the same time in a formula....

Purpose Return value Syntax =MAKEARRAY(rows,columns,function) rows - The number of rows to create. columns - The number of columns to create. function - The custom LAMBDA calculation to apply. Using the MAKEARRAY function The MAKEARRAY function returns an array with specified rows and columns, based on a custom LAMBDA calculation . MAKEARRAY can be used to create arrays with variable dimensions that are calculated. The generic syntax for MAKEARRAY looks like this:...

Explanation In this example, the goal is to calculate the maximum value of every “nth” column in a row of data, where n is a variable entered in the named range M2 . This problem can be solved in several ways, as explained below. The explanation below also includes a formula that will work in older versions of Excel, before dynamic array formulas were introduced. FILTER + MOD + SEQUENCE A classic solution to “nth” problems is to use the MOD function to check a numeric array for a remainder of zero after dividing by n ....

Purpose Return value Syntax =MODE(number1,[number2],...) number1 - A number or cell reference that refers to numeric values. number2 - [optional] A number or cell reference that refers to numeric values. Using the MODE function The MODE function returns the most frequently occurring number in a set of numeric data. If supplied data does not contain any duplicate numbers, the MODE function returns a #N/A error. The MODE function takes multiple arguments in the form number1 , number2 , number3 , etc....

The Name Manager is a dialog box in Excel that allows you to create, edit, and delete defined names. These names can be named ranges , named formulas, and named constants. Using named ranges can make formulas easier to read, understand. and maintain Names also provide simple navigation via the Name Box . To open the Name Manager, navigate to Formulas > Name Manager. Or, you can use the keyboard shortcut Control + F3 ....

Nesting is the technique of placing one formula or function inside another. The idea is that one function requires a value that can be delivered by another. By nesting a function inside another, and placing the inner function where a function argument would appear, the inner function can pass a result directly to the outer function. In other words, the general concept of nesting is that the “outer” formula is doing something useful and the “inner” formula is providing something the “outer” formula needs to do the job....

Purpose Return value Syntax =OCT2HEX(number,[places]) number - The octal number you want to convert. places - [optional] The number of characters to use in the result. If omitted, uses the minimum number of characters necessary. Using the OCT2HEX function The OCT2HEX function is used to convert octal numbers (base 8) to hexadecimal numbers (base 16). This is useful when working with different number systems, especially in engineering and computer science applications....

In this example, a pivot table shows the year-over-year variance in sales for each month of the year. Change can be displayed as the numeric difference or as a percentage (this example). Fields The pivot table uses all two of the three fields in the source data: Date, and Sales. Because Date is grouped by Years and Months, it appears twice in the list, once as “Date” (month grouping), and once as “Years”:...

Transcript In this lesson, we’ll look at an easy shortcut for entering data in more than one cell at a time. The trick is to use the control key while you press enter. Let’s take a look. Normally, to add the same value to a group of cells, you’d add it to one cell, copy the value, then paste. Or, you could use the fill handle. But there’s a faster way to do it....

Transcript In this video, we’ll look at how to sort by multiple columns with the SORT and SORTBY functions. In this worksheet, we have a list of names, projects, values, and regions. This data is not sorted. Our goal is to sort the data first by region, then by name, and finally by value, with larger values first. Let’s start with the SORTBY function. The first argument, array , is the data we want to sort, in this case, all data in the range B5:E16....

Purpose Return value Syntax =SUBSTITUTE(text,old_text,new_text,[instance_num]) text - The text to change. old_text - The text to replace. new_text - The text to replace with. instance_num - [optional] The instance to replace. If not supplied, all instances are replaced. Using the SUBSTITUTE function The SUBSTITUTE function is a way to perform a find‑and‑replace with a formula. Use it when you know what text you want to change, but you don’t know (or care) where it appears in a text string....

Explanation This formula uses structured references to refer to the “Amount” column in each table. The structured references in this formula resolve to normal references like this: =SUM(Table1[Amount],Table2[Amount]) =SUM(C7:C11,F7:F13) =1495.5 When rows or columns are added or removed from either table, the formula will continue to return correct results. In addition, the formula will work even if the tables are located on different sheets in a workbook. Alternative syntax with Total row It is also possible to reference the total row in a table directly, as long as tables have the Total Row enabled....