Transcript In this video we’re going to look at how to create a dynamic named range using the OFFSET function . To create a dynamic named range that refers to this data using the OFFSET function, first identify the first cell of the data in the upper left. In this case, that’s cell B6. To create a named range , we’re going to use the Name Manager. However, it will be easier to enter the formula using the formula bar, so I’ll start by entering the OFFSET function in cell K6, and then use that formula to create a named range in the next step....

Transcript In this lesson we’ll introduce the concept of filtering. Filtering is a great way to quickly explore data in a list or table. Filtering has some similarities to conditional formatting; but, instead of highlighting data of interest, filtering hides data that is not of interest. Let’s take a look. To filter data in Excel, first select a cell anywhere in the list or table you’d like to filter; then, click the Filter button on the Data tab of the ribbon....

Transcript In this video, we’re going to look at three ways to find formulas in a worksheet. Knowing where formulas are is the first step in understanding how a spreadsheet works. When you first open a worksheet you didn’t create yourself, it may not be clear exactly where the formulas are. Of course, you can just start selecting cells, while watching the formula bar, but there are several faster ways to find all formulas at once....

Grouping data with pivot tables One of the most powerful features of pivot tables is their ability to group data. Any field added as a row or column label is automatically grouped by the values that appear in that field. For example, you might use a pivot table to group a list of employees by department. In this case, with the department field added as a row label, the pivot table neatly breaks out a count of employees by department, with a new row for each department that appears in the source data....

Transcript In this video, we’ll look at how to make a stacked area chart. Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. For example, here we have sales data for an eclectic website with 4 product lines: banjos, hammocks, cycling bags, and organic flannel. In this case, the four product lines have a part-to-whole relationship - together, they represent total sales....

Transcript In this video, we’ll look at a way to use basic formulas to randomly assign people to teams. Here we have a list of 36 people. Let’s say we want to randomly assign each person to a team of 4 people so that we have a total of 9 teams with 4 people in each. I’m going to solve this problem in small steps, with helper columns, then bring things together in the end....

Transcript In this video, I’ll show you how to quickly test your conditional formatting rules with dummy formulas. When you apply conditional formatting with formulas, it can be hard to get the formulas to work properly, because you can’t see what happens to the formula when the rule is applied. You can think of conditional formatting as an “overlay” of invisible formulas that sit on top of the cells. When a formula in the overlay returns TRUE for a given cell, the formatting is applied....

If you want to master Excel keyboard shortcuts on a Mac, you need to take a moment to understand how the Mac keyboard is arranged, and how it can be configured through system preferences. This is especially important with Excel, which uses several function keys for shortcuts. Function Keys Modern Mac computers using an Apple keyboard have icons printed on some of the keys on the top row of the keyboard....

Transcript In this video we’ll look at how to use the VLOOKUP function with wildcards. This is useful when you want to allow a lookup based on a partial match. Let’s take a look. Here we have the employee list we’ve looked at previously. This time, however, notice that the ID column has been moved into the data. It’s no longer the first column in the data, so we can’t use it to find values with VLOOKUP ....

Purpose Return value Syntax =IMPRODUCT(inumber1,[inumber2],...) inumber1 - Complex number 1. inumber2 - [optional] Complex number 2. Using the IMPRODUCT function The Excel IMPRODUCT function returns the product of one or more complex numbers. For example: =IMPRODUCT("1+2i", "3+5i") // returns -7+11i Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number. Examples The IMPRODUCT function takes in two or more arguments in the form of inumber1 , inumber2 , and so on....

Explanation This formula pulls the customer name and state from the customer table into the order table. The MATCH function is used to locate the right customer and the INDEX function is used to retrieve the data. Retrieving customer name Working from the inside out, the MATCH function is used to get a row number like this: MATCH($C5,ids,0) The lookup value comes the customer id in C5, which is a mixed reference, with the column locked, so the formula can be easily copied....

Explanation In this example, the goal is to return the greater of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MAX function. MAX function The MAX function returns the largest numeric value in the data provided. In Excel, it’s common to use the MAX function with a range like this:...

Explanation The key to this formula is the array or TRUE and FALSE values constructed with this expression: LEFT(code,1)<>"N" Here, each value in the named range “code” is evaluated with the logical test “first letter is not N”. The result is an array or TRUE and FALSE values like this: {FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE} This array is fed into the MATCH function as the lookup array. The lookup value is TRUE, and match type is set to zero to force an exact match....

Purpose Return value Syntax =MULTINOMIAL(number1,[number2],...) number1 - The first value. number2 - [optional] Additional values. Using the MULTINOMIAL function The Excel MULTINOMIAL function calculates the multinomial coefficient, which is used to determine the number of ways to assign groups of items into specified sizes. This is especially useful in combinatorics and probability, where you need to count the distinct ways to distribute items into multiple groups, regardless of the order within each group....

Explanation In this example, the goal is to answer the question “What percentage of students were absent from each class”. In other words, given a class with 30 students total, 27 of which were present, we want to return 10% absent. The general formula for this calculation, where “x” is the percent absent is: x=absent/total However, since we don’t have a column for the number of students absent in the table, we need to calculate this number as part of the formula:...

The Radar Chart is a built-in chart type in Excel. Radar charts, sometimes called spider charts , have one axis per category which all use the same scale. The axes of a radar chart radiate out from the center of the chart and data points are plotted on each axis using a common scale. The result is a geometric shape that shows “at-a-glance” performance across all categories. Radar charts can be used to plot the performance of employees, athletes, products, and companies in various categories....

Explanation The key to solving this problem is to realize that the solution requires a specific kind of rounding. We can’t just round to the “nearest” .45 or .95 value. In fact, the first step is to round up to the nearest half dollar (.50). The second step is to subtract 5 cents ($0.05). To round up to the nearest half dollar, we use the CEILING function, with the significance argument set to ....

About This Shortcut Inside a cell, this shortcut will select from the cursor to the beginning of the text. About This Shortcut Inside a cell, this shortcut will select from the cursor to the end of the text.

Transcript In this video, we’ll look at shortcuts you can use in an Excel Table. Excel Tables are one of Excel’s most powerful features for working with data. To create a table, just select a cell in the data and use Control + T. New tables have filters enabled. To toggle filters, use Ctrl-Shift-L in Windows, and use Command-Shift-F on a mac. A number of shortcuts for selecting cells take advantage of the table structure....

Explanation In this example, the goal is to subtotal (count and sum) values based on cell color. This is a tricky problem, because there is no Excel function that will let you count cells by color directly. There are several different approaches, as explained below. Standard formula logic If color is being applied based on specific rules (either with conditional formatting or manually) you may be able to use standard logic that follows the same rules to count and sum by color....