Let’s take a look. To filter data in Excel, first select a cell anywhere in the list or table you’d like to filter; then, click the Filter button on the Data tab of the ribbon. You’ll know that filtering is enabled when you see the small arrows appear to the right of each column heading. Each arrow provides a drop down menu that contains tools to filter on that column....
When you create a pivot table, you have your choice of three layouts. In this video, we’ll look at each layout. Once you have a pivot table, you can change layouts using the Report layout menu, on the Design tab of the pivot table tools ribbon. By default, each new pivot table you create will use the Compact layout. But you can easily switch to Outline layout, or to Tabular layout....
One solution is a formula that uses the IF function together with the SEARCH and ISNUMBER functions. In the example shown, we have a list of email addresses, and we want to extract those that contain “abc”. In C5, the formula were using is this: If “abc” is found anywhere in cell B5, IF will return that value. If not, IF will return an empty string (""). In this formula, the logical test is this bit: This snippet will return TRUE if the the value in B5 contains “abc” and false if not....
which returns a value from the first non-blank cell, B5, C5, D5, or E5, respectively. When all cells are blank, the formula returns “no value”. The value returned when all cells are blank can be adjusted as desired. This expression is used four times in the formula shown in the example, in order to test four different cells in a particular order. The overall structure of this formula is what is called a “nested IF formula”....
In this worksheet, we have a list of the Wimbledon Men’s Singles Champions since 1968, in a table called “data”. How can we list names that appear more than once, along with a count? To start off, let’s look at how we might count the names in the data with the COUNTIF function in column H. For range, we want all the names in the source data. Now, for criteria, we want exactly the same list....
Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter They key is in understanding how FREQUENCY gathers numbers into “bins”. Each bin represents an upper limit, and generates a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number. The gist of this formula is that it creates a new bin at the end of each winning streak using the id of the subsequent loss....
where data is the named range B5:B15. Note: this formula performs a case-sensitive comparison. See notes below for other options. Note: this formula performs an exact match when both the lookup value and array values are greater than 255 characters. See below for other options. The string we are testing with in cell E5 is 373 characters as follows: Lorem ipsum dolor amet put a bird on it listicle trust fund, unicorn vaporware bicycle rights you probably haven’t heard of them mustache....
where “level” is the named range C5:C9, and “points” is the named range B5:B9. Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2. The small twist is that we add 1 to this result to override the matched result and return 3 as the row number for INDEX....
Note: this is an array formula and must be entered with control + shift + enter. Here, the formula uses the COLUMN function to get a set of “relative” column numbers for the range, explained in detail here. The result is an array like this: This array goes into the MOD function as the number argument: where L5 is the the value to use for “nth”. The MOD function returns the remainder for each column number divided by N....
This is an array formula and must be entered with control + shift + enter. This formula uses the named ranges “name” (B5:B11) and “group” (C5:C11). The logical test checks each cell in the named range “group” for the color value in E5 (red in this case). The result is an array like this: That result is used in turn to filter names from the named range “name”: For each TRUE, the name survives, for each FALSE, IF returns an empty string ("")....
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. Everyone is paid the same hourly wage, so we used an absolute reference to the hourly wage in our formulas. Let’s create a named range for the hourly rate and convert our formulas to use the named range instead of a cell address....
In the example shown, the formula in C6 is: Note: this formula assumes Excel’s default 1900 date system. In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturday’s in the future are evenly divisible by 14. The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous....
Where age (E5:E16) and amount (D5:D16) are named ranges. See below for the formulas in I6 and I7, and the formulas in H5:H7. SUMIFS function The SUMIFS function is designed to sum cells that meet multiple criteria. SUMIFS takes at least three arguments like this: Notice sum_range appears first. Additional criteria are added in range/criteria pairs like this: The formulas used to sum invoices by age in I5:I7 are as follows: Notice that criteria appear in double quotes ("")....
where data is the named range B5:B16, and each text string includes both a label and a number separated by a single space (" “). See below for more details and for the formulas used to generate the summary table in E7:F9. Total sum To sum all the numbers that appear in B5:B16, ignoring text, the formula in E5 is: Working from the inside out, the TEXTAFTER function is used to extract the numbers like this: TEXTAFTER is configured to extract the text that occurs after a single space character (” “)....
For this example, we want to find the original amount of a loan with a 4.5% interest rate, and a payment of $93.22, and a term of 60 months. The PV function is configured as follows: rate - The interest rate per period. We divide the value in C5 by 12 since 4.5% represents annual interest: nper - the number of periods comes from cell C7, 60 monthly periods in a 5 year loan....
The result is a date 60 years (720 months) from the date of birth in column C. Note: At the time of this writing, the current date is September 2, 2021. This is the date used to calculate the remaining years in column F. The date comes from column C. For months, we need the equivalent of 60 years in months. Since most people don’t know how many months are in 60 years, a nice way to do this is to embed the calculation in the formula like this: Inside the EDATE function, Excel will perform the math and return 720 directly to EDATE as the months argument....
where birthdays is the named range (B5:B104), which contains 100 random birthdays. As the formula is copied down, it returns the total count of birthdays in each month as listed. Inside SUMPRODUCT, we have this expression: On the right, the MONTH function extracts the month for each date in the named range “birthdays”. On the right, the MONTH function is used to get a number for each month name shown in the table....
where data is the named range B5:B16. COUNTIF returns 3, since there are three cells that end with “R”. Note that COUNTIF is not case-sensitive. COUNTIF function The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument: question mark (?), asterisk(), or tilde (~). A question mark (?) matches any one character and an asterisk () matches zero or more characters of any kind....
The result is 9 since there are 6 orders that are complete and 3 orders that are pending. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range: Count cells in range1 that meet criteria1....
In this example, SUMPRODUCT is configured with two arrays. The first array is the range that holds product pricing: Note the reference is absolute to prevent changes as the formula is copied to the right. This range evaluates to the following array: The second array is generated with this expression: The result of D5:D9=“x” is an array of TRUE FALSE values like this: The double negative (–) converts these TRUE FALSE values to 1s and 0s: So, inside SUMPRODUCT we have: The SUMPRODUCT function then multiplies corresponding items in each array together: and returns the sum of products, 168 in this case....
