Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
The SORTBY function takes three primary arguments: array, by_array, and sort_order. The first argument, array, is the range or array to be sorted. The second argument, by_array, contains the values to be used for sorting. These values can come from an existing range, or from an array created by a formula. Note that by_array values do not need to be part of the source data, and do not need to appear in the output....
Filtering to extract data based on matching criteria is a traditionally hard problem in Excel. However, the new FILTER function makes this task much easier. The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains names, scores, and groups. Our goal is to use the FILTER function to filter the data by group. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “filter”....
The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains an order number, amount, name, and state. Our goal is to use the FILTER function to extract orders from Texas with an amount of at least $100. To explain how this logic works, I’m going to test the criteria first in a helper column, then copy it into the FILTER function....
Here we have data in an Excel Table called “data”. In cell J2, I’ll set up a dropdown list we can use to filter data by color. First, I’ll type “Red” in J2, so we have something to filter on. Next, I’ll enter the FILTER function in cell I5. For array, we want the full table. For the include argument, we use an expression to compare values in the color column to cell J2....
In the example shown, the formula in cell F5 is: where data is a named range for B5:D10 {5;6;7;8;9;10} If you want only the first row number, you can use the MIN function to extract just the first row number, which will be the lowest number in the array. Simple version Entered in a single cell, the ROW function will display only the first row number, even though it returns an array....
The idea is to enter a new weight each day, and calculate the difference from the previous day. When every day has an entry, the formula is straightforward: The difference is calculated with a formula like this, entered in D6, and copied down the table: However, when one or more days are missed, things go awry, and the calculated result doesn’t make sense: No, you did not gain 157 pounds in one day The problem is the formula uses the blank cell in the calculation, which evaluates to zero....
To get the last day of the prior month, use: To get the last day of the next month, use: Alternative formula You can also write a formula using the DATE, YEAR and MONTH functions to return the last day of the month: The trick with this formula is supplying zero for the day. When you supply zero as the day argument to DATE, the date function will “roll back” one day to the last day of the previous month....
For example, if you have dates in the range C4:C10, and want to weekend dates, select the range C4:C10 and create a new conditional formatting rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be C5 in this case. Once you save the rule, you’ll see all dates that are a Saturday or a Sunday highlighted by your rule....
After you’ve created a chart, you can select the chart and resize manually using any one of the 8 drag handles. To constrain the chart to the cell grid as you drag, hold down the Alt key. You’ll then see the chart snap to the gridlines as you go. If you know what size chart you want exactly, you can double click the chart area, and enter a height and width manually in the Format Task Pane....
This is the same example we looked at previously: a table which contains first, middle, and last names. In column E, I’ll add a formula that uses the CONCATENATE function to join these names together into a full name. With the CONCATENATE function, just enter each value you want to join together as a separate argument. If I use B5 for the first argument, and D5 for the second argument, we’ll get the name “SusanBrown” without spaces....
In the example shown, we give MODE the range B4:K4, so the formula is solved like this: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
The formula in this example is purposely more verbose that necessary in order to “show” all possible options and results in a way that is easier to understand and maintain. The trick is to structure the formula with line breaks to show each IF on a separate line along with the “true result” for that IF. The “false result” is the following IF statement. Notice the final false result will “catch” any case that fails all previous tests....
Data The data in this pivot tables comes from the Excel Table in column B. Excel Tables are dynamic and will automatically expand and contract as values are added or removed. This allows the Pivot Table to always show the latest list of unique values (after refresh). Fields The pivot table shown in the example has just one field in the row area, as seen below. Steps Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
In this example, an annuity pays 10,000 per year for the next 25 years, with an interest rate (discount rate) of 7%. The PV function is configured as follows in cell C9: The inputs to PV are as follows: rate - the value from cell C7, 7%. nper - the value from cell C8, 25. pmt - the value from cell C6, 100000. fv - 0. type - 0, payment at end of period (regular annuity)....
which returns 10 random values from the named range names (B5:B104). However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this: The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result: In either case, RANDARRAY will return 10 numbers in an array that looks like this: Note: these numbers are random only and do not map directly to the example shown....
where data is the named range B5:B16. Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel. where data is the named range B5:B16. Background study Below are related links to help you understand how this formula works: What is an array formula? - 3 min video What is an array? - 3 min video COUNTIF function Working from the inside-out, the core of the formula is based on the COUNTIF function: Here, data (B5:B16) is given for both range and criteria....
where “data” is the named range B5:B10. The results can be seen in the range D10:F10, which correctly contains 10, 15, and 20. However, if we wrap the formula in the SUM function: The final result is 10, while it should be 45, even if entered as an array formula. The problem is that INDEX only returns the first item in the array to the SUM function. To force INDEX to return multiple items to SUM, you can wrap the array constant in the N and IF functions like this: which returns a correct result of 45....
The value in B6 is 17 and the result is 15 since 15 is the nearest multiple of 5 to 17. Other multiples As you’d expect, you can use MROUND to round to other multiples as well: And so on. Force up or down You can use the CEILING function to force rounding up to the nearest multiple and the FLOOR function to force rounding down to the nearest multiple....
Let’s take a look. Normally, to add the same value to a group of cells, you’d add it to one cell, copy the value, then paste. Or, you could use the fill handle. But there’s a faster way to do it. Just select all the cells from the start and type the data you want to enter. Then, while holding down the control key, press the enter key. The value is then entered into all cells at once....
