Explanation In the example shown, we have a set of order data that includes Date, Product, Name, and Amount. The data is sorted by date in ascending order. The goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. The challenge is that Excel lookup formulas will return the first match by default. There are several ways to approach this problem....

Explanation In this example, the goal is to determine the percentage discount for each item shown in the table, given an original price and a sale price. In other words, given the Charcoal grill has an original price of $70.00 and a Sale Price of $59.50, we want to calculate a percentage discount of 15%, based on a price decrease of $10.50. Note that a discount of 15% represents the price change ($10....

Explanation In this example, the goal is to get the last value in column B, even when data may contain empty cells. A secondary goal is to get the corresponding value in column C. This is useful for analyzing datasets where the most recent or last entry is significant. In the current version of Excel, a good way to solve this problem is with the XLOOKUP function. In older versions of Excel, you can use the LOOKUP function....

Explanation When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12, and the reference to B4 will change to the address of each cell being evaluated, since it is a relative address. The formula itself uses the COUNTIF function to “count” cells that end with “ota” using the pattern “ota” which uses a wildcard () to match any sequence of characters followed by “ota”....

Explanation Conditional formatting is evaluated for each cell in the range, relative to the upper left cell in the selection. In this case, the formula uses the MOD function to check the remainder of dividing the value in each cell, with the value in cell E2, which is 9. When the remainder is zero, we know that the value is an even multiple of the number 9, so the formula checks the result of MOD against zero....

Transcript In this lesson we’ll look at how to align text across multiple columns or rows by merging cells. Let’s take a look. Here we have a simple table meant to summarize the features of a hypothetical service plan. One thing you might want to do with a table like this is combine cells with like values. For example, Feature Y is not offered in Plans A, B, or C, and feature Z is not applicable to Plan A or B....

Download the practice worksheet here: How to create 3d references Practice.xlsx This is part of a 3-part series on Excel formulas. Video 1: 5 ways to use VLOOKUP Video 2: 23 tips to work faster with formulas today Video 3: How to learn Excel formulas and functions Sample video lessons: How to use IF with AND and OR How to create 3d references <- this video Got a question or a comment?...

Transcript In this lesson, we’ll look at how to enter times in Excel. As with dates, the key to entering a time in Excel is to enter it in a format that Excel will recognize as a time. When checking for a time, Excel will look for hours, minutes, seconds, and the AM or PM designation. Let’s take a look. You can, of course, enter a time with all components. For example, 7:00 PM can be entered like this: 7:00:00 PM...

Transcript In this lesson, we’ll look at how to move and copy worksheets in the same workbook. This allows you to put worksheets into a sequence that makes sense and to duplicate worksheets when needed. Let’s take a look. Here we have a workbook that contains several sheets. Let’s rearrange these worksheets into alphabetical order. To move a worksheet, just click on the tab with your mouse and drag the sheet to a new location....

Transcript In many cases, you’ll want to sort pivot table items by values instead of labels. For example, you might want to sort products by total sales, with the best-selling products listed first. This is easily done. Let’s take a look. Let’s quickly build a pivot table that shows total sales and order count by product. As usual, products are listed in alphabetical order by default. To sort a pivot table by value, just select a value in the column and sort as you would any Excel Table....

Transcript In this video, we’ll look at how data series work in Excel charts. A row or column of related numbers plotted in a chart is called a data series. Excel can plot data arranged in rows or columns. When you create a new chart, Excel makes a decision about data series. When the data contains more rows than columns, Excel uses columns for the data series. When the data contains more columns than rows, Excel uses rows for the data series....

Explanation Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (&) and a single space between each value: B5&" "&C5&" "&D5&" "&E5&" "&F5 This part of the formula is annoyingly manual. To speed things up, copy &" “& to the clipboard before you start. Then follow this pattern: [click] [paste] [click] [paste] [click] [paste] until you get to the last cell reference....

Explanation This formula is designed to be copied throughout the interior of the multiplication table without change. In other words, when the formula is copied to other cells in the table, the references will automatically update as needed to calculate the product of the corresponding row and column. In $B5, the column is “locked” so that it won’t change, and in C$4, the row is locked. As the formula is copied, this is what the references look like for the first 5 rows and columns:...

Explanation Important: This formula assumes that units are the last 2 characters of the text value that includes both a number and a unit of measure. This formula works because digital units have a “power of 10” relationship. At the core, this formula separates the number part of the size from the unit, then divides the number by the appropriate divisor to normalize to Gigabytes. The divisor is calculated as a power of 10, so the formula reduces to this:...

Purpose Return value Syntax =OCT2BIN(number,[places]) number - The octal number you want to convert. places - [optional] The number of characters to use. If omitted, the function uses the minimum number of characters necessary. Using the OCT2BIN function The OCT2BIN function is used to convert octal numbers (base 8) to binary numbers (base 2). This is useful when working with different number systems, especially in engineering and computer science applications....

A Pivot Table is a special tool in Excel for summarizing data without formulas. The Pivot Table interface behaves like a report generator, allowing you to interactively add and remove fields as you like. The screen below shows the how fields have been configured to build the pivot table shown above. When data changes, you can simply refresh the pivot table to see a new summary. Pivot tables automatically group data using field values....

Explanation In this example, the goal is to replace the comma-separated values in column B with the line break-separated values seen in column D. In a problem like this, the first step is to identify the delimiter , which is the character (or characters) that separate each value we want to process. In this case, the values in column B are separated by commas, so a comma (",") is the delimiter....

Explanation In this example, the goal is to split the text strings in column B, which contain three dimensions in the form “L x W x H”, into 3 separate dimensions. One problem with dimensions entered as text is that they can’t be used for any kind of calculation. So, in addition, we want our final dimensions to be numeric . In a problem like this, we need to identify the delimiter, which is the character (or characters), that separate each thing we want to extract....

Purpose Return value Syntax =STANDARDIZE(x,mean,standard_dev) x - The value to normalize. mean - The arithmetic mean of the distribution. standard_dev - The standard deviation of the distribution. Using the STANDARDIZE function The Excel STANDARDIZE function returns a normalized value (z-score) based on the mean and standard deviation. To use the STANDARDIZE function, calculate the mean with the AVERAGE function , and the standard deviation with the STDEV.P function (see below)....

Explanation In this example, the goal is to sum every nth value by column in a range of data, as seen in the worksheet above. For example, if n =2, we want to sum every second value by column, if n =3, we want to sum every third value by column, and so on. All data is in the range B5:J16 and n is entered into cell K2 as 3. The value for n can be changed at any time....