Knowing how many cells to reference in an Excel spilled array formula can be tricky if you don't know how your data is going to change in the future. It's common to "cheat" by referencing an entire column or row, but this is often inefficient. Enter Excel's new TRIMRANGE function and Trim Refs feature!

Referencing cells in formulas

Let's say you set up a formula based on the currently populated cells in your workbook. For this example, we'll create a spilled array to avoid having to copy our formula to each row in the list.

We're subtracting the Budget from the Gross to calculate the Profit.

Entering the formula populates the relevant range of cells with answers.

The formula populates a number of cells based on the sizes of the input ranges.

Extending the range

The problem with the above formula is that it doesn't respond when you add more data to the list.

The spilled array doesn't automatically expand to to include the new data.

Referencing empty cells

One option for ensuring the formula gives an answer for data that you add in the future is to include the empty cells below the existing data when you create the original formula.

This seems a little excessive.

The problem with this approach is that every row will receive an answer, even when there's no data there.

This isn't quite what we wanted!

Using the TRIMRANGE function

You can use the TRIMRANGE function to ignore empty cells beyond the last used row or column of a range. Here's the syntax of the function (parameters in square brackets are optional):

We can use the TRIMRANGE function for each of the ranges we select in our formula.

Use a separate TRIMRANGE function for each range you reference.

The result is a formula which ignores any cells after the last populated cell in each range.

We only see answers for populated rows.

When we add new data to the list, the formula automatically populates new cells with the results.

The answer range expands automatically to include new data.

Note that you'll see an error if you populate only one of the trimmed ranges:

You must populate each trimmed range with a value to see a valid answer.

Trimming horizontal ranges

You can use the TRIMRANGE function to trim horizontal ranges just as easily.

This formula references two ranges spanning columns F to XFD.

The formula produces answers for the populated columns.

Only the currently populated columns receive answers.

The range of answers grows as more data is added to the worksheet.

Unfortunately, we have to apply the formatting manually.

Nesting TRIMRANGE in other functions

You can nest the TRIMRANGE function in other functions.

Here we've nested TRIMRANGE in an IF function.

The formula shows an answer for each populated row.

The TRIMRANGE function ensures answers appear only on rows with an Oscar Wins value.

The answer range expands as we add more data to column B.

As we add more data, we reveal more answers.

Using the optional parameters

By default, the TRIMRANGE function ignores blanks at the top, bottom, left and right of a range. You can use the optional parameters of the TRIMRANGE function to provide more control over which cells are trimmed. The first optional parameter allows you to control which rows are trimmed.

Here, we only need to ignore blank rows at the end of the range.

The second optional parameter allows you to control which columns are trimmed.

Here, we're referring to a single column so we don't need to trim any columns.

The complete formula might look like this:

For each range, we're trimming the trailing blank rows only.

Using Trim Refs

Rather than using the TRIMRANGE function, you can modify the way you reference a range to ignore leading or trailing blank cells. You do this by placing a full stop before and/or after the colon which separates the cell references.

We've placed a full stop before and after the colon in each range in this formula.

The result is the same as when using the TRIMRANGE function: we see results for populated cells in the input ranges.

The result range extends as far as the populated input ranges.

When we add more data to columns B and C, the answer ranges expands.

The results appear automatically as we add more data.

Placing a full stop before the colon indicates that you want to trim blank cells at the beginning of the range. Placing a full stop after the colon indicates that you want to trim blank cells at the end of the range. In the example above, we only needed to place a full stop after each colon.

We don't have blank cells at the beginning of the range so we can use a full stop after each colon to ignore blank cells at the end.