Explanation To do this, LOOKUP is configured as follows: Lookup values are ages in column C The lookup vector is the named range “age” (F5:F8) The result vector is the named range “group” (G5:G8) With this setup, LOOKUP performs an approximate match on the numeric values in column F, and returns the associated value from column G. The LOOKUP function always performs an approximate match, with the following behavior: If LOOKUP finds an exact match in the age column, the corresponding group is returned....

Explanation The SEARCH function returns the position of the text you are looking for as a number (if it exists). Conditional formatting automatically treats any positive number as TRUE, so the rule is triggered whenever SEARCH returns a number. When SEARCH doesn’t find the text you’re looking for, it returns a #VALUE error, which conditional formatting treats as FALSE. Using the ampersand (&) we are concatenating all values in each row together and then searching the result with SEARCH....

Explanation Conditional formatting is applied to all cells in the active selection at the time a rule is created. In this case, the column references are locked to prevent columns from changing as the formula is evaluated, but the row references are relative so that row numbers are free to change. The result is a formula that applies exactly the same logic to every cell in the same row. If COUNTBLANK finds any blank cells in a given row, it returns a positive number, which Excel evaluates to TRUE, triggering the rule....

Transcript In this lesson we’ll look at how to add a title and a legend to a chart. Let’s take a look. To add a title to a chart, first, select the chart. Then go to the Layout tab under Chart Tools on the ribbon. Controls for the chart title, and other “label-type” elements are in the Label group. Click Chart Title to see the two primary options. “Centered Overlay Title” places a title on the chart in the plot area....

Transcript In this video, we’ll look at how to add and configure a Total Row to an Excel Table. All Excel Tables come with a built-in Total Row feature. The total row allows you to easily show summary calculations below a table. You can use this total row to calculate counts, sums, min and max, averages, and more. There are a couple ways you can display a total row for a table....

Transcript In this video, we’ll look at how to create a stacked column chart. A stacked column chart can show part-to-whole comparisons over time, or across categories. In a stacked column chart, data series are displayed as vertical columns, stacked one on top of the other. In this worksheet, we have quarterly sales in 4 regions: East, West, North, and South. To start off, I’ll place the cursor in any cell, click the column icon next to Recommended Charts, and select Stacked column....

Transcript In this video, we’ll look at how to change the Chart Type. Once you have a chart in Excel, you can easily change from one chart type to another. Just select the chart and click “change chart type” on the Design tab of the ribbon. Excel will open the change chart type window, where you can browse and select other chart types. Notice that Excel builds a preview for each type using the data you’ve specified in the existing chart....

Transcript

Explanation A circular reference occurs when a formula refers directly to its own cell, or refers to another cell that depends on the original cell. This creates an infinite loop that cannot be resolved. For example, if cell A1 contains a formula that refers to B1, and B1 contains a formula that refers to A1, this creates a circular reference. Circular reference errors are not like other errors in Excel in that they don’t display on the worksheet and don’t have a numeric error code....

Transcript In addition to letting you group data manually, pivot tables can also group certain kinds of data automatically. One kind of automatic grouping works on numeric data. Let’s take a look. This pivot table shows total sales and orders for the sales data we’ve looked at previously. One field we haven’t looked at yet is Quantity. If we take a quick look at the source data, we can see that quantity refers to the number of items in each order....

Transcript In this video, we’ll look at how to rank data in a pivot table. Here we have data that represents one month of sales for a camping store. You can see the store sells tents, backpacks, headlamps, and so on. In fact, after I summarize the data with a pivot table, I can get an exact list of items sold by adding the Item field to the Rows area....

Transcript By default, when you select a range of cells in Excel, hidden cells are also selected. This means that any data in hidden rows or columns is included when you copy a selection. Chances are, that’s not what you want. In this video we’ll show you how to work with visible cells only. Here we have a list of real estate properties. Let’s say we want to give this list to someone else, but without the data in columns E through I....

Transcript In this video, we’ll look more closely at how calculated columns work in Excel Tables. One of the best features of tables is called “calculated columns”. Calculated columns help you enter and maintain formulas in Excel tables. To explain how this works, let me first add a formula to this data, which is not an Excel Table. A quick way to copy down the formula is to double-click the fill handle....

Purpose Return value Syntax =IMSINH(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMSINH function The Excel IMSINH function returns the hyperbolic sine of a complex number. For example, given 1+π/2i as input, the function returns -4.10319E-15 + 1.543080635i as output. =IMSINH(COMPLEX(1, PI()/2)) // returns -4.10319E-15 + 1.543080635i When the function’s output is plotted over the complex plane, the real output along the real axis traces the shape of the SINH function ....

Purpose Return value Syntax =MATCH(lookup_value,lookup_array,[match_type]) lookup_value - The value to match in lookup_array. lookup_array - A range of cells or an array reference. match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest. Using the MATCH function The MATCH function is used to determine the position of a value in a range or array . For example, in the screenshot above, the formula in cell E6 is configured to get the position of the value in cell D6....

Explanation In this example, the goal is to generate a basic mortgage payment schedule. A mortgage payment schedule is a detailed breakdown of all the payments you will make over the life of a mortgage. It provides a chronological list of each payment, showing the amount that goes toward the principal (the loan amount), the amount that goes toward interest, and the balance that remains. The schedule provides a clear picture of how the loan will progress over time....

About This Shortcut This keyboard shortcut will cycle through every cell in a selection, moving from bottom right to top left. About This Shortcut This shortcut will move the active cell right one cell when there are multiple cells selected.

About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell below the active cell. If the active cell is non-empty, Excel will stop on the last non-empty cell below the active cell. On a Mac, the control key (⌃) can be used instead of the command key (⌘). About This Shortcut It doesn’t matter whether cells contain data or not.

About This Shortcut The last cell is at the intersection of the last row that contains data and the last column that contains data. The last cell may or may not contain data. About This Shortcut If freeze pane is enabled, this shortcut will go to the upper left cell in the current pane. If freeze pane is not enabled, this shortcut will go the cell upper left cell in the worksheet (A1)....

About This Shortcut This shortcut will move to the previous tab in the dialog box. About This Shortcut This shortcut will accept and apply the settings in the dialog box.