How to Use TRIMRANGE in Excel for Dynamic Ranges

TRIMRANGE returns the occupied portion of a range after excluding empty rows and columns around its edges. For example, =TRIMRANGE(A1:C9) returns A1:B7 when those are the occupied cells and column C plus rows 8–9 are empty.

The result updates when the source changes within the specified range. It does not delete worksheet rows, remove spaces inside text, or discard every blank row in the middle of a list.

Applies to: Excel for Microsoft 365. The basic example was checked in Excel for Windows. Microsoft announced Windows availability in its March 2025 Excel update; older instructions describing it as an Insider-only feature may be outdated.

Try TRIMRANGE on a small range

1. Enter the following data in A1:B7. Leave column C and rows 8–9 empty.
2. Select H1 or another empty cell outside the source range. Leave enough room below and to the right for the result.
3. Type =TRIMRANGE(A1:C9) and press Enter.
4. Check H1:I7. You should see the two headers and all six records, with no extra output column for C and no extra output rows for rows 8–9.

Type another item and amount in A8:B8. The result can now extend to row 8 because that row is already inside A1:C9. A record entered in row 10 will not be included: TRIMRANGE can only inspect the range you give it.

Choose which edges to trim

The syntax is TRIMRANGE(range,[trim_rows],[trim_cols]). Use 0 to keep an axis unchanged, 1 to trim its leading edge, 2 to trim its trailing edge, or 3 to trim both edges. Omitting the optional arguments uses 3 for both.

Preserving columns can matter in a report with a fixed structure. If an optional Notes column is currently empty, trimming it away changes the number of columns returned. Setting the third argument to 0 keeps its position.

The Microsoft TRIMRANGE reference also describes shortened range operators:

• =A1.:.C9 trims both leading and trailing edges.
• =A1:.C9 trims trailing edges.
• =A1.:C9 trims leading edges.

The ordinary colon still separates the endpoints. The added dots specify trimming. Use the full TRIMRANGE function if separate row and column controls make the formula easier to understand.

Keep a formula range ready for new records

Suppose product names start at A2, and you reserve A2:A1000 for future entries. This returns the used part of that reserved range:

=TRIMRANGE(A2:A1000)

Enter the formula outside column A’s reserved area. As names are added farther down within the range, the result grows. The 1,000-row boundary is a deliberate capacity limit, not a promise to find every name anywhere in the worksheet.

You can pass the result to another function. For example:

=ROWS(TRIMRANGE(A2:A1000))

This counts the rows in the trimmed result, not necessarily the number of nonempty records. If A2:A5 contains four product names, it returns 4. If A3 is empty but A2, A4, and A5 contain names, it still returns 4 because the interior blank remains inside the returned rectangle. Use a count based on your record rule when you need a count of actual entries.

For a maintained list that people edit every day, an Excel Table is another useful option. Structured references follow table data as it changes. TRIMRANGE is useful when you want an ordinary range or formula result to supply a smaller range to another calculation. See Microsoft’s table-reference documentation for that alternative.

Use TRIMRANGE in a named range

A name can keep the same range definition available to several formulas. To create one for the product list in a sheet called Sheet1:

1. Open Formulas > Name Manager and select New.
2. Enter ProductList as the name and use Workbook scope.
3. In Refers to, enter =TRIMRANGE(Sheet1!$A$2:$A$1000).
4. Select OK, then close Name Manager.
5. Test =ROWS(ProductList) in a separate cell and compare it with the occupied span of your product list.

Replace Sheet1 with your actual sheet name. The dollar signs keep the source coordinates fixed. If the sheet name contains spaces, use single quotation marks around it, such as 'Product Data'!$A$2:$A$1000.

If the name already exists, edit its definition in Name Manager instead of creating a duplicate name.

Why blank cells or errors remain

• Blank rows in the middle: TRIMRANGE trims boundaries. Use a FILTER condition when you want to exclude records based on an empty required field.
• A supposedly empty edge stays: Inspect it for spaces, stray values, and formulas. A cell that looks empty is not always an unused cell; check the formula bar before deciding it is blank.
• #SPILL!: The output area must be clear and outside an Excel Table. Merged cells or existing values can block it.
• #NAME? or _xlfn.TRIMRANGE: Check spelling and whether the function is available in your Excel installation. Use a table or a manually maintained range if it is unavailable.
• An entirely empty source: There is no occupied block to return. Do not assume that an empty source will produce a zero-row list that every downstream formula can use.
• New data does not appear: Confirm it falls inside the original range and that calculation is current.

When the required record field is column A, =FILTER(A2:B1000,A2:A1000<>"","No records") keeps only rows whose A cell is nonempty, including removing gaps in the middle. That rule also excludes any row with a blank A cell and a value in B, so use it only when A is genuinely required. If you prefer to hide rows interactively instead of returning a separate formula result, use the Data tab’s Filter controls.

Before replacing an established range, test a normal list, a gap in the middle, an added record near the boundary, and an empty list. Check the result dimensions as well as its values. That makes it easier to spot a formula that returns the right-looking data but includes the wrong rows or columns.

Matt Jacobs

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.