In recent blogs and videos we've looked at how to use Excel's TRIMRANGE function and trim ref notation to ignore empty cells at the end of a range you reference in a spilled array formula.

This is helpful when you want to reference a range of cells which haven't yet been populated but which you anticipate will receive values at some point in the future. If you reference empty cells in a spilled array formula, Excel will generate an answer for the empty cells, as shown below:

Referring to empty cells at the end of columns G and F forces Excel to produce an answer of 0 for all the empty rows.

Using the TRIMRANGE function or trim ref notation, you can refer to these empty cells without generating answers for them, as shown below:

The full stop after each colon tells Excel to ignore empty cells at the end of each range so we only produce answers for the populated rows.

This works nicely when you want to ignore empty cells, but what if the cells you want to ignore contain some sort of value?

Referencing entire columns

In the example above, it would have been easier to create the formula if we could refer to the whole of columns G and F rather than specific cells in those columns. Here's what the formula would look like:

We're referencing entire columns and using trim ref notation to ignore empty cells at the bottom of each column.

And here's what happens when we enter the formula:

These aren't quite the results we're looking for!

The problem is that the formula creates answers for the top two rows of the columns we've referenced. We could make things a little better by changing the trim ref notation to also ignore blank cells at the top of each range. Here's what the formula would look like:

The full stop before each colon will ignore blank cells at the beginning of each range.

And here are the slightly better results:

We still have one problem to solve!

The modified formula now effectively refers to only the populated cells in columns G and F. The remaining problem is that the first row of each range contains the column title (Box Office $m and Budget $m) which produces a #VALUE error when we try to subtract one from the other.

The DROP function

To solve the problem, we need to tell the formula to ignore the first row of each trimmed range. The DROP function allows you to ignore a specific number of rows or columns at the start or end of a range whether the cells are blank or not. Here's the syntax of the function:

Here's a summary of the function's parameters:

 Here's how we can use the DROP function to solve the problem shown above:

We've used a separate DROP function for each range in the formula.

And here are the results of the formula:

The DROP function ignores the column header in row 2 of the worksheet.

Ignoring columns

You can use the third parameter of the DROP function to ignore columns in a formula. You can see an example in the diagram below:

This formula uses the DROP function to ignore the first 5 columns of the each row.

The formula shown above uses trim ref notation to refer to whole rows of the worksheet, ignoring blank cells at the right of each range. The DROP function ignores the first 5 columns of each range, whether the cells are empty or not. Here's the result of the formula:

The result of the formula.

The formula ensures we calculate answers for only the populated columns, beginning from column F.

Ignoring cells at the end of a range

You can use negative numbers to ignore the bottom rows or right columns in a range. In the example below, we've added a label to the end of row 13:

We want to multiply the average spend in E12 by the number of sales on row 13.

The above formula references row 13 using the DROP function to ignore the first 5 columns and trim ref notation to ignore the blank columns to the right of the range. The problem is the label in cell R13.

Column R produces a #VALUE error.

To correct this, we can use a second DROP function to ignore the column containing the label. Here's the formula to do this:

We've wrapped another DROP function around the existing one.

In the second DROP function we've used -1 for the Columns parameter to ignore 1 column at the right of the range. Here's the result of the formula:

Excel ignores the text in R13 so doesn't produce an error.

Using DROP with other functions

You can nest the DROP function in other functions to make it easier to reference entire rows and columns. In the example below we're calculating the length of each film based on its run time using a lookup table:

Each film is categorised according to its length.

The formula in cell D2 uses an XLOOKUP function with three DROP functions nested within it.

The formula which produces the result.

The first DROP function ignores the single header in column C, while the second and third DROP functions ignore the top two rows of columns F and G.