Explanation If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode. The problem There are several ways to group times in Excel. If you just need to group times by the hour, a pivot table is very quick and easy. If you need to group times into other equal buckets of multiple hours (i....
Transcript Sometimes you might create named ranges after you’ve already built formulas. In that case, Excel will not automatically update the formulas to use the named ranges. However, there are a couple of ways you can apply named ranges to formulas that already exist. Let’s take a look. Here we have a table that shows hours worked and gross pay for a small team. Everyone is paid the same hourly wage, so the formula used to calculate pay uses an absolute reference to cell C3....
Transcript In this video, we’ll look at how to use a formula to apply conditional formatting. Formulas allow you to make powerful and flexible conditional formatting rules that highlight just the data you want. Let’s take a look. Excel provides a large number of conditional formatting presets, but there are many situations where you’ll want to use a formula to trigger a rule. The main requirement is that the formula return either TRUE or FALSE....
Transcript When you’re working with a big list, it’s often handy to freeze headers or columns so that they’re always visible as you scroll through the data. In this lesson we’ll look at how to do that. Let’s take a look. Here we have a large data table with a header row at the top. Notice that as we scroll down through the data, the headers scroll off the screen....
Transcript The SUM function is a simple function that adds up numbers. Let’s take a look. The SUM function sums numeric values. For example, if I enter the formula =SUM(B7:B11) Excel will return “50,” the total of all numbers in that range. SUM simply ignores blank cells. The formula =SUM(D7:E9) will return “1,350”. Like blanks, SUM ignores text values. In addition to ranges, SUM can work with individual cells, too. Just separate each additional item with a comma....
Explanation Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B: INDEX(data,MATCH(B5,data,0)) The MATCH function gets the position of the value in B5 inside the named range data, which for the lookup value “blue” is 3. This result goes into the INDEX function as row_num, with “data” as the array: INDEX(data,3) This appears to return the value “blue” but in fact the INDEX function returns the address E6....
This example shows a line chart plotted with over 8000 data points. The data itself is daily stock market information for Microsoft Corporation over a period of more than 30 years. Only the closing price is plotted. When you first create a line chart with this much data, the x-axis will be crowded with labels. The key is to adjust the bounds and units for the in the Axis options area....
Explanation In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach: Define a name called “sheetnames” with an old macro command and the Name Manager. Use the defined name in a formula that extracts the names into the workbook The article below explains how to follow these steps....
Explanation In this example, the goal is to get the minimum value for each group in the data as shown. The easiest way to solve this problem is with the MINIFS function. However, there are actually several options. If you need more flexibility (you need to work with arrays instead of ranges), you can use the MIN function with the FILTER function. To create a dynamic summary table with a single all-in-one formula, you can use the BYROW function....
About This Shortcut Inside a cell, this shortcut will move the cursor by one word to the right each time the right arrow key is pressed. About This Shortcut Inside a cell, this shortcut will move the cursor by one word to the left each time the left arrow key is pressed.
About This Shortcut This shortcut allows you to paste a previously defined name into a formula. It displays the Paste Name dialog box. About This Shortcut This shortcut will accept a function suggested by autocomplete. Start typing the first few letters of a function, then type Tab to accept the top option in the list that appears. You can also use up or down arrow keys to navigate the autocomplete list before pressing Tab....
Purpose Return value Syntax =PERCENTRANK.EXC(array,x,[significance]) array - Array of data values. x - Value to rank. significance - [optional] Number of significant digits in result. Defaults to 3. Using the PERCENTRANK.EXC function The Excel PERCENTRANK.INC returns the relative standing of a value within a data set as a percentage. For example, a test score greater than or equal to 80% of all test scores is said to be at the 80th percentile....
By default, a Pivot Table will count all records in a data set. To show a unique or distinct count in a pivot table, you must add data to the object model when the pivot table is created. In the example shown, the pivot table displays how many unique colors are sold in each state. Fields The pivot table shown is based on two fields: State and Color. The State field is configured as a row field, and the Color field is a value field, as seen below....
Explanation All the hard work is done by the COUNTIF function, which is configured to count the values in the named range “substrings” that appear the named range “rng” with like this: COUNTIF(rng,"*"&substrings&"*")) By wrapping substrings in the asterisks, Excel evaluates the formula like this: =SUMPRODUCT(COUNTIF(rng,{"*dog*";"*green*";"*sun*";"*every*"}))>0 COUNTIF counts the values wherever they appear in the cell. Since we are giving COUNTIF multiple values to look for, we receive a count for each value in an array like this: {1;0;1;1} ....
Explanation The COUNTIF function counts cells that meet supplied criteria, and returns a count of occurrences found. If no cells meet criteria, COUNTIF returns zero. The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range....
About This Shortcut When multiple cells are selected, this shortcut will leave only the active cell selected. About This Shortcut This shortcut will scroll the screen to show the active cell on the worksheet. It works when there is only one cell selected or when there are multiple cells selected. Note: Older Excel versions on the Mac may use Command + Delete instead of Control + Delete.
Transcript In this video, we’ll cover some really useful shortcuts for selecting cells. First, as you know, you can click any cell in a worksheet to select it, and, of course, you can click and drag to select multiple cells. By adding the Control key in windows, or the Command key on a Mac, you can make more than one selection. These selections do not need to be next to one another....
Explanation In this example, the goal is to split comma-separated values (CSV) in column B into multiple columns, as seen in the worksheet shown. Each text string in column B contains 5 fields separated by commas, so we expect to get 5 columns of data as a result. The header row in column D is manually entered. One disadvantage of the TEXTSPLIT + REDUCE + VSTACK formula featured in this article is that it is somewhat complex....
Explanation Overview The formula looks complex, but the mechanics are in fact quite simple. As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what you need. In this case, we are assuming that numbers and text are combined, and that the number appears after the text....
Explanation The INDEX function looks up values by position. For example, this formula retrieves the value for Acme sales in Jan: =INDEX(data,1,1) The INDEX function has a special and non-obvious behavior: when the row number argument is supplied as zero or null, INDEX retrieves all values in the column referenced by the column number argument. Likewise, when the column number is supplied as zero or nothing, INDEX retrieves all values in the row referenced by the row number argument:...
