“Can you reference an entire column in an Excel formula?”. I get asked this question quite often. The answer is yes. You can write formulas like these:
=SUM(A:A)
=XLOOKUP(A1, C:C, E:E)
However it’s not considered best practice. Why? Because referencing an entire column is inefficient. Excel has to check every single cell, even if most of them are empty! Since each column contains over a million rows, this can significantly slow down calculations.
This is where Excel’s new TRIMRANGE function comes in. It automatically removes empty rows and columns from a range, ensuring that Excel only processes the data-containing cells.
Why Do People Reference Entire Columns?
Many users reference entire columns because their data grows over time. If new rows are added, they want their formulas to update automatically without manually adjusting ranges.
Best practice is to convert your list into an Excel Table. Tables dynamically expand when new data is added, ensuring your formulas always reference the correct range. You can learn more about Tables in this tutorial. However, there are situations where Tables aren’t practical.
The TRIMRANGE function was introduced in the Microsoft Beta Insider Channel in September 2024 and released to the Current Channel in early 2025.
How TRIMRANGE Works
Let’s say you have numbers in A1:A4:
100
200
300
400
If you use the formula =SUM(A:A), Excel calculates the sum of all 1,048,576 rows, even though most of them are empty. While the result is still 1000, Excel is performing unnecessary calculations.
Now, use TRIMRANGE: =SUM(TRIMRANGE(A:A))
This is the equivalent of =SUM(A1:A4), making your formula far more efficient. Plus, if you add a value to A5, TRIMRANGE automatically includes it, just like an Excel Table would.
Why Use TRIMRANGE?
✅ Improves Performance – eliminates unnecessary calculations on blank cells.
✅ Auto-Expands – includes new data as you add rows.
✅ Works Without Tables – a great alternative when using structured references isn’t an option.
For a demo of TRIMRANGE check out the video below
Download a copy of the file(s) used in this video: https://share.zight.com/Blum8Qg5