TRIMRANGE in Excel: How to Remove Blank Cells from Spilled Ranges

Free Excel workbook

Get the TRIMRANGE Practice Workbook

Download the ready-to-use workbook and test the exact formulas from the tutorial.

• Clean spills without trailing zeros or blank edges
• See TRIMRANGE and dot syntax examples already set up
• Practice UNIQUE, VSTACK, and last-12-month formulas fast

Enter your email below for instant access.

Get the Practice Workbook

Free. Instant access.

What is the TRIMRANGE function in Excel?

TRIMRANGE is an Excel function that excludes empty rows and columns from the outer edges of a range. It returns a “trimmed” reference that contains only the cells with data, and it updates automatically when you add or remove rows.

The syntax is simple:

=TRIMRANGE(range, [row_trim_mode], [col_trim_mode])

• range – the range or array to trim.
• row_trim_mode (optional) – 0 = none, 1 = leading, 2 = trailing, 3 = both (default).
• col_trim_mode (optional) – same options for columns. Default is both.

The function is available to Microsoft 365 subscribers on Windows, Mac, and Excel for the web. It will not work in Excel 2021, 2019, or earlier versions.

If you see #NAME? or _xlfn._TRO_ in a downloaded file, your version of Excel does not support it yet.

What is the dot syntax (trim references) in Excel?

Trim references are a shortcut for TRIMRANGE. Instead of wrapping the range in a function, you add a period before or after the colon:

• A2:.A100 – trims trailing blanks (most common).
• A2.:A100 – trims leading blanks.
• A2.:.A100 – trims both leading and trailing blanks.

The dot is on the side of the range you want to trim. Trim references work everywhere TRIMRANGE works, including with full-column references like A:.A, which lets you reference an entire column without the performance cost of pulling in over a million empty cells.

Why do spilled formulas show zeros and blanks?

When you reference a range that has empty cells at the bottom, Excel still spills a result for each one.

Empty cells return 0 by default, which fills your output with junk values.

The same problem hits unique lists, VSTACK across sheets, and any formula that anticipates new rows.

The classic workarounds are Excel Tables (which auto-expand) or FILTER wrapped around the range. TRIMRANGE replaces both for cases where you can’t or don’t want to convert your data to a Table.

How do you reference an entire column without the zeros?

This is the most common use case. You have a list of names in column A and want to mirror it on another sheet so new entries appear automatically.

The old way, =Sheet1!A:A, drags in over a million blank cells as zeros. The fix is one character:

=Sheet1!A:.A

Or with the function:

=TRIMRANGE(Sheet1!A:A)

Both return only the populated cells. Add a new name on the source sheet and it appears on the destination sheet instantly. No table, no named range, no OFFSET formula.

How do you build a clean dynamic numbered list?

Once your spilled range is trimmed, you can chain it into other functions. To number every row in your list, combine SEQUENCE with COUNTA on the spill range:

