In the formula shown, HLOOKUP is configured like this: The lookup value is “*”, a wildcard that matches one or more text values. The table array C5:E5, entered as a relative references so that it changes as the formula is copied down column F. The row index number is 1, since the range contains only one row. The range lookup argument is set to zero (false) to force exact match....

where “data” is the named range C5:G14. Note: for this example, we arbitrarily find the location of the maximum value in the data, but you can replace data=MAX(data) with any other logical test that will isolate a given value. Also note these formulas will fail if there are duplicate values in the array. To get the row number, the data is compared to the max value, which generates an array of TRUE FALSE results....

When formatted as a percentage with zero decimal places, the result is 7%. Note: you must format the result using Percentage number format to display the final result as 7% Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Although you can reference any cell in a pivot table with a normal reference (i.e. E10) the GETPIVOTDATA will continue to return correct values even when the pivot table changes. In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second: This will give us the grand total....

Let’s take a look. Here we have a pivot table that contains product sales organized by year and city. Notice that whenever you hover over a label or value in the pivot table, you’ll see a pop-up window that shows the details for that field at that location in the pivot table. But what if you want to see the data behind that number? Whenever you want to see the data behind a summarized value, you can simply double click that value to get the full set of data that backs up that number....

Let’s take a look. Whenever you enter or edit a formula that contains a function, the function tip window appears. The location that the function tip window appears in depends on where you’re editing the formula. If you’re editing a cell directly, it appears below the cell. If you’re working in the formula bar, it appears just below the formula bar. Whenever the tips appear, you’re free to move them to a new location if they’re in your way....

Let’s take a look. Here we have a set of times in column B of our table. Let’s start off by copying these times to all columns, then adjust formats to match those shown in the table header. Let’s look first at the default time format in column C. This is the format you get when you apply the Time format using the menu in the ribbon. If we check this format in the Format Cells dialog, we see it listed first, with an asterisk....

Which increments as the formula is copied down. If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet. However, if we copy this formula down the column, the reference to A1 won’t change, because “A1” is hardcoded as text. To solve this problem, we use the CELL function to generate a text reference from a regular cell reference: With “address” as the first argument, and A1 as the second argument, the CELL function returns a string like “$A$1”....

This formula generates a working hyperlink to cell A1 in each of the 9 worksheets as shown. In cell D5, the link location argument is created like this: which returns the string “#Sheet1!A1”. The formula then resolves to: Which returns a valid link. The cell value in column C is entirely arbitrary and can be any cell you like. It could also be hardcoded into the formula as a string like this: Note: The hash character (#) at the start of the sheet name is required....

In the example the formula in C5 is: If you also need to strip out extra spaces in the names, you can wrap PROPER in the TRIM function: This will strip any leading and trailing spaces, and convert runs of spaces to a single space. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....

In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement text. SUBSTITUTE will replace all instances of “-” with nothing. Note that SUBSTITUTE is a case-sensitive function. Removing more than one thing If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions....

which generates a series of 12 times, beginning at 7:00 AM, the date in B5. In the example shown, we want to generate 12 times, one hour apart, starting at 7:00 AM (the value in B5). To do this, we use the TIME function, which can create a valid Excel time with hours, minutes, and seconds given as decimal values. To create the numbers used for hours with SEQUENCE, we have: The HOUR function is used to convert the time in B5 to a decimal value for hours (7)....

With $1,000 in cell F5, this formula returns $3,875, the sum of values in D5:D16 less than $1,000. SUMIF function The SUMIF function is designed to sum cells based on a single condition. The generic syntax for SUMIF looks like this: For example, to sum values in D5:D16 that are less than $1,000, we can use the SUMIF function like this: We don’t need to enter a sum_range, because D5:D16 contains both the values we want to test and the values we want to sum....

Enter times in correct format You must be sure that times are correctly entered in hh:mm:ss format. For example, to enter a time of 9 minutes, 3 seconds, type: 0:09:03 Excel will show the time in the formula bar as 12:09:03 AM, but will record the time properly as a decimal value. Internally, Excel tracks times as decimal numbers, where 1 hour = 1/24, 1 minute = 1/(2460), and 1 second = 1/(2460*60)....

Pros Quick visualization of hierarchical data At-a-glance breakdown of data in categories Cons Data must be sorted by category Limited options for controlling format and presentation Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Tables offer special features for working with data. Once you have a table defined, it is much easier to select and rearrange data in rows and columns. There are several ways you can add and remove rows. First, you can use the Insert and Delete buttons on the Home tab of the ribbon. With any cell selected in the table, Insert will add a new row above. And, the Delete button will delete rows....

where date (C5:C15), item (B5:B15) and price (D5:D15) are named ranges. By default, XLOOKUP will return the first match in a data set. To get the last match, we can set the optional argument search_mode, to -1 to cause XLOOKUP to search “last to first”. However, we can’t use this approach here because there is no guarantee that the latest price for an item appears last. Instead, we can set the optional argument match_mode to -1 to force an approximate match of “exact or next smallest”, and adjust the lookup value and lookup array as explained below....

where data is the named range B5:B15. This happens B9 and B13 contain the #N/A errors, and this is a common problem in Excel: errors in the data tend to percolate up to summary calculations. AVERAGEIF One way to work around this problem is to use the AVERAGEIF function, which can apply a condition to filter values in a range before they are averaged. For example, to specifically ignore #N/A errors, you can configure AVERAGEIF like this: In the worksheet shown, this is the formula in cell E8....

Boolean operations are a key building block in the world of dynamic array formulas. To illustrate, let’s look at some simple order data. Given the data shown, how can we total orders from Texas using an array formula? To start off, I’ll enter an expression that tests the state column in the data to see if it equals “TX”: Notice Excel by default is not case-sensitive. Because I’ve provided a range of values from the State column, we get all results in a single dynamic array....

A key tool in building friendly messages is concatenation. Concatenation sounds complicated, but it’s a really simple and useful formula technique you can learn in a few minutes. You’ll find many opportunities to use concatenation in your spreadsheets. Caution: When people see messages like this in a worksheet you built, they’ll assume you’re some kind of Excel genius, so be warned :) A basic example You can assemble messages with nothing more than a simple formula and a technique called “concatenation”....