Uploaded March 2025 | Updated September 2026, 4 days ago
🔥 500,000+ professionals trust our courses—become an Excel Black Belt 👉 xelplus.com/course/excel-black-belt
or checkout our other courses: xelplus.com/courses
Stop fighting with empty cells and zeros in your Excel reports. This video introduces the revolutionary "Trim Refs" (dot notation) and the TRIMRANGE function—a feature designed to automatically clean leading and trailing empty cells from your dynamic arrays.
🔗 Get my free practice file here: xelplus.com/trimrange-function-excel/#download
TRIMRANGE is a new feature in Excel 365 that allows you to reference an entire column while "trimming" the empty spaces. By adding a simple dot (.) to your range reference (e.g., A:A.), you can keep your reports dynamic without the clutter of unwanted zero values.
What You’ll Master in This Tutorial:
- The Dot Syntax: How to use leading and trailing dots to clean ranges instantly.
- Dynamic Stacking: Using VSTACK with TRIMRANGE to combine sheets without the "zero" headache.
- Modern Reporting: Replacing complex OFFSET and TAKE(FILTER()) formulas with a single character.
- Visualization: Building line charts that automatically update as you add data, with no "empty tail" at the end.
00:00 The problem: Why referencing entire columns creates messy zeros
00:59 The "One Dot" Trick: Introducing TRIMRANGE syntax
02:31 Creating dynamic numbered lists with spilled ranges
03:14 How to use the TRIMRANGE function (Function syntax)
04:15 Pro Tip: Getting a clean UNIQUE list without the "Zero" bug
05:01 VSTACK + Dots: Combining data from multiple sheets effortlessly
07:27 Get the Last 12 Months of Data with TAKE
Availability: Excel for Microsoft 365 - TRIMRANGE is generally available.
In this video, you’ll discover:
- How the dot operator (Trim Refs) instantly removes trailing and leading empty cells
- How TRIMRANGE automatically expands/contracts with your data — no more OFFSET or INDEX hacks
- A cleaner way to create dynamic lists, charts, and reports
- How to combine data across sheets without dragging blank rows along
- The modern, simple formula to get the last 12 months of data
- When to use TRIMRANGE vs. Excel Tables (and why you might switch depending on the task)
📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: link.xelplus.com/yt-d-newsletter
➡️ Join this channel to get access to perks:
youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
🎬 LINKS to related videos:
Excel GROUPBY & PIVOTBY Functions - All You Need to Know: youtu.be/mCJzhCxEQlM
Excel TRANSLATE Function: youtu.be/AQgVr4euJ4U
👕 Get the Official XelPlus Merch: xelplus.creator-spring.com
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: xelplus.com/course-quiz
🎥 RESOURCES I recommend: xelplus.com/resources
More resources on my Amazon page: amazon.com/shop/leilagharani
🚩Let’s connect on social:
Instagram: instagram.com/lgharani
LinkedIn: linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
🔥 500,000+ professionals trust our courses—become an Excel Black Belt 👉 xelplus.com/course/excel-black-belt
or checkout our other courses: xelplus.com/courses
Stop fighting with empty cells and zeros in your Excel reports. This video introduces the revolutionary "Trim Refs" (dot notation) and the TRIMRANGE function—a feature designed to automatically clean leading and trailing empty cells from your dynamic arrays.
🔗 Get my free practice file here: xelplus.com/trimrange-function-excel/#download
TRIMRANGE is a new feature in Excel 365 that allows you to reference an entire column while "trimming" the empty spaces. By adding a simple dot (.) to your range reference (e.g., A:A.), you can keep your reports dynamic without the clutter of unwanted zero values.
What You’ll Master in This Tutorial:
- The Dot Syntax: How to use leading and trailing dots to clean ranges instantly.
- Dynamic Stacking: Using VSTACK with TRIMRANGE to combine sheets without the "zero" headache.
- Modern Reporting: Replacing complex OFFSET and TAKE(FILTER()) formulas with a single character.
- Visualization: Building line charts that automatically update as you add data, with no "empty tail" at the end.
00:00 The problem: Why referencing entire columns creates messy zeros
00:59 The "One Dot" Trick: Introducing TRIMRANGE syntax
02:31 Creating dynamic numbered lists with spilled ranges
03:14 How to use the TRIMRANGE function (Function syntax)
04:15 Pro Tip: Getting a clean UNIQUE list without the "Zero" bug
05:01 VSTACK + Dots: Combining data from multiple sheets effortlessly
07:27 Get the Last 12 Months of Data with TAKE
Availability: Excel for Microsoft 365 - TRIMRANGE is generally available.
In this video, you’ll discover:
- How the dot operator (Trim Refs) instantly removes trailing and leading empty cells
- How TRIMRANGE automatically expands/contracts with your data — no more OFFSET or INDEX hacks
- A cleaner way to create dynamic lists, charts, and reports
- How to combine data across sheets without dragging blank rows along
- The modern, simple formula to get the last 12 months of data
- When to use TRIMRANGE vs. Excel Tables (and why you might switch depending on the task)
📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: link.xelplus.com/yt-d-newsletter
➡️ Join this channel to get access to perks:
youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
🎬 LINKS to related videos:
Excel GROUPBY & PIVOTBY Functions - All You Need to Know: youtu.be/mCJzhCxEQlM
Excel TRANSLATE Function: youtu.be/AQgVr4euJ4U
👕 Get the Official XelPlus Merch: xelplus.creator-spring.com
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: xelplus.com/course-quiz
🎥 RESOURCES I recommend: xelplus.com/resources
More resources on my Amazon page: amazon.com/shop/leilagharani
🚩Let’s connect on social:
Instagram: instagram.com/lgharani
LinkedIn: linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
