Explanation In this example, the goal is to construct a data validation rule that will prevent any one of a list of values from being entered. Data validation rules are triggered when a user adds or changes a cell value. One option is to use a formula to validate user input, which is the approach taken in the example shown, where the formula used to enforce the rule looks like this:...

About This Shortcut Use function with delete on a Mac to delete characters to the right of the cursor. About This Shortcut Normally, when you press the Enter key, Excel moves the cursor to the next cell. To insert a line break (i.e. a new line) inside a cell, you need to use a shortcut. Here at the steps: (1) Move the cursor where you want to break the line...

Purpose Return value Syntax =DETECTLANGUAGE(text) text - A sample of the language as a text string. Using the DETECTLANGUAGE function The DETECTLANGUAGE figures the language for a given text string. The result from DETECTLANGUAGE is a short language code indicating the language. For example, if the language is English, the result is “en”; if the language is French, the result is “fr”; if the language is Japanese, the result is “ja”, and so on....

When working with Excel formulas, a common term is “empty string”. A text string is a text value like “apple”. An empty string is a text value that has no value. Since text in Excel formulas must be enclosed in double quotes, an empty string appears as empty double quotes (""). For example, in the screenshot shown, the formula D5, copied down, is: =IF(C5>10,"x","") Here, we use the IF function to check the value in column C5....

Purpose Return value Syntax =EXACT(text1,text2) text1 - The first text string to compare. text2 - The second text string to compare. Using the EXACT function The EXACT function compares two text strings in a case-sensitive manner. If the two strings are exactly the same, EXACT returns TRUE. If the two strings are not the same (taking into account upper and lower case) EXACT returns FALSE. The EXACT function takes two arguments , text1 and text2 , which should be valid text strings ....

Explanation This formula relies on the FILTER function to retrieve data based on a logical test . The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is an expression based on the EXACT function: EXACT(B5:B15,"RED") The EXACT function compares two text strings in a case-sensitive manner. If the two strings are exactly the same, EXACT returns TRUE. If the two strings are not exactly the same, EXACT returns FALSE....

Explanation This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic : (D5:D14="red")+(D5:D14="blue") After each expression is evaluated, we have the following two arrays : ({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+ ({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}) The math operation (addition) coerces TRUE and FALSE values to 1s and 0s: =({1;0;0;0;0;0;1;0;0;0})+({0;1;0;0;1;0;0;1;0;0}) The result is a single array like this: ={1;1;0;0;1;0;1;1;0;0} This final array is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a 1....

Purpose Return value Syntax =FORECAST.ETS(target_date,values,timeline,[seasonality],[data_completion],[aggregation]) target_date - The time or period for the prediction (x value). values - Existing or historical values (y values). timeline - Numeric timeline values (x values). seasonality - [optional] Seasonality calculation (0 = no seasonality, 1 = automatic, n = season length in timeline units). data_completion - [optional] Missing data treatment (0 = treat as zero, 1 = average). Default is 1. aggregation - [optional] Aggregation behavior....

Purpose Return value Syntax =GAMMAINV(probability,alpha,beta) probability - The probability associated with the gamma distribution (must be between 0 and 1). alpha - The shape parameter of the distribution. beta - The scale parameter of the distribution. Using the GAMMAINV function The GAMMAINV function returns the value at which the cumulative gamma distribution reaches a specified probability. For better accuracy and consistency with other modern statistical functions, it is recommended to use the GAMMA....

Explanation In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found. In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is: =TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),"")) Working from the inside out, this formula is based on the formula described here , which uses the SEARCH function together with the ISNUMBER function :...

Explanation In this example, the goal is to extract just the domain name from a list of email addresses. In the current version of Excel, the easiest way to do this is with the TEXTAFTER function or the TEXTSPLIT function. In older versions of Excel, you can use a formula based on the RIGHT, LEN, and FIND functions. All three options are explained below. TEXTAFTER function The TEXTAFTER function returns the text that occurs after a given delimiter....

About This Shortcut To navigate back to a hyperlink, after clicking the link, you can use a two step-process: (1) use the shortcut control + G to bring up the Go To dialog box, (2) press Enter to go back to the hyperlink. This works because the Go To dialog records the hyperlink location when a hyperlink is clicked. Then the Go To dialog box is opened, this location will appear in the “Reference” input area....

Explanation This formula uses the value in cell E5 for a lookup value, the named range “key” (H5:I9) for the lookup table, 2 to indicate “2nd column”, and 0 as the last argument indicate exact match. You can also use FALSE instead of zero if you like. VLOOKUP simply looks up the value and returns the group name from the 2nd column in the table. Both columns in the table can contain any values that you need, and the table doesn’t need to be sorted since VLOOKUP is using exact match....

Transcript In this video, we’ll look at several options for creating a pivot chart. A pivot chart is an extension of a pivot table, so in order to have a pivot chart you must also have a pivot table. Excel provides several ways insert a pivot table and pivot chart in one step. First, try Recommended Charts. If you don’t have too much data, recommended charts will suggest pivot chart options with the small pivot icon in the upper right....

Transcript Filtering a pivot table for top or bottom values is a special kind of Value Filtering. Let’s take a look. Here is the same pivot table we’ve looked at previously, showing Sales and Orders by product. Let’s add a Value Filter on the Product field that limits products to the top 5 products by Sales. Top and bottom Value Filters are a special kind of Value Filter, so you’ll find the option under Value Filters in the drop-down menu for the field you want to filter....

Transcript In this video, we’ll look at how to create a list of random dates. One of the nice things about the RANDARRAY function is that it makes it easy to generate a list of random dates. In this worksheet, let’s generate 20 random dates between May 1 and May 30, 2020. Now, to use the RANDARRAY function for this, we’re going to need a max and min value. These values correspond to start and end dates, and I’ve already this set up in cells C4 and C5....

Transcript When you’re working in a big list of data, a common task is to check the list for duplicates. Excel provides a built in conditional format to highlight duplicates, but it can be a little tricky to use it in a multi-column list. In this lesson we’ll look at one way to approach this problem. Let’s take a look. As we’ve seen previously, Excel provides a built-in conditional format preset to highlight duplicates in a set of values....

Transcript Excel contains special functions that will let you extract the day, month, and year from a valid date. Let’s take a look. Here we have a set of random dates in column B. First, I’ll add a formula to column C to pick up the date values in B and format them with the General format, so we can see the raw value. You can see that these are normal date serial numbers, and B11 is the only cell that contains a time value....

Explanation When building advanced formulas that use dynamic ranges, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last relative position inside a given range , not the row number on the worksheet. The screen below shows the idea:...

Explanation In this example, the goal is to make a noun plural when the number of items is greater than one. In many cases, a noun can be made plural by adding an “s”. However, many nouns have an irregular plural form, and the main challenge is to handle these exceptions. Ingredients In the example shown, the formula uses these ingredients: IF function – to check the number of items VLOOKUP function – to lookup irregular plural forms IFNA function – to take action when there is no irregular form Concatenation – to glue text together Note: There are many ways to solve a problem like this in Excel, and this is just one approach....