Dave Bruns

This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:

=AVERAGE(first:last)

where "first" is a...Read more

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...Read more

At the core, this formula has a simple pattern like this:

=SUM(first:current)

Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.

To get the a...Read more

This formula uses structured references to feed table ranges into the SUMIFS function.

The sum range is provided as Table1[Total], the criteria range is provided as Table1[Item], and criteria comes from...Read more

This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match.

The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH...Read more

At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value:

=VLOOKUP($I$4,Table1,MATCH(H5,Table1[#Headers],0),0)

• The ID value comes from cell I4, and is locked so that it won't change as
...Read more

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...Read more

This formula uses structured referencing, a syntax that allows table parts to be called out by name. When a table is called with the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is B4:...Read more

In this example, we have a list of URLs. The goal is to get the portion of each URL that appears after the domain name. In the current version of Excel, the easiest way to do this is to use the TEXTAFTER function. In an older version of Excel, you can use a formula based on the MID, FIND,...Read more

The goal is to remove the forward-slash ("/") from the URLs in column B when it is present as the last character. When a URL does not end with a forward slash ("/") the original URL should be returned without modification. Despite the fact that Excel offers many functions designed to...Read more