Transcript In this video, we’ll look at how to create a scatter plot, sometimes called an XY scatter chart, in Excel. A scatter plot or scatter chart is a chart used to show the relationship between two quantitative variables. A scatter chart has a horizontal and vertical axis, and both axes are value axes designed to plot numeric data. On this worksheet, we have the height and weight for 10 high school football players....

Transcript In this lesson, we’ll look at several ways to delete data in a worksheet. The most common way to remove data is to use the delete key, or, to use the Clear button on the ribbon. However, you can also remove data by deleting columns, rows, or cells. Let’s take a look. One way to remove data in Excel is to use the Clear button on the home ribbon. Choose “Clear Contents” to clear just the contents....

Transcript There are many ways to filter information in a pivot table, but the report filter is the most basic tool to limit information in a pivot table globally. Let’s take a look. By default, a new pivot table will summarize all of the source data. However, the report filter gives you a way to quickly limit the information that appears in the table by filtering on one or more fields....

Transcript When you’re looking for things in an Excel worksheet, you aren’t restricted to looking for values only; you can also search for cells that are formatted in a certain way. Let’s take a look. Here’s the table of names we looked at previously. Let’s add some formatting, then look at how to find this formatting. Let’s fill several cells that contain the name Ann with yellow. Let’s also bold two of these cells....

Explanation About the #DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors when data is being entered, but is not yet complete. For example, a cell in the worksheet is blank because data is not yet available....

What is Scroll Lock? Usually, the arrow keys will move you one cell at a time in whatever direction you wish. However, when Scroll Lock is enabled, the worksheet is “scrolled” instead. The up and down arrow keys scroll one row up and down, and the right and left arrow keys scroll one column right and left. The active cell never changes. If you don’t understand what’s going on, this can be quite distressing :)...

Transcript In this lesson we’ll look at the Time format. Like the Date format, the Time format includes a number of built-in options for displaying time. Let’s take a look. Here we have a set of times in column B of our table. Let’s start off by copying these times to all columns, then adjust formats to match those shown in the table header. Let’s look first at the default time format in column C....

Explanation Working from the inside out, we use MATCH to locate the relative position of the last entry in column C: MATCH(9.99E+307,C5:C100) Basically, we are giving MATCH a “big number” it will never find in approximate match mode. In this mode, MATCH will “step back” to the last numeric value. Note: this works in this case because all values in C are numeric, and there are no blanks. For other situations (text values, etc....

Purpose Return value Syntax =IMLOG10(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMLOG10 function The Excel IMLOG10 function returns the base-10 logarithm of a complex number. For example, given the number “0-100i” as input, the function returns “2-0.682188176920921i” as output. =IMLOG10("0-100i") // returns 2 - 0.682188176920921i The real part of the output is equal to the logarithm (base 10) of the complex number’s magnitude. =LOG10(IMABS("0-100i")) // returns 2 The imaginary part of the output is equal to the angle (in radians) of the complex number divided by the natural logarithm of 10....

Purpose Return value Syntax =IMSIN(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMSIN function The Excel IMSIN function returns the sine of a complex number. For instance, given “1+1i” as input, the function returns the complex number equal to the sine of the input. =IMSIN("1+1i") // returns 1.29845758141598+0.634963914784736i Given real number input, the function behaves like the sine function. For example, given π + 0i as input the function returns 3....

Purpose Return value Syntax =IMSUM(inumber1,[inumber2],...) inumber1 - Complex number 1. inumber2 - [optional] Complex number 2. Using the IMSUM function The Excel IMSUM function returns the sum of two or more complex numbers. For example: =IMSUM("4+3i","2-5i") // returns "6-2i" 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 IMSUM function takes in two or more arguments in the form inumber1 , inumber2 , inumber3, and so on....

Purpose Return value Syntax =ISERR(value) value - The value to check for any error but #N/A. Using the ISERR function The ISERR function returns TRUE for any error type except the #N/A error. This includes #VALUE! , #REF! , #DIV/0! , #NUM! , #NAME? , #NULL! , #CALC! , and #SPILL! errors. The ISERR function takes one argument, value , which is typically a cell reference. Examples ISERR will return TRUE if A1 contains any error except #N/A:...

About This Shortcut This shortcut will move to the next control in the dialog box. About This Shortcut This shortcut will move to the previous control in the dialog box.

In Excel, the Name Box refers to an input box directly to the left of the formula bar . The Name Box normally displays the address of the “active cell” on the worksheet. You can also use the name box to quickly create a named range. Another use for the Name Box is to navigate quickly to any range in a worksheet. If you type Z100 into the Name Box, the active cell will move to that address....

Purpose Return value Syntax =NOT(logical) logical - A value or logical expression that can be evaluated as TRUE or FALSE. Using the NOT function The NOT function returns the opposite of a given logical or Boolean value. Use the NOT function to reverse a Boolean value or the result of a logical expression . When given FALSE, NOT returns TRUE. When given TRUE, NOT returns FALSE. The NOT function is commonly used with other functions like IF, AND, and OR to create complex logical tests....

About This Shortcut This shortcut displays the Insert Function dialog box on Windows and the Formula Builder dialog box on a Mac. About This Shortcut If you select just the cells you’d like to sum, SUM formula(s) are placed below the selection. If you select empty cells to the right and Excel will place SUM to the right and SUM horizontally. If you select empty cells below and to the right, Excel will add SUM formulas to cells below and to the right....

Explanation The PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. An annuity is a series of equal cash flows, spaced equally in time In this example, an annuity pays 10,000 per year for the next 25 years, with an interest rate (discount rate) of 7%....

Explanation The Pythagorean theorem is a key principle in Euclidean geometry. It states that the square of the longest side of a right triangle (the hypotenuse) is equal to the sum of the squares of the other two sides. The theorem is written as an equation like this: a 2 + b 2 = c 2 When any two sides are know, this equation can be used to solve for the third side....

Explanation In this example, the goal is to calculate a sum of the values in a range that are generated with a formula. In other words, we want to sum values in a range while ignoring the values that have been entered manually. In the context of this example, the hardcoded values in C5:C12 represent actual sales values and the values in the range C13:C16 represent forecasted values. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below....

Purpose Return value Syntax =TAN(number) number - The angle in radians for which you want the tangent. Using the TAN function The TAN function returns the tangent of an angle provided in radians . For example, given the π/4 as input, the function returns 1.0 as output. =TAN(PI()/4) // Returns 1 To supply an angle in degrees, use the radians function. =TAN(RADIANS(45)) // Returns 1 Explanation In math, the tangent of an angle is defined as the ratio of the opposite side to the adjacent side of a right triangle....