Transcript In this lesson we’ll look at the Scientific format. The Scientific format is used to display numbers in scientific or exponential notation. Scientific notation is a compact way to display values in the same column, even when they vary greatly in size. Let’s take a look. In column C of our table we have a set of very large and small numbers in Number format. Let’s first copy these numbers to the rest of our table....

Explanation The goal is to display a checkmark (also called a “tick mark” in British English) when a task is marked complete. The easiest way to do this is with the IF function and the mark you would like to display. The article below explains several options. IF with a plain checkmark The simplest approach, and the one that appears in the example shown, is to use a plain text checkmark like this:...

Explanation The core of this formula is the MODE function, which returns the most frequently occurring number in a range or array. The rest of the formula just constructs a filtered array for MODE to use in each row. The expanding range $D$4:D4 works to exclude numbers already output in $D$4:D4. Working from the inside out: The MATCH checks all numbers in the named range “data” against existing numbers in the expanding range $D$4:D4 ISNUMBER converts matched values to TRUE and non-matched values to FALSE 1-NUMBER reverses the array, and the math operation outputs ones and zeros IF uses the array output of #3 above to filter the original list of values, excluding numbers already in $D$4:D4 The MODE function returns the most frequent number in the array output in step #4 In cell D5, no filtering occurs and the output of each step above looks like this:...

Explanation In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like “Neptune” is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data (C5:F16) are named ranges . Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....

Explanation Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated, except for the code appended at the end to represent version (“CA”, “CB”, “CC”, “CD”, etc.). For a given file, we want to locate the position (row number) for the last revision. This is a tricky problem because the version codes at the end of the file names make it harder to match the file name....

Explanation The PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to have 100,000 at the end of 10 years, with an interest rate of 5%....

Purpose Return value Syntax =POWER(number,power) number - Number to raise to a power. power - Power to raise number to (the exponent). Using the POWER function The POWER function returns a number raised to a given power. POWER is an alternative to the exponent operator (^) in a math equation. The POWER function takes two arguments: number and power . Number should be a numeric value, provided as a hardcoded constant or as a cell reference....

About This Shortcut Select the current array, which is the array that the active cell belongs to. This shortcut is useful for editing multi-cell array formulas . About This Shortcut This shortcut will select all cells that don’t match the value in the active cell in a row or selection within a row. Note that this shortcut depends on the location of the active cell in a selection. You may want to start your selection with the active cell or move the active cell after the selection is made....

Transcript In this video, we’ll cover shortcuts for hiding and unhiding rows and columns. To hide columns, use Control + 0. You can also work with multiple columns at the same time. To unhide columns again, make a selection that spans the hidden columns, and use Control Shift 0. To hide rows, use Control + 9. Again, you’ll see a visual indication in row numbers to indicate that rows are hidden....

Purpose Return value Syntax =SUMIF(range,criteria,[sum_range]) range - Range to apply criteria to. criteria - Criteria to apply. sum_range - [optional] Range to sum. If omitted, cells in range are summed. Using the SUMIF function The SUMIF function adds up numbers in Excel when they meet a specific condition. It’s one of Excel’s most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional sums based on dates, text, or numbers....

Purpose Return value Syntax =SUMXMY2(array_x,array_y) array_x - The first range or array containing numeric values. array_y - The second range or array containing numeric values. Using the SUMXMY2 function The Excel SUMXMY2 function returns the sum of squares of differences between corresponding values in two arrays. The “m” in the function name stands for “minus”, as in “sum x minus y squared”. SUMXMY2 takes two arguments , array_x and array_y ....

Transcript Hi everyone, my name is Dave Bruns from Exceljet and I want to welcome you to Core Excel. Let me take you through some of the material covered in the course. Core Excel begins with the basics. We cover the Excel interface, the concept of workbooks and worksheets, and many tricks for selecting cells and entering data. We also look at Excel’s powerful find and replace tools. Then we get you started with formulas and cell references....

Explanation The goal is to look up the Title, Author, and Year in the list of books as shown using a formula based on a partial match and a wildcard. The text string to search for is entered in cell G4. All data is in an Excel Table named data in the range B5:D16. This problem can be easily solved with the XLOOKUP function or the VLOOKUP function. Both methods are explained below....

Explanation In this example, the goal is to write a formula that will return TRUE if a year is a leap year and FALSE if not. This is a surprisingly challenging problem in Excel for two reasons: (1) Excel thinks 1900 is a leap year due to a long-standing bug inherited from Lotus 1-2-3 and (2) The logic for testing a leap year is not intuitive and requires some understanding of the history of the Gregorian calendar system we use today....

