Posts

Explanation The COUNTIF function counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the rule. Conditional formatting is evaluated for each cell that is applied to. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created....

Transcript How to use conditional formatting with the LEN function to highlight text that is too long. Have you ever had to check to make sure certain lines of text aren’t too long? For example, maybe you need to check values being imported to a database or website that only allows a certain number of characters per field? In a case like this, what’s a good way use Excel to flag values that need to be shortened?...

Transcript In this lesson we’ll look at how to keep your chart updated with the latest values, and how to add more data to your chart when needed. Let’s take a look. After you’ve created a chart, you normally don’t need to worry about updating the chart manually. That’s because Excel will automatically update the chart when the source data changes, as long as the calculation is set to Automatic....

Transcript In this video, we’ll look at several ways to enter functions. Let’s take a look. If you know the function name, the easiest way to enter a function is just to enter an equal sign and start typing. For example, if I want to get the average of these test scores with the AVERAGE function , I can start typing the name. Excel will show a drop-down list of possible functions below by matching the characters you’ve already typed....

Transcript In this lesson we’ll show you how to filter data using more than one column. Let’s take a look. Here we have a table that contains 300 property listings. Each entry has information for price, address, property type, bedrooms, baths, square footage, cost per square foot, year built, and date listed. Let’s use Excel’s filter tools to explore this information. First, we need to enable filter mode. Select any cell in the table, then click the Filter button on the Data tab of the ribbon....

Transcript Excel has a great feature that allows you to find all cells that meet your find criteria in one step. Let’s take a look. When using the Find and Replace dialog box in Excel, there are actually two options for finding matches: Find Next, which we’ve already covered, and Find All. The Find All button will build a list of every cell that meets the current search criteria and report a total at the bottom....

Transcript In this lesson we’ll look at how to apply local formatting to individual characters and words. Let’s take a look. Sometimes you need to apply formatting to only a few characters or words. It doesn’t work to select a cell and apply the formatting, because that will affect all of the content in the cell. There are two ways to apply this kind of formatting. The first way is to select a cell and apply formatting in the formula bar....

Transcript In this video, we’ll look at how to create a list of random text values. As we’ve already seen, the RANDARRAY function can be used to generate random dates and times, which are numeric values. How can we generate random values that aren’t numeric? One way is to use this is to use the RANDARRAY function to generate random positions, then use the INDEX function to retrieve the values at these positions....

Transcript Excel has a handy feature called Evaluate Formula, which solves a formula one step at a time. Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result. Here’s the same worksheet we looked at in a previous video when we talked about debugging formulas using the F9 key. The Age column contains a formula that calculates age from Birthdate, and the Status column uses the IF function to label each person in the list as either an Adult or Minor....

Transcript In this video, we’ll look at an easy way to test a conditional formatting formula before you create the rule. When you use a formula to apply conditional formatting, it can be tricky to set the formula up correctly. The dialog used to create and edit the formula doesn’t provide all the nice features that the formula bar does. You can’t easily check references, you don’t get function screen tips, and the window is too small....

Transcript In this video, we’ll cover the basics of data labels. Data labels are used to display source data in a chart directly. They normally come from the source data, but they can include other values as well, as we’ll see in a moment. Generally, the easiest way to show data labels to use the chart elements menu. When you check the box, you’ll see data labels appear in the chart....

Explanation In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX and MATCH formula that looks up information using the movie title. If you are new to INDEX and MATCH formulas, this article provides a detailed overview with many examples....

Explanation Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and repeated on an on-going basis. However, you can also use the LAMBDA function to create a custom function to combine ranges. This makes sense when you have good control over the data and need a solution that updates automatically, without a refresh step....

The term “lifting” (also called “auto-lifting”) refers to an array behavior that occurs in Excel formulas. Lifting is an automatic, element-wise evaluation of an operation or function over an array. When an operator or function that typically accepts scalar (single) values is given one or more arrays, Excel “lifts” the operation so it is applied to each element and returns an array of results. If this array is the final result of a formula, the array spills into the worksheet according to the dimensions of the array....

Explanation In this example, we’ll use SEQUENCE to generate all dates in a given month. Creating a complete list of dates for a specific month is a common Excel task with many practical applications, from building project timelines and work schedules to generating calendar views and tracking daily data. The input is any date within the target month (it doesn’t matter which specific day), and the output is a dynamic list that automatically adjusts when you change the input date....

Explanation Working from the inside out, the MATCH function matches the range against itself. That is, we give the MATCH function the same range for lookup value and lookup array (B5:F5). Because the lookup value contains more than one value (an array), MATCH returns an array of results, where each number represents a position. In the example shown, the array looks like this: {1,2,1,2,2} Wherever “dog” appears, we see 2, and Wherever “cat” appears, we see 1....

About This Shortcut This shortcut will move to the next tab in the dialog box. About This Shortcut This shortcut will move to the previous tab in the dialog box.

Explanation The AND function is designed to evaluate multiple logical expressions, and returns TRUE only when all expressions are TRUE. In this case the we simply compare one range with another with a single logical expression: B5:D12=F5:H12 The two ranges, B5:B12 and F5:H12 are the same dimensions, 5 rows x 3 columns, each containing 15 cells. The result of this operation is an array of 15 TRUE FALSE values of the same dimensions:...

Explanation The core of this formula is the IF function, which “filters” the names in the table by color like this: IF(group=E5,name,"")) 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: {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE} That result is used in turn to filter names from the named range “name”: {"Matt";"Sally";"Jude";"Aya";"Elle";"Linda";"George"} For each TRUE, the name survives, for each FALSE, IF returns an empty string ("")....

Purpose Return value Syntax =PERCENTILE(array,k) array - Data values. k - Number representing kth percentile. Using the PERCENTILE function The Excel PERCENTILE function calculates the “kth percentile” for a set of data. A percentile is a value below which a given percentage of values in a data set fall. A percentile calculated with .4 as k means 40% percent of values are less than or equal to the calculated result, a percentile calculated with k = ....