I have just discovered the TRIMRANGE function, and its very useful shortcut version, which were introduced to Excel 365 about a year ago. The function excludes all empty rows and/or columns from the outer edges of a range or array, and returns the remainder of the array​​​​​​​​​​​.

The function arguments are:

• The range to be trimmed, in this case entered as the complete column B:
• Optional “Row_trim_mode” and “Col_trim_mode” (see below for details).

The shortcut is to simply enter the range as =B.:.B. Some examples are shown below:

• Either the left or right period may be omitted from the shortcut symbol, so that either only the rows below and columns to the right will be trimmed using B:.B, or only the rows above and columns to the left will be trimmed using B.:B.
• The range may also be a 2D range, as shown in Column K.

Options to trim only to the left and above, or only to the right and below, are shown below:

Using the optional arguments with the full function name allows more options on the ranges to trim:

• 0 – None
• 1 – Trims leading blank rows or columns
• 2 – Trims trailing blank rows or columns
• 3 – Trims both leading and trailing blank rows or columns (default) 

In the examples above:

• =TRIMRANGE(A1:E100,2,2) trims trailing rows and columns
• =TRIMRANGE(A1:E100,1,2) trims leading rows and trailing columns
• =TRIMRANGE(A1:E100,3,1) trims both leading and trailing rows, but only leading columns.

Using the shortcut notation:

• =A1.:E100 is equivalent to: =TRIMRANGE(A1:E100,1,1)
• =A1:.E100 is equivalent to: =TRIMRANGE(A1:E100,2,2)
• =A1.:.E100 is equivalent to =TRIMRANGE(A1:E100,3,3) or just =TRIMRANGE(A1:E100)