- Got Sheet
- Posts
- Dot Array Shortcuts in Excel
Dot Array Shortcuts in Excel
well, technically, trimrange...
Eamonn Cottrell
June 04, 2025 • Estimated Reading Time: 6 minutes
OVERVIEW
TRIMRANGE
TRIM REFS
FILTER
RESOURCES
Some tools to make life easier. You’re welcome :)
beehiiv - my choice for a newsletter operating system
Coefficient - Live connections to 60+ business systems
Lido - Automate your spreadsheets; accurately extract date from PDFs
TransactionPro - quickly import, export or delete data in Quickbooks
Perspective - Build high-converting lead funnel microsites
Gamma - AI designer for presentations, websites, social posts, and more
Gratitude Plus - social gratitude journal
Senja - collect and display testimonials
OpusClip - turn long videos into short clips
Carrd - free one-page website builder
Notion - notetaking + project management + database
Transistor - my favorite podcast host
Fathom - AI-powered notetaking app
MAIN ARTICLE
TRIMRANGE
I love this.
TRIMRANGE will automatically clean up arrays for me. If you’ve ever selected a big array using a formula in Excel, like A:A, you’ll know that there are some potential issues with formatting if there are blank rows of data at the start or the end.
Not to mention that if you’re also wrapping the range with a calculation of some sort, you run the risk of unnecessarily running that calculation on a ton of blank cells.
No more. TRIMRANGE will automatically trim those pieces of it.
We can specify to trim the beginning, the end, or both.
Below is a small example. On the left, we have two columns of data: dates and corresponding events.
To select an array that is longer than the current list so that our formula will include future events but also not return a bunch of zeros at the bottom, we can simply use TRIMRANGE(A2:B18).
This will spill the results down to the last row containing data.
TRIMRANGE in Excel
By default, this trims both leading and trailing blanks, but we could also specify one or the other in the formula itself.
TRIM REFERENCES
I love a good shortcut too.
Trim references allow for us to use dot-notation shorthand to use the same functionality.
Instead of writing =TRIMRANGE(A2:B18), we can write simply =(A2.:.B18). The dot on the left of the colon instructs it to trim the leading blanks, and the dot on the right of the colon instructs it to trim the trailing blanks.
FILTER
You may be wondering about those pesky zeros where there is a date listed in column A but no corresponding event.
You’ll need to use our friend FILTER if you want to omit those. This will
For instance, =FILTER(A2:B18, (A2:A18<>"") * (B2:B18<>"")) will take a look at both column A and column B and only include that row if both are not blank.
filter in excel
I love filter, and the overarching lesson is that there is always a solution. You just may have to dig a little further to get it.
I’ll include the full documentation for TRIMRANGE in case you need the textbook version of the trim stuff below👇
From the Documentation
Here is the summary from the documentation in Excel:
=TRIMRANGE(range,[trim_rows],[trim_cols])
Argument | Description |
|---|---|
range Required | The range (or array) to be trimmed |
trim_rows | Determines which rows should be trimmed 0 - None 1 - Trims leading blank rows 2 - Trims trailing blank rows 3 - Trims both leading and trailing blank rows (default) |
trim_columns | Determines which columns should be trimmed 0 - None 1 - Trims leading blank columns 2 - Trims trailing blank columns 3 - Trims both leading and trailing blank columns (default) |
Trim References (aka Trim Refs)
A Trim Ref can be used to achieve the same functionality as TRIMRANGE more succinctly by replacing the range's colon ":" with one of the three Trim Ref types described below:
Type | Example | Equivalent TRIMRANGE | Description |
|---|---|---|---|
Trim All (.:.) | A1.:.E10 | TRIMRANGE(A1:E10,3,3) | Trim leading and trailing blanks |
Trim Trailing (:.) | A1:.E10 | TRIMRANGE(A1:E10,2,2) | Trim trailing blanks |
Trim Leading (.:) | A1.:Z10 | TRIMRANGE(A1:E10,1,1) | Trim leading blanks |
This pattern can also be applied to full-column or -row references (eg. A:.A)
NEXT STEPS
Whenever you’re ready, here’s how I can help:
Business tech stack (FREE)
My recommendations for software and tools I use to create content and run my online business.Sponsor Got Sheet
Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.Personal budget tool
As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials
Reply