The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database. Using the example above, you can get the variance of heights for the group “Fox” with either of these formulas: Criteria options The criteria can include a variety of expressions, including some wildcards....
Notice a full row reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no columns in a full row reference, the literal translation of the range 1:1 is “every cell in row 1”. Like other ranges, a full row reference can include multiple rows. For example, to reference rows 1 through 5: In the example shown above, the formula in cell C8 sums all quantities in row 5: Here, 5:5 is a range that includes all 16,384 columns in a worksheet, so it includes 16,384 cells....
To test if the value in A1 is between 5 and 10, you can use a formula like this: Logical tests in Excel formulas can be simple or quite complex, depending on the formula. Often, you will see several different logical tests in a single formula. In formulas that need to evaluate many cells at once, you will see logical tests that use Boolean logic. Here are a few examples of formulas based on logical tests:...
The LOOKUP function accepts three arguments: lookup_value, lookup_vector, and result_vector. The first argument, lookup_value, is the value to look for. The second argument, lookup_vector, is a one-row, or one-column range to search. LOOKUP assumes that lookup_vector is sorted in ascending order. The third argument, result_vector, is a one-row, or one-column range of results. Result_vector is optional. When result_vector is provided, LOOKUP locates a match in the lookup_vector, and returns the corresponding value from result_vector....
Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. In some cases (3rd example above) they can be used to create a reference that will expand when copied. Mixed references are a common feature in well-designed worksheets. They are harder to set up, but they make formulas much easier to enter. In addition, they significantly reduce errors since they allow the same formula to be copied to many cells without manual edits....
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.
On a Mac, the control key (⌃) can be used instead of the command key (⌘). 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.
On Windows, F4 also works. 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.
On Windows, you can also use Control + Y for this shortcut. You can add the “Repeat” command to the Quick Access Toolbar to see when an action can be repeated — when it’s possible to repeat an action, the command will highlight and become available. On a Mac, the number of actions that can be repeated is more limited. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
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.
When array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on. TRANSPOSE can be used with both ranges and arrays. Transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range....
XLOOKUP can find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (see match type and search mode details below). Compared to older functions like VLOOKUP, HLOOKUP, and LOOKUP, XLOOKUP offers several key advantages. Not found message When XLOOKUP can’t find a match, it returns the #N/A error, like other match functions in Excel....
where data (C4:L6) and group (C5:L5) are named ranges. The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation. The formula in B5 is: Working from the inside out, the include argument for FILTER is a logical expression: When the logical expression is evaluated, it returns an array of 10 TRUE and FALSE values: Note: the commas (,) in this array indicate columns....
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 Author 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....
One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest information. To illustrate, I’ll add some formulas to this worksheet to report on the size of this table. The first formula counts rows with the ROWS function. The second formula counts columns with the COLUMNS function....
Let’s take a look. Here we have a pivot table that shows sales by Product. If we wanted to filter the entire pivot table by Region, we know from an earlier video that we could just add Region as a Report Filter, and use it to filter as needed. Slicers work the same way as Report Filters, but they are easier to use. To show how slicers work, let’s add a slicer for Region and try it out....
When plotting unsummarized data, the chart filter may not be convenient. For example, here we have historical stock data for Microsoft covering more than 15 years in more than 100 rows of data. If I insert a line chart, Excel automatically builds a condensed horizontal axis and plots all the data. And I can easily use the Chart Filters menu to exclude everything but the close price. But what if I only want to show data from 2016 and 2017?...
No matter how many rows you add or delete, the number of rows in the worksheet never changes. When you insert rows, rows are pushed off the worksheet at the bottom. When you delete rows, new rows are added to the bottom. Let’s take a look. To insert a row in Excel, first select the row below where you want the new row to be. Then, click the Insert button on the ribbon....
There are two special functions, AND and OR, that make this easy to do. Let’s take a look. In this first worksheet, we have a list of employees. Let’s assume that you need to group these employees into two groups. Group A includes employees in Sales and Marketing and group B includes employees in Fulfillment, Support, or Engineering. While this could be done using nested IF statements, an easier way to calculate these groups is to use the IF function together with the OR function....
where “data” is the named range B5:B16. The formula is then copied to rows below D5 to output the desired list of most frequent numbers. Note: this is an array formula and must be entered with control + shift + enter. Working from the inside out: In cell D5, no filtering occurs and the output of each step above looks like this: In cell D6, with 93 already in D5, the output looks like this:...
