Dot operator

The dot operator (.) is used to "trim" empty rows and/or columns from a range reference. It was introduced to Excel in 2024 along with the TRIMRANGE function and can be used to create a simple dynamic range that automatically adjusts to fit the data it contains. TRIMRANGE has more options than the dot operator, but both tools do the same thing: they remove unused rows and columns from a range reference, working from the outside in — empty rows and columns inside the data are not removed. The result is a "trimmed" range that is sized to fit the data.

Dot operator syntax

To use the dot operator, you combine it with Excel's standard range operator, the colon (:). To trim leading rows and columns, place a dot before the colon. To trim trailing rows and columns, place a dot after the colon. To both leading and trailing rows and columns, place a dot before and after the colon. Here are some examples:

=A:F // normal range, not trimmed
=A:.F // trim trailing rows and columns
=A.:F // trim leading rows and columns
=A.:.F // trim leading and trailing rows and columns

Note that the ranges above are full column references, which can create a performance disaster in large or complex workbooks because they can cause Excel to perform operations on millions of empty cells. Using the dot operator avoids this problem by "right-sizing" the range to fit the data it contains, so that Excel only runs calculations on cells that contain data.

The dot operator does not remove empty rows or columns that appear inside a block of data; it only removes empty rows or columns on the outside of the data.

Creating a simple dynamic range

To help you understand how the dot operator is useful, let's look at how to convert to typical static range into a simple dynamic range. In the worksheet below, we have some text values in column B. The goal is to return the number of characters in each value in column B. Because we want the formula to return results for new text values added to column B, we are using the "oversized" range B5:B1000 inside the LEN function. The formula in D5 looks like this:

=LEN(B5:B1000) // typical static range

This is a typical static range that does not change based on the data it contains. The LEN function correctly calculates the length of new text values, but notice that the results extend off the bottom of the screen. While this formula does handle new text values as they are added, it also returns a zero (0) for most of the cells in the range, which are empty:

In the worksheet below, we have adjusted the range to use a dot operator to remove empty trailing rows. The formula in cell D5 is now:

=LEN(B5:.B1000) // trim trailing rows

The result is that the output from the LEN function tracks the text values perfectly. LEN only returns results for cells that contain text:

The beauty of using the dot operator like this is that the formula will track the data in a worksheet perfectly as it changes. When data is added or removed, the range will automatically adjust, with no need to adjust cell references manually. In addition, we avoid the messy output from the same formula with an oversized range, as in the previous screen.

Note that the dot operator is an alternative to TRIMRANGE — don't use both together; use one or the other. An Excel Table is another good way to create a range that expands automatically, but the dot operator is a more light weight option.