=SEQUENCE(COUNTA(A1#) -1)

The # after A1 references the entire spill. Subtract 1 to skip the header.

Add a name to the source list and the numbered sequence grows with it. This is the building block for dynamic numbered rows and any report that needs to scale with the data.

How do you get a unique list without trailing zeros?

Reference more rows than you currently need so future entries are picked up, then trim the result:

=UNIQUE(B2 :. B100)

Without the dot, UNIQUE returns the unique values plus a stray zero for the empty cells. With the dot, the empty cells are excluded before UNIQUE evaluates them. The same pattern works for SORT, FILTER, and any other dynamic array function.

How do you combine data from multiple sheets with VSTACK?

Stacking ranges from different sheets is one of the strongest use cases for trim references. Say you have a Staff sheet with 21 rows and a Management sheet with 5 rows, and you want to combine them on an All sheet that updates as either list grows.

Reference extra rows on each sheet, then trim:

=VSTACK(Staff!A3:.E30, Management!A3:.E15)

The dot before each range trims trailing blanks per sheet. Add a new manager on the Management sheet and the All sheet updates automatically.

No empty rows, no zeros, no manual range adjustments.

If you prefer the function syntax:

=VSTACK( TRIMRANGE( Staff!A2:C30), TRIMRANGE( Management! A2:C15))

Both produce the same result. The dot is shorter; TRIMRANGE is more readable when someone else opens the file.

How do you grab the last 12 months for a dynamic chart?

This is the use case that replaces a long, painful FILTER+TAKE formula with one short formula. You have a dataset with monthly dates and a value column. You want a rolling 12-month window that updates when you add a new month.

The full formula:

=TAKE(A6:.B30, -12)

• TAKE with a negative number returns the last N rows.
• The dot syntax trims the trailing blanks before TAKE evaluates the range, so TAKE always pulls the last 12 rows of actual data, not the last 12 cells (which would include blanks).

Plot this spilled result as a line chart and the chart becomes self-updating. Add August data and the chart instantly drops July from a year ago and shows August as the newest point. This is one of the cleanest patterns for KPI dashboards and rolling reports.

TRIMRANGE vs Excel Tables: which should you use?

Tables and TRIMRANGE solve similar problems but in different ways. Use this as a quick decision guide:

• Use a Table when your data is structured, you control the source, and you want headers, total rows, and structured references like Sales[Amount].
• Use TRIMRANGE or trim refs when your data isn’t in a Table (imported data, exports, shared files), when you need spilled array formulas (Tables don’t support them in calculated columns), or when you’re stacking data from multiple sources.

You can also combine the two. TRIMRANGE works fine inside formulas that reference Table data, and it’s especially useful for dynamic data validation lists that need to grow with new entries.

Common limitations to know in 2026

• 3D ranges across sheets are not supported. You can’t write TRIMRANGE(Sheet1:Sheet5!A1:A100).
• Mid-range blanks are not removed. TRIMRANGE only trims from the outer edges. A blank row in the middle of your data stays put. Use FILTER or Power Query for that.
• Empty strings ("") returned by formulas are not treated as blank. If you have =IF(A1="","",something), those "" values won’t be trimmed.
• Conditional formatting “Applies to” field doesn’t recognize the dot. The formula rule itself works.

Want to master Excel’s modern function set?

TRIMRANGE pairs naturally with the dynamic array functions that changed how formulas work: TAKE, VSTACK, FILTER, UNIQUE, SORT, and the recent GROUPBY.

If you want to learn all of them with real-world examples and downloadable practice files, our Modern Excel Functions course walks through each one in depth, and our Black Belt Excel Bundle includes it alongside seven other top courses for a full Excel skill upgrade.

Featured Course

Master Excel’s Essential Modern Functions

FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.

Learn More

Download the free TRIMRANGE practice file

Frequently asked questions

What does the dot do in an Excel formula?

The dot before or after a colon in a range reference (like A2:.A100) is a trim reference. It trims empty cells from the edges of the range. A dot before the colon trims leading blanks, after the colon trims trailing blanks, and on both sides trims both. It’s a shortcut for the TRIMRANGE function.

Is TRIMRANGE available in Excel 2021 or 2019?

No. TRIMRANGE and trim references are exclusive to Microsoft 365 (Excel for the web, Windows, and Mac). They will not be added to perpetual licenses like Excel 2019, 2021, or 2024. If you open a file that uses TRIMRANGE in an unsupported version, you’ll see #NAME? errors and the formula will appear as _xlfn._TRO_TRIMRANGE.

What’s the difference between TRIM and TRIMRANGE?

TRIM removes extra spaces from inside text strings. TRIMRANGE removes empty rows and columns from the edges of a cell range. They share a name but solve different problems. Use TRIM for text cleanup and TRIMRANGE for range cleanup.

Does TRIMRANGE remove blank rows in the middle of my data?

No. TRIMRANGE only trims from the outer edges of the range. Blank rows or columns inside your data are preserved. To remove internal blanks, use FILTER (=FILTER(range, range<>"")) or Power Query.

Why am I getting #NAME? when I type TRIMRANGE?

Three possible causes. First, your Excel version doesn’t support it: TRIMRANGE requires Microsoft 365. Second, you’re on an older Microsoft 365 build: update via File > Account > Update Options. Third, you’re on a corporate install pinned to an older channel by your IT department.

Should I use TRIMRANGE or convert my data to a Table?

Use a Table when you control the source data and want headers, total rows, and structured references. Use TRIMRANGE when you can’t or don’t want to convert to a Table, when you’re stacking ranges from multiple sheets, or when you need spilled array formulas in calculated outputs. Many real-world workbooks use both.

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses