Explanation The goal is to match a row in a set of data based on a given Item, Size, and Color. At a glance, this seems like a difficult problem because XMATCH only has one value for lookup_value and lookup_array . How can we configure XMATCH to consider values from multiple columns? The trick is to generate the lookup array we need using Boolean logic, then configure XMATCH to look for the number 1....

The IF function is one of the most heavily used functions in Excel. IF is a simple function, and people love IF because it gives them the power to make Excel respond as information is entered in a spreadsheet. With IF, you can bring your spreadsheet to life. But one IF often leads to another, and once you combine more than a couple IFs, your formulas can start to look like little Frankensteins :)...

About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a horizontal interior border added or removed from the border preview area. On the Mac, there is no equivalent shortcut. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a interior vertical border added or removed from the border preview area....

Explanation This is a good example of how the SUMPRODUCT function can help in situations where the COUNTIF or COUNTIFS functions do not work. In this case, the goal is to check all dates in a given range and show a check mark (✓) only when dates are in chronological order. The logic itself is quite simple, but perhaps not intuitive. Rather than check that all dates are greater than the previous date, we check if any previous date is greater than the next date....

The numbers returned by the CHAR function in Excel come from ASCII. ASCII stands for “American Standard Code for Information Interchange” and is a 7-bit character set that contains characters from 0 to 127. The original ASCII specification encodes 128 characters into numbers ( see table below ). These include the numbers 0 to 9, lowercase a-z, uppercase A-Z, and punctuation. The first 31 characters are non-printing “control codes”, most of which are no longer used, with the exception of the carriage return (13), line feed (10), and tab (9)....

Explanation This formula uses the MIN function to make a decision that might otherwise be handled with the IF function . Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for the “lesser of the two” situations. Inside MIN, the value in C6 is multiplied by 10%, and the result appears at the first number given to MIN. The number 1000 is supplied as the second value....

Transcript In this video, we’ll look at chart elements and chart layouts. A chart layout is a specific set of chart elements arranged in a particular way. Excel provides a limited set of chart layouts on the ribbon in the Quick layout menu on the Design Tab under Chart Tools. When you access this menu, you’ll see available layouts. As you hover over each layout, you’ll see a preview of the layout applied to the chart....

A clustered bar chart displays more than one data series in clustered horizontal columns. Each data series shares the same axis labels, so horizontal bars are grouped by category. Clustered bars allow the direct comparison of multiple series in a given category, but it’s more difficult for the human eye to compare the same data series across categories. Like clustered column charts, clustered bar charts become visually complex as the number of categories or data series increase....

Explanation In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously entered. The easiest way to solve this problem is with the SUMPRODUCT function, but it can also be solved with COUNTIFS as explained below....

Explanation You might wonder why we aren’t using COUNTIF or COUNTIFS . These functions seem like the obvious solution. However, without adding a helper column that contains a weekday value, there is no way to create criteria for COUNTIF to count weekdays in a range of dates. Instead, we use the versatile SUMPRODUCT function , which handles arrays gracefully without the need to use Control + Shift + Enter. We are using SUMPRODUCT with just one argument, which consists of this expression:...

Explanation At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total. When given a date, WEEKDAY simply returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday and 7 = Saturday. So, 2 = Monday, 6 = Friday, and so on....

Explanation This formula uses structured referencing , a syntax that allows table parts to be referred to by name. When a table is referred to by the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is B4:F104 so Table1 returns the range B5:F105 to the COLUMNS function. =COLUMNS(Table1) =COLUMNS(B5:F105) COLUMNS then returns a final result of 5, since there are 5 columns in the table....

Purpose Return value Syntax =CUMPRINC(rate,nper,pv,start_period,end_period,type) rate - The interest rate per period. nper - The total number of payments for the loan. pv - The present value, or total value of all payments now. start_period - First payment in calculation. end_period - Last payment in calculation. type - When payments are due. 0 = end of period. 1 = beginning of period. Using the CUMPRINC function The CUMPRINC function calculates the cumulative principal amount paid over a specified range of time, defined by the start and end periods of a loan....

Explanation Data validation rules are triggered when a user adds or changes a cell value. In this case, the COUNTIF function is part of an expression that returns TRUE when a value does not exist in a defined list. The COUNTIF function simply counts occurrences of the value in the list. As long as the count is zero, the entry will pass validation. If the count is not zero (i.e. the user entered a value from the list) validation will fail....

Explanation This formula relies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent of the operation of this formula. As long as the sequence is continuous, it can represent an ascending or descending sort, or even an arbitrary sort. In most cases, values will come from a formula . At the core, this is a simple INDEX and MATCH formula , where INDEX retrieves a value based on a specified row number:...

Explanation Dynamic ranges are also known as expanding ranges because they automatically expand and contract to accommodate new or deleted data. You can see a video demo of this approach here . This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells: =OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4)) The first argument in OFFSET represents the first cell in the data (the origin), which in this case is cell B5....

Explanation In this example, we need to construct logic that filters data to include: account begins with “x” AND region is “east”, and month is NOT April. The filtering logic of this formula (the include argument) is created by chaining together three expressions that use boolean logic on arrays in the data. The first expression uses the LEFT function to test if Account begins with “x”: LEFT(B5:B16)="x" // account begins with "x" The result is an array of TRUE FALSE values like this:...

Explanation When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. In the example shown the array looks like this: {2,3,4} If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array....

In a world where everyday is Saturday or Sunday…. Here’s a little puzzle for you…how can you use Excel to generate a list of dates that are weekends only? For example, a list of Saturday Sunday pairs like this: A couple years ago, I found and described a formula that will do it using the WEEKDAY function and some tricky date logic handled with IF: =IF(WEEKDAY(A1)=7,A1+1,A1+(7-WEEKDAY(A1))) With a date in A1, you can enter the formula in A2 and drag down to get your list of weekend dates....

Explanation In this example, the goal is to return the middle name from a full name in “First Middle Last” format. In the current version of Excel this is a fairly simple problem using the TEXTAFTER and TEXTBEFORE functions. In older versions of Excel, a similar formula is significantly more complicated, based on the MID function and multiple FIND functions. Both approaches are explained below. Modern solution In the latest version of Excel you can solve this problem with the TEXTAFTER and TEXTBEFORE functions like this:...