Shortcut Windows Mac Create new workbook Ctrl N ⌘ N Open workbook Ctrl O ⌘ O Save workbook Ctrl S ⌘ S Save as F12 ⌘ ⇧ S Print file Ctrl P ⌘ P Open print preview window Ctrl F2 Close current workbook Ctrl W ⌘ W Close Excel Alt F4 ⌘ Q Shortcut Windows Mac Expand or collapse ribbon Ctrl F1 ⌘ ⌥ R Activate access keys Alt Move to next ribbon control Tab Tab Activate or open selected control Space Space Confirm control change Enter Return Get help on selected control F1 Shortcut Windows Mac Open options Alt F T ⌘ , Open help F1 ⌘ / Undo last action Ctrl Z ⌘ Z Redo last action Ctrl Y ⌘ Y Copy selected cells Ctrl C ⌘ C Repeat last action F4 ⌘ Y Cut selected cells Ctrl X ⌘ X Paste content from clipboard Ctrl V ⌘ V Paste values Ctrl Shift V ⌘ ⇧ V Display the Paste Special dialog box Ctrl Alt V ⌘ ⌃ V Display find and replace Ctrl F ⌘ F Display find and replace, replace selected Ctrl H ⌃ H Find next match Shift F4 ⌘ G Find previous match Ctrl Shift F4 ⌘ ⇧ G Create embedded chart Alt F1 Fn ⌥ F1 Create chart in new worksheet F11 Fn F11 Shortcut Windows Mac Insert table Ctrl T ⌘ T Toggle Autofilter Ctrl Shift L ⌘ ⇧ F Activate filter Alt ↓ ⌥ ↓ Select table row Shift Space ⇧ Space Select table column Ctrl Space ⌃ Space Select table Ctrl A ⌘ A Clear slicer filter Alt C ⌥ C Toggle table total row Ctrl Shift T ⌃ ⇧ T Shortcut Windows Mac Drag and cut drag drag Drag and copy Ctrl drag ⌥ drag Drag and insert Shift drag ⇧ drag Drag and insert copy Ctrl Shift drag ⌥ ⇧ drag Drag to worksheet Alt drag ⌘ drag Drag to duplicate worksheet Ctrl drag ⌥ drag Shortcut Windows Mac Move one cell right → → Move one cell left ← ← Move one cell up ↑ ↑ Move one cell down ↓ ↓ Move one screen right Alt PgDn Fn ⌥ ↓ Move one screen left Alt PgUp Fn ⌥ ↑ Move one screen up PgUp Fn ↑ Move one screen down PgDn Fn ↓ Move to right edge of data region Ctrl → ⌘ → Move to left edge of data region Ctrl ← ⌘ ← Move to top edge of data region Ctrl ↑ ⌘ ↑ Move to bottom edge of data region Ctrl ↓ ⌘ ↓ Move to beginning of row Home Fn ← Move to last cell in worksheet Ctrl End Fn ⌃ → Move to first cell in worksheet Ctrl Home Fn ⌃ ← Turn End mode on End Fn → Go back to hyperlink Ctrl G Enter ⌃ G Return Shortcut Windows Mac Select entire row Shift Space ⇧ Space Select entire column Ctrl Space ⌃ Space Select entire worksheet Ctrl A ⌘ A Add adjacent cells to selection Shift Click ⇧ Click Add non-adjacent cells to selection Ctrl Click ⌘ Click Same selection in next column ⌥ Tab Same selection in previous column ⌥ ⇧ Tab Move right between non-adjacent selections Ctrl Alt → ⌃ ⌥ → Move left between non-adjacent selections Ctrl Alt ← ⌃ ⌥ ← Toggle add to selection mode Shift F8 Fn ⇧ F8 Cancel selection Esc Esc Shortcut Windows Mac Select active cell only Shift Backspace ⇧ Delete Show the active cell on worksheet Ctrl Backspace ⌃ Delete Move active cell clockwise in selection Ctrl ....

Purpose Return value Syntax =ACOTH(number) number - The number to get the inverse hyperbolic cotangent of. Using the ACOTH function The Excel ACOTH function returns the inverse hyperbolic cotangent of a number. Given 2 as input, the function returns 0.549306144 as output. =ACOTH(2) // returns 0.549306144 Explanation The hyperbolic arc cotangent function is the inverse of the COTH function. =ACOTH(COTH(x)) // returns x when x ≠ 0 The plot below shows the ACOTH function’s output in Excel....

About This Shortcut This shortcut will apply the Currency format with two decimal places. Excel offers many types of number formatting . About This Shortcut This shortcut will apply the Percentage format with no decimal places. Excel offers many types of number formatting .

Transcript In this video, we’ll look at how to use Boolean algebra in array formulas for AND and OR logic. In an earlier video, I showed how AND logic corresponds to multiplication and OR logic corresponds to addition. Let’s look at how to apply this in an array formula. In the first worksheet, we want to sum orders where the state is Texas or “tx” and the amount is greater than 125....

Explanation Dates in Excel are just serial numbers that begin on January 1, 1900. If you enter 1/1/1900 in Excel, and format the result with the “General” number format , you’ll see the number 1. This means that you can easily calculate the days between two dates by subtracting the earlier date from the later date. In the example shown, the formula is solved like this: =C5-B5 =42735-42370 =365 Days remaining from today If you need to calculate days remaining from today, use the TODAY function like so:...

Explanation Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the RATE function is to calculate the periodic interest rate when the amount, number of payment periods, and payment amount are known. In this example, we want to calculate the annual interest rate for 5-year, $5000 loan, and with monthly payments of $93....