Explanation At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters to extract. The text comes from column B, and the number of characters can be any large number that will ensure the last two words are extracted. The challenge is to determine the starting position, which is just after the second to last space....

Explanation At the core, this formula looks for a line delimiter (“delim”) and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions. Note: In older versions of Excel on a Mac, use CHAR(13) instead of CHAR(10). The CHAR function returns a character based on it’s numeric code. The number of spaces used to replace the line delimiter is based on the total length the text in the cell....

Explanation The table contains basic order information, with columns for Date, Product, Name, and Amount. The Helper column is used to create a special lookup value, as explained below. The goal is to retrieve the nth matching record in a table for a specific product, which is entered in cell I4. For example, if the value in cell H4 is “A”, the formula in I7 should return the name “John”, since this is the first name in the table associated with product “A”....

About This Shortcut This shortcut toggles the display of outline symbols on the worksheet. Outline symbols only appear if the worksheet has at least some rows or columns grouped. About This Shortcut This shortcut zooms in on the current worksheet, making items larger and easier to read. Note: you can 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 changes increases the zoom level shown in the lower right of the worksheet by 15% each time Control + is used....

Explanation Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range “things”: --ISNUMBER(SEARCH(things,B4) The SEARCH function returns the position of the value if found, and the #VALUE error if not found. For B4, the results come back in an array like this: {8;#VALUE!;#VALUE!} The ISNUMBER function changes all results to TRUE or FALSE: {TRUE;FALSE;FALSE} The double negative in front of ISNUMBER forces TRUE/FALSE to 1/0:...

Transcript It’s easy to create your own pivot table style, which you can then apply to one or more pivot tables. Let’s take a look. The easiest way to create a custom pivot table style is to first apply a built-in style and then duplicate and customize that style as needed. For example, let’s apply a medium style to this pivot table. You can check that this style is applied by selecting any cell in the pivot table and then checking the PivotTable styles group....

Transcript All pivot charts are based on pivot tables, so in order to have a pivot chart you must also have a pivot table. Let’s take a look. If you’re creating a pivot chart from scratch, first select a cell in the source data. Then go to the Insert tab and click the Pivot Table menu. From the menu, choose Pivot Chart. Similar to creating a pivot table, you’ll need to confirm the data source and the location....

Transcript In this video we’ll look at how to set up a classic two-way lookup using INDEX and MATCH . Here we have a list of salespeople with monthly sales figures. What we want to do is add a formula in Q6 that looks up and retrieves a sales number based on the name and month above. To do this, we’ll use the INDEX and MATCH functions. First, I’ll name some ranges to make the formulas easier to read....

Explanation The aim is to mark records with an “x” if a score is greater than 80 and leave the cell blank if the score is less than 80. This can be achieved using the IF function in Excel. IF function The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The generic syntax for IF looks like this:...

Transcript In this video, we’ll look at how to nest dynamic array formulas together. One of the most powerful ways to extend the functionality of dynamic array formulas is to nest one function inside another. To illustrate, let’s look at an example based on the SORT and FILTER functions. Here we have data that shows over 300 of the largest cities by population in the United States. This data is in an Excel Table called “Table1”....

Explanation Excel supports the wildcard characters “” and “?”, and these wildcards can be used to perform partial (substring) matches in various lookup formulas. However, if you use wildcards with a number, you’ll convert the numeric value to a text value. In other words, “"&99&”*" = “99” (a text string), and if you try to find a text value in a range of numbers, the match will fail. One solution is to convert the numeric values to text with the TEXT function like this:...

One of the quirks of pivot tables is that they may hold on to items that have been previously removed from the source data, even after refreshing the data. You may see these deleted “ghost” items when filtering a pivot table. In the example shown, the source data originally contained three colors: red, blue, and green. At some point after the pivot table was created, the color Green disappeared from the source data....

Explanation The goal in this example is to reformat names that appear in mixed upper and lower case letters into “proper case”, defined as each word in the name beginning with a capital letter. This can easily be done in Excel with the PROPER function. PROPER function The PROPER function automatically reformats text so that all words are capitalized. At the same time, it lowercases all other text. For example:...

About This Shortcut This shortcut will allow multiple levels of redo’s; each time you use it Excel will step forward one level. Also, with certain actions (e.g. deleting a row), Excel will perform that action again. So, in some cases you can use this shortcut to quickly perform the same action again multiple times. On Windows, F4 also works. About This Shortcut Copy puts everything on the clipboard: text, formulas, formatting, borders, fills, etc....

Explanation The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period ("."). =LEFT(filename,characters) The FIND function is used to figure out how many characters to extract: FIND(".",B5)-1 Find returns the position of the first match (6 in the first example) from which 1 is subtracted. The result, 5, goes into LEFT like this:...

Explanation The goal is to display a blank cell based on a specific condition. In the worksheet shown, we want to return the value from column C, but only when the value in column B is “A”. If the value in column B is anything else, we want to display nothing. The easiest way to solve this problem is with the IF function and an empty string (""). 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 At the core, we are using the INDEX function to retrieve the value at a given row or column number like this: =INDEX(C5:G9,row,column) The range C5:C9 defines the matrix values. What’s left is to figure out the correct row and column numbers, and for that we use the MATCH function. To get a row number for INDEX (the impact), we use: MATCH(impact,B5:B9,0) To get a column number for INDEX (the impact), we use:...

About This Shortcut This shortcut display the Save As File dialog box. About This Shortcut This shortcut displays the Print pane in the File tab in windows. On a Mac, it displays the Print dialog box.

About This Shortcut When you select a formula and use this shortcut, Excel will select cells that are directly referred to in the selected formula. The idea behind the word precedent in this case is “precede” or “before”…cells that come before. About This Shortcut When you select a formula and use this shortcut, this shortcut will select all cells that are referred to directly or indirectly by the formula(s) in your selection....

Purpose Return value Syntax =SMALL(array,k) array - An array or range of numeric values. k - Position as an integer, where 1 corresponds to the smallest value. Using the SMALL function The SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the “nth smallest” value (1st smallest value, 2nd smallest value, 3rd smallest value, etc....