Posts

Explanation Note: the core idea of this formula is adapted from an example in Mike Girvin’s excellent book Control+Shift+Enter . The example shown uses several formulas, which are described below. At a high level, the MMULT function is used to compute a numeric rank in a helper column (column C), and this rank is then used by an INDEX and MATCH formula in column G to extract unique values. Ranking data values The MMULT function performs matrix multiplication and is used to assign a numeric rank to each value....

The term “spill” refers to a behavior where formulas that return multiple results “spill” these results into multiple cells automatically. This is part of " Dynamic Array " functionality. In the example shown, the formula in D5 is: =SORT(B5:B14) The SORT function returns 10 sorted results. Even though the formula is entered just once in cell D5, all 10 values “spill” into the range D5:D14 automatically. The range of results returned by a formula that spills is called a spill range ....

Our main goal is to provide high-quality, free resources for improving Excel skills. Because Excel is a huge application, this is quite a lot of work. If you would like to support Exceljet, here are some ways you can help us: Send us feedback . Recommend Exceljet to friends and colleagues. Share us on Facebook, LinkedIn, Twitter, and other social media sites. Buy a shortcut card , and leave a review on Amazon ....

Explanation In the worksheet shown, we have race results for an 800m race. The goal is to display time in minutes, seconds, and hundredths of a second (centiseconds). Dealing with times that include fractional seconds can be tricky in Excel. The default time format will only show whole seconds and it is not obvious how to display seconds in smaller units. The first step is to apply a custom number format that specifically includes placeholders for tenths, hundredths, or thousandths of a second as needed....

Purpose Return value Syntax =TRIMRANGE(range,[trim_rows],[trim_columns]) range - The range or array to be trimmed. trim_rows - [optional] How rows should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default). trim_columns - [optional] How columns should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default). Using the TRIMRANGE function The TRIMRANGE function removes empty rows and columns from the outer edges of a range of data....

Explanation In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and potentially more columns in larger sets of data. The formula in K6 is:...

Explanation The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. The formula in F5 is: =XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50 lookup_value - E5, with asterisks (*) concatenated front and back lookup_array - the named range code (B5:B15) return_array - the named range quantity (C5:C15) if_not_found - the string “no match” match_mode - provided as 2 (wildcard match) search_mode - not provided....

Explanation In this example, the goal is to list the 10 most frequently occurring text values in a range, in descending order by count, as seen in the range in E5:F14. This is an advanced formula that requires a number of nested functions. However, it is an excellent example of the power of dynamic array formulas in Excel . For convenience, data is the named range B5:B104. This range contains 100 random color names....

Explanation This formula relies on a table with columns for both the full state name and the 2-letter abbreviation. Because we are using VLOOKUP, the full name must be in the first column. For simplicity, the table has been named “states”. VLOOKUP is configured to get the lookup value from column C. The table array is the named range “states”, the column index is 2, to retrieve the abbreviation from the second column)....

Purpose Return value Syntax =ATAN2(x_num,y_num) x_num - The x coordinate of the input point. y_num - The y coordinate of the input point. Using the ATAN2 function The Excel ATAN2 function returns the arctangent from the x and y coordinates of a point. In geometric terms, the function returns the radian angle corresponding to the coordinates of the input point. If you imagine a ray starting from the origin of the coordinate system and extending outwards, every point along the ray will return the same angle value....

Transcript In this video, we’ll go over some common options for configuring a pivot chart. Here we have the chocolate sales data we looked at earlier, and a basic pivot table and chart set up on a separate sheet. When you create a pivot chart, you’ll see something called “field buttons” sitting on top of the chart area. In the upper left, you’ll see buttons for value fields, which correspond to values being plotted in the chart....

Purpose Return value Syntax =BYROW(array,function) array - The array or array to process. function - The function to apply to each row. Using the BYROW function The Excel BYROW function applies a function to each row in array and returns one result per row in a single array . The purpose of BYROW is to process data in an array or range in a “by row” fashion. For example, if BYROW is given an array with 10 rows, BYROW will return an array with 10 results....

Purpose Return value Syntax =CELL(info_type,[reference]) info_type - The type of information to return about the reference. reference - [optional] The reference from which to extract information. Using the CELL function Use the CELL function to return a wide range of information about a reference . The type of information returned is given as info_type , which must be enclosed in double quotes (""). CELL can return a cell’s address, the filename and path for a workbook, and information about the formatting used in the cell....

Purpose Return value Syntax =CHOOSECOLS(array,col_num1,[col_num2],...) array - The array to extract columns from. col_num1 - The numeric index of the first column to return. col_num2 - [optional] The numeric index of the second column to return. Using the CHOOSECOLS function The Excel CHOOSECOLS function returns specific columns from an array or range . The columns to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a column in the source array....

Explanation When date information from other systems is pasted or imported to Excel, it may not be recognized as a proper date or time. Instead, Excel may interpret this information as a text or string value only. In this example, the goal is to extract valid date and time information from a text string and convert that information into a datetime . It is important to understand that Excel dates and Excel times are numbers so at a high level, the main task is to convert a text value to the appropriate numeric value....

Explanation In this example, the goal is to count numbers that contain leading zeros. In cell E5, we have the code “009875” and we want to count how many times this code appears in the range B5:B16. The challenge is that Excel can be finicky with leading zeros. Technically, the values in B5:B16 are text , as is the value in E5. However, sometimes text values that contain numbers are converted to numeric values as they go through Excel’s calculation engine....

Explanation One of the trickier problems in Excel is to count rows in a set of data with “OR logic”. There are two basic scenarios: (1) you want to count rows where a value in a column is “x” OR “y” (2) you want to count rows where a value, “x”, exists in one column OR another. In this example, the goal is to count rows where group = “a” AND Color1 OR Color2 are “red”....

Explanation This formula counts how many values are not in range of a fixed tolerance. The variation of each value is calculated with this: ABS(data-target) Because the named range “data” contains 10 values, subtracting the target value in F4 will created an array with 10 results: {0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006} The ABS function changes any negative values to positive: {0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006} This array is compared to the fixed tolerance in F5: ABS(data-target)>tolerance The result is an array or TRUE FALSE values, and the double negative changes these to ones and zeros....

Explanation In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value using concatenation , then use the INDIRECT function to convert the text reference into a proper Excel reference....

Transcript In this video, we’ll take a look at the TEXTAFTER function. TEXTAFTER is designed to extract text that occurs after a specific “delimiter”, which can be one or more characters. Let’s look at an example. In this worksheet, we have a list of email addresses. The goal is to extract the domain portion of each email into column D. As I start to enter TEXTAFTER, Excel will match the function and I can use Tab to autocomplete....