Transcript In this lesson we’ll take a look at the Date format. The Date format is flexible and can display the same date in many different ways. Let’s take a look. Here we have a set of dates in column B of our table. Let’s start off by copying these dates to all columns. Let’s look first at the Short Date and Long Date formats. We can apply both of these date formats using the Number Format menu on the home tab of the ribbon....
Transcript In this lesson, we’ll take a look at a few lesser-used horizontal alignment options. These are the options to fill, justify, and distribute content horizontally in cells. Let’s take a look. Let’s look first at the Fill option. Selecting a horizontal alignment of Fill will fill a cell with content, repeating the content as necessary. This is not very useful for ordinary content, but it can be used for other effects....
Transcript In this lesson, we’ll look at how to multiply in Excel. To multiply things in Excel, use the asterisk symbol “*” which is over the 8 key. For example, 8 times 8 is entered as = 8 * 8. Let’s take a look. As with our previous examples, we have a simple worksheet that contains instructions and several highlighted cell references. Following the instructions in the table, let’s build the formulas needed using multiplication....
Transcript In this lesson, we’ll take a quick look at how to subtract in Excel. To subtract things in Excel, use the “-” sign. 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:...
Purpose Return value Syntax =INFO(type_text) type_text - The information type to return as text. Using the INFO function The INFO function can retrieve information about the current environment, including the operating system, the operating system version, Excel version, and so on. INFO takes one argument, type_text , which is a text value indicating the type of information to be returned. Examples To use the INFO function, supply the type of information you want as text....
Purpose Return value Syntax =ISNONTEXT(value) value - The value to check. Using the ISNONTEXT function The ISNONTEXT function returns TRUE when a cell contains any value except text. This includes numbers, dates, times, errors, and formulas that return non-text results. ISNONTEXT also returns TRUE when a cell is empty. The ISNONTEXT function takes one argument , value , which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1....
Purpose Return value Syntax =ISODD(value) value - The numeric value to check. Using the ISODD function The ISODD function tests for odd numbers. ISODD takes one argument , value , which should be a numeric value or a cell reference. When value is an odd number, ISODD returns TRUE. When value is an even number, ISODD returns FALSE. If value is not numeric, ISODD will return the #VALUE error. Only the integer portion of value is evaluated, decimal values are truncated....
Purpose Return value Syntax =ISTEXT(value) value - The value to check. Using the ISTEXT function The ISTEXT function returns TRUE when a cell contains a text value , and FALSE if the cell contains any other value, or is empty. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text. The ISTEXT function takes one argument , value , which can be a cell reference, a formula, or a hardcoded value....
Explanation In this example, the goal is to join different parts of a name (first, middle, last) into a full name. This is an example of concatenation . To concatenate means to join one text value to another with a formula, or in a more general programming language. In a current version of Excel, the simplest approach is to use the TEXTJOIN function, which is a flexible function for concatenating values in Excel....
Explanation In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and list2 (D5:D12) are named ranges . The easiest way to solve this problem in Excel is with the FILTER function and the COUNTIF function, as explained below....
Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to summarize sales by year and quarter. Once the date field is grouped into years and quarters, the grouping fields can be dragged into separate areas, as seen in the example. Fields The source data contains two fields: Date, and Sales, and both are used create the pivot table, along with Years, which appears after dates are grouped:...
Explanation Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead. To pull a random value out of a list or table, we’ll need a random row number. For that, we’ll use the RANDBETWEEN function, which generates a random integer between two given values - an upper value and lower value. For the lower value, we use the number 1, and for the upper value we use the ROWS function to get count the total rows in the table or list:...
Explanation You can use the RANK function to rank numeric values. RANK has two modes of operation: ranking values where the largest value is #1 (order = 0), and ranking values where the lowest value is #1 (order = 1). In this case, we are ranking test scores, so the highest value should rank #1, so we omit the order argument, which defaults to zero: =RANK(C6,scores) The following formula, which includes order set to zero, is equivalent:...
Transcript In this video, we’ll look at shortcuts that Excel provides for applying number formatting. First, just to recap, number formats control how a number is displayed, but they have no effect on the number’s value. You can find a list of available number formats on the home tab of the ribbon. Or you can use Control + 1 (command + 1 on a Mac) to see a list of number formats there....
Explanation Background A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example: 50 ft x 200 ft 153 ft x 324 ft Etc. In a spreadsheet, it’s a lot more convenient to have actual numbers so that you can use them in calculations as you wish....
Purpose Return value Syntax =STDEVPA(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEVPA function The STDEVPA function calculates the standard deviation for data that represents an entire population, evaluating text and logicals as numbers as part of the calculation. TEXT is evaluated as zero, TRUE is evaluated as 1, and FALSE is evaluated as zero. Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers....
Explanation Excel’s formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example: =90>100 // returns FALSE ="A">100 // returns TRUE The second formula above returns TRUE when you probably expect it to return FALSE. You can see this behavior in the worksheet shown in cells D9:D11. We are comparing each value in column B to 100, and the values in these cells return TRUE because they contain text....
Purpose Return value Syntax =TODAY() Using the TODAY function The TODAY function returns the current date, and will continually update each time the worksheet is updated. Use F9 to force the worksheet to recalculate and update the value. The value returned by the TODAY function is a standard Excel date . To display the result as a date, apply a date number format . Optionally customize the number format as you like....
About This Shortcut This shortcut toggles Scroll Lock on and off. Note: the Scroll Lock status message may or may not be visible on the left side of the status bar, since it can be disabled. See How to disable Scroll Lock for a full run-down on how to turn off Scroll Lock if you don’t have an extended keyboard. About This Shortcut New in Windows Excel 2016, this shortcut toggles a full screen mode that hides both the ribbon and status bar ....
So, what are pivot tables good for? In the last video, we looked at the question Why Pivot Tables? and I focused on their speed. I showed you how a pivot table will beat even an advanced user every time. We kept score of the clicks, shortcuts, and formulas needed for both approaches and it’s not even close. If you missed that one, you should have a look . In this video, we shift from why to what , and we explore some ways you can use a pivot table with different kinds of data....
