Explanation Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and MATCH to find values to the left in a table or range. In the example shown, we are looking for the weight associated with Model H in row 12. The formula in H6 is: =XLOOKUP(H4,E5:E14,B5:B14) The lookup_value comes from cell H4 The lookup_array is the range E5:E14, which contains Model The return_array is B5:B14, which contains Weight The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last) Lookup multiple values XLOOKUP can return more than one value at a time from the same matching record....

Transcript In this video, we’ll look at how to apply conditional formatting to one cell based on values in another, using a formula. Let’s take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average test scores in column H that are below 80, I can just select the cells and apply a Conditional Formatting rule with that logic....

Explanation The goal is to count the number of cells in a given range, regardless of whether the cells are empty or not. Although Excel has several functions designed to count cells based on their contents, there is no built-in function for counting the total number of cells in a range. The classic solution is to use a formula based on the ROWS function and the COLUMNS function. It is also possible to force the COUNTA function to count all cells, empty or not....

Explanation Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you’ll need to use a more complicated formula . The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of numeric values, organized in “bins”....

Explanation The general goal is to return the first text value in a range. Specifically, we have dates in column B and some city names in column C. We want a formula to find the first city listed in the range C5:C16. Because some cells in C5:C16 are empty, and some contain zeros, we need to ignore these cells in the process. This problem can be solved using the XLOOKUP function....

Explanation With TRUNC, no rounding takes place. The TRUNC function simply slices off the decimal part of the number with default settings. TRUNC actually takes an optional second argument to specify the precision of truncation, but when you don’t supply this optional argument, it is assumed to be zero, and truncation happens at the decimal. What about INT or ROUND? You might wonder if you can use INT function , or the ROUND function instead....

A histogram is a visual representation of the distribution of numerical data. It is a special kind of bar graph where bars represent “bins” that group together values at specific intervals. The height of each bar shows the proportion of values in that bin. Typically, there are no gaps between bins to represent continuous data. In Excel 2016+, a histogram is built-in chart type. Pros Simple to create in Excel 2016+ At-a-glance visualization of the distribution of numerical data Cons Less common chart type not as familiar to many people Built-in histogram chart type has limited options The Treemap chart is a built-in chart type in Excel 2016+....

Transcript When you bring data into Excel you sometimes end up with extra spaces and other characters that cause problems. Excel contains two functions that can help you clean things up. Let’s take a look. Here we have a list of movie titles that were copied in from some other system. You can see that there’s a problem with extra space characters. Not only are there extra spaces between words, there are also extra spaces at the beginning and end of some of the titles....

Transcript In this lesson, we’ll look at how to edit information that’s already been entered into Excel. Let’s take a look. If you want to replace information, just select a cell and start typing. Then type enter or tab to confirm. The contents of the cell will be completely replaced by the new information. If you want to revise the contents of a cell, double-click the cell and edit the content as you like....

Transcript In this video, we’ll look at how to use the SUMIFS function to sum cells that meet multiple criteria. Let’s take a look. SUMIFS has three required arguments: sum_range, criteria_range1, and criteria1 . After that, you can enter additional range and criteria pairs to add additional conditions. In the first set of tables, we’re using the named range called “number” and a named range called “color.” I’ll enter the formulas in column H to handle the conditions in column F....

Purpose Return value Syntax =ISFORMULA(reference) reference - Reference to cell or cell range. Using the ISFORMULA function The ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula’s output or error conditions. The ISFORMULA takes one argument , reference , which must be a cell reference. Examples If cell A1 contains the formula =2+2, the ISFORMULA function returns TRUE:...

Explanation In this example, the goal is to get the maximum quiz score (i.e. the best quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MAX function or the LARGE function, as explained below. MAX function The MAX function accepts one or more arguments , which can be a mix of constants, cell references, and ranges ....

Explanation You can use the RANK function to rank numeric values. RANK has two modes of operation: ranking values so that the largest value is ranked #1 (order = 0), and ranking values so that the smallest value is #1 (order = 1). In this case, we are ranking race times. The lowest/fastest value should rank #1, so we set the order argument to 1: =RANK(C6,times,1) Explanation This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts....

Explanation Excel doesn’t have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same result. The formula in C5, copied down, is: =TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),"")) This looks pretty complicated but the gist is that we create an array of all characters in B5, and test each character to see if it’s a number....

Explanation In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the worksheet, one for Vendor A and one for Vendor B. Both tables are defined as the named ranges vendor_a (B5:C8) and vendor_b (B11:C14). At the core, this is a basic lookup problem, and we could use the VLOOKUP function to get the cost for a color like this:...