where data (B5:G14) and headers (B4:G4) are named ranges. The result is 22, since there are 22 values that are either “z” or “c” in columns labeled “A” or “B”. Working from the inside out, note that SUMPRODUCT contains a single argument, which is composed of this expression: This expression is formed from two parts, each representing a logical test. The left part tests column headers, and the right tests values....

the CHAR function takes just one argument, number, which must be an integer between 0-255. The result from CHAR is a text value. The CHAR function was designed to operate in an ASCII/ANSI world, and only understands numbers 0-255. For extended character support on modern Unicode systems, see the UNICHAR function. CHAR can be useful when you want to specify characters in formulas or functions that are awkward or impossible to type directly....

For example, to convert the price “3 and 1/16” to an equivalent decimal value, you can use the DOLLARDE function like this: Notice first argument shows the whole dollar value on the left, and the decimal component is used to express the numerator (.01 = 1, .11 = 11, etc.). The second argument is the denominator. In the example shown, the formula column E, copied down, is: On each row, the DOLLARDE function picks up the fractional dollar notation from column C and the denominator from column D....

In the example shown, the formula in D6, copied down, is: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Examples ISERR will return TRUE if A1 contains any error except #N/A: You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found. Other error functions Excel provides a number of error-related functions, each with a different behavior: The ISERR function returns TRUE for any error type except the #N/A error. The ISERROR function returns TRUE for any error....

In addition to importing data to Excel, Power Query is designed to “transform” data. You can easily do things like remove columns or rows, rename and reorder columns, split columns, add new columns, fix date problems, join tables, and much more. The beautify of Power Query is that each step is defined separately in a query. When you “refresh” the data, all steps will be automatically repeated in exactly the same order....

Controlling rank order The rank function has two modes of operation, controlled by the order argument. To rank values where the largest value is ranked #1, set order to zero (0). For example, with the values 1-5 in the range A1:A5: Set order to zero (0) when you want to rank something like top sales, where the largest sales number should rank #1, and to set order to one (1) when you want to rank something like race results, where the shortest (fastest) time should rank #1....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Will the course work for both Windows and Mac? Yes. The videos were recorded in Excel 2010 on Windows 7, but all videos show shortcuts for both Windows and Mac, and we include material specifically for Mac users. Do you have a guarantee? Of course! If you aren’t happy with the course, just let us know within 30 days and we’ll give you a full refund. We’re always interested in your feedback (so we can make the course better), but this a no-hassle guarantee....

Will the course work for both Windows and Mac? Yes. There are some small differences in the Mac and Win interface with respect to tables, but in general this is one area in Excel where there is very good overlap between the Mac and Windows versions. The videos were recorded in Excel 2016 on Windows 10. All videos highlight shortcuts for both Windows and Mac. Do you have a guarantee? Of course!...

In the example, the active cell contains this formula: At a high level, the formula replaces the last space in the name with an asterisk “” and then uses FIND to determine the position of the asterisk in the name. The position is used to work out how many characters to extract with RIGHT. How does the function replace only the last space? This is the clever part. Buckle up, the explanation gets a bit technical....

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. To save a template based on a chart, right-click the Chart Area, then choose Save as Template from the shortcut menu....

Let’s take a look. First, let’s look at the problem. Here’s a list of numbers that need to be formatted as shown in column C. When we try to enter the values directly, we can see that Excel just strips off the leading zeros. In the case of the part code, Excel actually interprets the code as a date. The problem is that Excel is treating these values as numbers or dates....

Let’s take a look. Here we have a pivot table that shows Total Sales only. Let’s add Customer as a row label, and Region as a column label. Both of these fields contain text, so we see a standard set of filter options for labels. This includes things like Equals, Contains, Begins With, and so on. The options are the same for both fields. Now let’s remove Region, and add the Date field....

Let’s take a look. Here we have a large data table with a header row at the top. Notice that as we scroll down through the data, the headers scroll off the screen. And, if we scroll to the right, the company name is no longer visible. This makes it hard to understand the worksheet because we aren’t able to see the columns and headers that give the data meaning....

The RANDBETWEEN function is a simple function you can use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100. When I press Enter, I get a random value between those two values. So, I can just drag the fill handle down to get 10 random numbers between 1 and 100. You can extend RANDBETWEEN to do all kinds of clever things....

Let’s take a look. Before we sort our pivot table using a custom list, let’s first review how to sort by a custom list generally. Custom lists are useful when you want to sort a list into a sequence that is not alphabetical. For example, here we have a list of four regions. We can easily sort these regions alphabetically in ascending or descending order. But what if we want to sort them from West to East?...

Let’s take a look. Again, we have a simple worksheet with several highlighted cell references. Following the instructions in the table, let’s build formulas that use subtraction. The first two examples don’t require cell references and can be input directly. Don’t forget to add the equals sign: = 11 - 6 = 31 - 13 The next example is D6 minus F7. As expected, we get thirteen as the answer....

The MATCH function takes three arguments: the lookup_value, which is the value you’re looking up, the lookup_array, which is the list to look in, and match_type, which specifies exact or approximate matching. In this example, we’ll use MATCH to find exact matches, so we’ll be using 0 for match_type. In column E, I have a list of numbers from 1 to 9. I’ve already created a named range called “numbers” that refers to this list....