Thank you for your wonderful work. I’ve become an Excel expert thanks to you. Moshe Like your site in general, your excel chatbot is far and away the best, most efficient, most comprehensive, and least full-of-needless-filler-and-other-crap resource regarding Excel on the internet. Colin I just wanted to tell you how MUCH I love your newsletter. I keep going back to your emails every time I have a question about Excel, and you always have the answer....
Explanation This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a logical test based on the ISNUMBER and SEARCH functions: ISNUMBER(SEARCH("rd",B5:B14)) In brief, the SEARCH function is set up to look for the text “rd” inside the street data in B5:B14. Because this range includes 10 cells, 10 results are returned....
Purpose Return value Syntax =FILTERXML(xml,xpath) xml - Valid XML as a text string. xpath - A valid Xpath expression as a text string. Using the FILTERXML function The Excel FILTERXML function returns specific data from XML text using a specified XPath expression. XML is a text format for storing and transporting data. It is not dependent on any particular hardware or software. XML is extensible and is designed to transport data, as opposed to displaying data in a particular way....
Explanation The core of this formula is the MOD function. MOD takes a number and divisor, and returns the remainder after division, which makes it useful for formulas that need to do something every nth time. In this case, the number is created with the COLUMN function, which return the column number of cell B8, the number 2, minus 1, which is supplied as an “offset”. We use an offset, because we want to make sure we start counting at 1, regardless of the actual column number....
Purpose Return value Syntax =FORECAST(x,known_ys,known_xs) x - The x value data point to use to calculate a prediction. known_ys - The dependent array or range of data (y values). known_xs - The independent array or range of data (x values). Using the FORECAST function The FORECAST function predicts a value based on existing values along a linear trend. FORECAST calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, test scores, expenses, measurements, etc....
Explanation In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function . In older versions of Excel, you can use an alternative formula based on the MID and FIND functions. Both formula options rely on the CELL function to get a full path to the current workbook....
Explanation In this example, the goal is to highlight differences in two ranges, B2:B11 and C2:C11, using conditional formatting. To do this, we need to create a new conditional formatting rule, triggered by a formula, like this: Select the range B2:C11, starting at cell B2. Select Home > Conditional Formatting > New Rule Select “Use a formula to determine which cells to format” Enter the formula =$B2<>$C2 in the input area Set the desired format to highlight differences Save the rule 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....
Transcript To understand how Excel formats dates and times we need to look at how Excel stores dates and times. In Excel, both dates and times are just numbers. Let’s take a look. Here we have a worksheet with three tables: one for dates, one for times, and one for dates with times. Let’s look at the dates first. In Excel, all dates are numbers, and the first day in Excel’s date system is January 1, 1900....
Transcript In this video I’ll show you a way to quickly add missing data to a worksheet. It’s a simple technique using a very basic formula, and it’s a beautiful example of the power of relative cell references. Sometimes you get a set of data that isn’t complete because it’s been organized like an outline, with main headings and sub-headings appearing just once at the start of a new section....
Transcript You might build or inherit a worksheet that uses a series of nested IF statements to assign values of some kind. Many people use nested IF statements this way because the approach is easy once you get the hang of it. But nested IF statements can be difficult to maintain and debug. Let’s look at how you can use the VLOOKUP function instead. Here we have the classic problem of assigning grades to scores....
Transcript Excel has the ability to sort using custom lists, and you can use these same lists to sort your pivot tables in a custom order. Let’s take a look. Before we sort our pivot table using a custom list, let’s first review how to sort by a custom list generally. Custom lists are useful when you want to sort a list into a sequence that is not alphabetical. For example, here we have a list of four regions....
Transcript In this lesson we’ll look at how to use text orientation. Orientation allows you to rotate text in a variety of ways. Let’s take a look. Here we have a simple feature summary table. We can use orientation to spice things up a bit. Before we do that, however, let’s look at what types of orientation are available. Orientation options are in a menu in the Alignment group on the home tab of the ribbon....
Explanation The goal is to return “Small” when the value in column D is “S” and “Large” when the value in column D is “L”. In other words, if the value in column D is “S” return “Small” else return “Large”. If else in Excel The concept of “If else” in Excel is handled with the IF function . The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE....
Explanation This is a basic example of VLOOKUP in “exact match” mode. The lookup value is B6, the table is the range B5:C7, the column is 2, and the last argument, FALSE, forces VLOOKUP to do an exact match. (Read why this is necessary here ). If VLOOKUP finds a matching value, the associated cost will be retrieved from the 2nd column (column C). If no match is found, VLOOKUP will return the #N/A error....
Explanation In this example, the goal is to get the minimum value for a given group above a specific temperature. In other words, we want the min value after applying multiple criteria. The easiest way to solve this problem is with the MINIFS function. However, if you need more flexibility (for example, you need to work with arrays and not ranges), you can use the MIN function with the FILTER function....
Purpose Return value Syntax =MOD(number,divisor) number - The number to be divided. divisor - The number to divide with. Using the MOD function The MOD function returns the remainder after division. For example, MOD(3,2) returns 1, because 2 goes into 3 once, with a remainder of 1. The MOD function takes two arguments: number and divisor. Number is the number to be divided, and divisor is the number used to divide....
Explanation The MODE function is fully automatic and will return the most frequently occurring number in a set of numbers. For example: =MODE(1,2,4,4,5,5,5,6) // returns 5 In the example shown, we give MODE the range B4:K4, so the formula is solved like this: =MODE(B4:K4) =MODE({1,2,2,1,1,2,2,2,1,1}) =2 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....
Purpose Return value Syntax =N(value) value - The value to convert to a number. Using the N function Use the N function to convert value to a number. The N function takes one argument , value , which can be a cell reference, a formula result, or a hardcoded value. Values are converted as shown below. The logical values TRUE and FALSE are converted to 1 and 0, and text values are converted to zero....
Transcript In this lesson, we’ll review the concept of using a named range as an absolute reference . By default, named ranges behave like absolute references and don’t change when you copy formulas. In addition, they make your formulas easier to read. Let’s take a look. Here we have the same worksheet we looked at in an earlier lesson which tracks the hours worked and gross pay of a small team....
Purpose Return value Syntax =NETWORKDAYS(start_date,end_date,[holidays]) start_date - The start date. end_date - The end date. holidays - [optional] A list of non-work days as dates. Using the NETWORKDAYS function The NETWORKDAYS function returns the number of working days between two dates, automatically excluding weekends (Saturday and Sunday) and optionally excluding holidays provided as a list of dates. NETWORKDAYS can be used to calculate employee benefits that accrue based on days worked, the number of working days available during a project, the number of working days required to resolve a customer support issue, etc....
