Posts

The MATCH function can perform exact and approximate matches and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below. Note: the MATCH function will always returns the first match. If you need to return the last match (reverse search) see the XMATCH function. If you want to return all matches, see the FILTER function. MATCH only supports one-dimensional arrays or ranges, either vertical and horizontal....

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.

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.

You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard. 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.

Notes: (1) May not work in Excel 2010 on Vista or Windows 7. Discussion and fix is here: http://superuser.com/questions/183197/whats-the-keyboard-shortcut-to-unhide-a-column-in-excel-2010 (2) May not work in Excel 2007. See http://www.techrepublic.com/blog/microsoft-office/office-solution-keyboard-shortcuts-for-hiding-and-unhiding-columns-and-rows/ (3) In Windows 10, there is a conflict with a keyboard switching shortcut. You can resolve by un-assigning the keyboard layout hot key. You may need to un-assign the input language as well. This is a tricky setting to find. You are looking for Advanced Keyboard Settings, under Region and Language....

In the example shown the formula in E5:K6 is: Note: this is a multi-cell array formula and must be entered with Control + Shift + Enter. In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a table that is 7 columns by 2 rows (14 cells). Note that this function creates a dynamic link between the source and target. Any change in to data in the source table will be reflected in the target table....

the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is: where “entry” is the named range C5:C13, “date” is the named range B5:B13, and E5 contains a valid date. This is an array formula, and must be entered with control + shift + enter. Working from the inside out, the expression: uses the TEXT function to generate an array of strings in the format “mmyy”: {“0117”;“0117”;“0117”;“0217”;“0217”;“0217”;“0317”;“0317”;“0317”} which are compared a single string based on the value in E5, “0117”....

In the example shown, the array formula in B4:H4 is: On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter This is a robust formula that will continue to generate relative numbers even when columns are inserted in front of the range. The second COLUMN function generates an array with just one item like this: which is then subtracted from the first array to yield: Finally, 1 is added to get:...

Which returns 36 hours, based on a custom schedule where 8 hours are worked Mon-Fri, 4 hours are worked on Saturday, and Monday September 3 is a holiday. Holidays are supplied as the named range G6:G8. The work schedule is entered as a text string in column D and can be changed as desired. Note: This is an array formula that must be entered with Control + Shift + Enter....

Like line charts, area charts are a good way to show trends over time. However, when plotting multiple data series, you must pay attention to the order in which the data series are plotted. Here we have some US census population data for several states. Let’s plot this data in an area chart. To start off, I’ll just select data for Arizona, and create a new area chart. Now, if I try Recommended Charts, we’ll see an area option that looks good....

To enter a date in Excel, you need to type the date in a format that Excel can recognize. When checking for a date, Excel will look for a month, a day, and a year in a variety of formats. If you don’t supply a year, Excel will use the current year. Let’s take a look. In North America, Excel defaults to a month, day, year format, so dates following this format are easily recognized....

Let’s take a look. To understand how find and replace works with formatting, let’s use the name table, and the example of replacing Ann with Anna. Before we replace any values, let’s add an orange fill to all cells that contain Ann. Now let’s open the Find and Replace dialog box and set up our Find options. Let’s look for Ann, and select “Match entire cell contents.” Let’s also add a format to match the orange fill we’ve just applied....

Where “Table” is an Excel Table with data in B5:D14. Note: This is an array formula and must be entered with control + shift + enter. Working from the inside out, the IF function is set up like this: Here, each value in the gender column is tested against the value in G4, “Male”. The result is an array of boolean values like this: Only scores associated with males make it into the array, female scores are translated to FALSE....

In the example shown, the formula in C5 is: The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. Note: an easy way to enter this range is to use the Select All button. For criteria, we use a reference to C4, which contains “John”. COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to “John”....

The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel dates are just numbers, SEQUENCE works well for generating dates. In this first worksheet, we have a couple cells set up to collect input for a start date, and days. I’ll enter the SEQUENCE function in cell E5 and configure it to use these inputs. The rows argument corresponds to the number of dates we want, so we want to use C5....

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here: This snippet spins up an array containing 100 numbers like this: Note: 100 represents the maximum characters to process; adjust to suit your data....

where amount (C5:C16) and date (B5:B16) are named ranges. Note: as explained below, the values in E5:E10 are valid Excel dates, formatted to display the month name only. Both solutions use the named ranges amount (C5:C16) and date (B5:B16) for convenience only, you can also use absolute references instead, or an Excel Table with structured references. Also note that the values in E5:E10 are valid Excel dates, formatted to display the month name only....

The rows and columns arguments function like “offsets” from the starting reference. The height and width arguments, both optional, determine how many rows and columns the final range includes. We want OFFSET to return a range that begins at the last entry and expands “backwards” so we supply arguments as follows: reference - the starting reference is D5 – the cell directly to the right of the formula, and the first cell in the range of values we are working with....

which calculates the circumference of a circle with the radius given in column B. The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function, which returns the number 3.14159265358979, accurate to 15 digits. Therefore, the formula to calculate the circumference of a circle with a radius of 3 is: And the formula to calculate circumference based on the radius values in column B, is:...

where data is the named range B5:B15. The result is 7, since there are seven cells in B5:B15 that contain numbers greater than zero. COUNTIF function The COUNT function counts the number of cells in a range that match supplied criteria. For example, you can use COUNTIF like this: To count positive numbers in this example, we need to use the less than operator (<) with zero like this: The result is 7, since there are seven cells in B5:B15 that contain numbers greater than zero....