Transcript In this video we’re going to take a quick look at the question: “what is text in Excel?” Whenever you enter data in an Excel worksheet, Excel checks the data type you’ve entered and classifies it according to four basic data types: Numbers Dates and times Boolean values Text Excel figures out the data type based on formatting and other information it uses to guess at your intention. Text is the category that Excel uses when Excel isn’t able to classify content as one of the other data types....

Transcript In this video we look at a few reasons why VLOOKUP is a better option than nested IF statements. In our last video, we used nested IF statements to calculate a commission rate based on a sales number. As a quick recap: The first formula is created with nested IF statements normally. The second example is the same formula but formatted with line breaks to make it easier to read....

Transcript In this video, we’ll set up the XLOOKUP function to return multiple values in a dynamic array. In this worksheet, we have an example we looked at previously. On the left, we have quantity-based discounts, and on the right, we have some random quantities. Let’s set up XLOOKUP to return all results in a single dynamic array. As a first step, I’m going to convert the table on the left into a proper Excel Table ....

Explanation In this example, the goal is to add hours in decimal format (i.e. 1, 2, 3, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1. But it isn’t the way most people think about time. To add decimal hours to a time, we need to first convert the hours to an equivalent fractional value....

Explanation In this example, the goal is to calculate an average of the top 3 quiz scores for each name listed in column B. For reference, column H has a formula that calculates an average of all 4 scores. This is a slightly tricky problem, because it’s not obvious how to limit the scores included in the average to only the top 3 scores. The classic solution is to use the AVERAGE function with the LARGE function as explained below....

Explanation At the core, this formula subtracts start time from end time to get duration in hours. This is done to calculate both work time and break time. MOD(C6-B6,1) // get work time MOD(E6-D6,1) // get break time Next, break time is subtracted from work time to get “net” work hours. This formula uses the MOD function to handle times that cross a day boundary (midnight). By using MOD with a divisor of 1, positive results are unchanged, but negative results (which occur when the start time is greater than the end time) are “flipped” to get a correct duration....

I ran into this chart while looking for examples of XY scatter plots and couldn’t resist re-building it, following the instructions outlined way back in 2000 I think. Unfortunately the original link no longer works. The exercise is to verify the value of pi by plotting the circumference vs. radius of a number of objects measured in a lab. Here is the data: circumference radius 4.08 0.66 11.94 1.89 13.18 1....

Explanation At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range “include”. In the right SUMPRODUCT, SEARCH looks for all strings in the named range “exclude”. In both parts of the formula, SEARCH returns numeric positions when strings are found, and errors when not. The ISNUMBER function converts the numbers to TRUE and errors to FALSE, and the double negative converts the TRUE FALSE values to 1 and 0....

One of the hottest trends today is data visualization. Why? Because when data is presented visually, people understand it immediately . This course will show you how to use Excel’s conditional formatting to communicate key insights with clarity and impact. This is an accelerated program that takes you step-by-step through the capabilities of this powerful tool.

Transcript In this video, we’re going to extend conditional formatting rule with a variable, so that you can change behavior without editing the rule. Let’s take a look. Here we have the table of random numbers. Let’s first build a conditional format that uses a formula to highlight cells greater than 500. As before, select the full set of values, then choose New Rule from the Conditional Formatting menu, then choose the option for formula....

Explanation In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Value Time 1 1/24 0.04167 1:00 3 3/24 0.125 3:00 6 6/24 0.25 6:00 4 4/24 0.167 4:00 8 8/24 0.333 8:00 12 12/24 0.5 12:00 18 18/24 0.75 18:00 21 21/24 0.875 21:00 This means if you have a decimal number for hours, you can simply divide by 24 to get the correct representation of hours in Excel....

Explanation The LEN function is fully automatic. In the example, the formula in the active cell is: =LEN(B5) The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters, as you can see in cell C9. Numbers Numbers in Excel (including dates, times, currency, etc.) are often formatted with a number format . It’s important to understand that the characters that makeup numbers are counted in their raw form (i....

Purpose Return value Syntax =COUNTBLANK(range) range - The range in which to count blank cells. Using the COUNTBLANK function The COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc. are not counted. Formulas that return an empty string ("") are counted as blank. COUNTBLANK takes just one argument, range, which must be a cell range . Examples In the example shown, the formula in cell E6 is:...

Explanation First, for context, it’s important to note that you can use COUNTIFS with a regular structured reference like this: =COUNTIFS(Table1[Swim],"x") This is a much simpler formula, but you can’t copy it down column H, because the column reference won’t change. The example on this page therefore is meant to show one way to set up a formula that references a table with a variable column reference. Working from the inside out, the MATCH function is used to find the position of the column name listed in column G:...

Explanation This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function. The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument ( array ), and a value for n as the second argument ( k ): =LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest In this example, the logic for FILTER is constructed with this snippet:...

Purpose Return value Syntax =FORECAST.ETS.STAT(values,timeline,statistic_type,[seasonality],[data_completion],[aggregation]) values - Existing or historical values (y values). timeline - Numeric timeline values (x values). statistic_type - The statistic to return, a numeric value between 1 and 8 (see table below). 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....

Explanation This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers: Table1[#Headers] This range goes into INDEX for the array argument, with the index value supplied from column H: =INDEX(Table1[#Headers],H5) The result is the name of the first item in the header, which is “ID”. Although the headers are in a horizontal array, with values in columns, INDEX will use the row number as a generic INDEX for one-dimensional arrays like this and correctly return the value at that position....

Transcript How to add a conditional formatting key When you apply conditional formatting, you might want to add a key to your worksheet to make the rules which trigger formats clear. In this lesson, we’ll look at how to build a key using the same conditional formatting already in the worksheet. Let’s take a look. Here we have same worksheet we looked at previously. We have three conditional format rules defined - one highlights scores over 95 in green, one highlights scores below 70 in red, and one highlights scores below 75 in yellow....

Transcript In this video, we’ll build a simple dashboard by setting up a slicer to control to more than one pivot chart. Here’s the chocolate sales data we looked at earlier. Using the techniques discussed in this section, let’s build a simple dashboard to show sales by city and top 5 chocolate flavors. Notice, I’ve already added a tab for the dashboard with some placeholders. This makes it easier to visualize what we’re trying to build....

Transcript Excel charts can exist in two states: as an embedded object in a worksheet, floating above the worksheet grid, or, in a worksheet all by themselves. In this lesson we’ll look at how to move charts from one state to the other. Let’s take a look. Normally, when you choose a chart type on the ribbon, Excel creates an embedded chart which floats over the worksheet grid below. But if you’re short on space, or, if you’d just rather have a chart in its own worksheet, you can easily do so....