Instructions for using the TRIMRANGE function to clean up Excel tables

This means that Excel will not return thousands of zeros if the calculation refers to empty cells. Conversely, when you expand the contents of the reference area, the Excel TRIMRANGE function is automatically expanded. Here is a guide to using the Excel TRIMRANGE function.

How to use the TRIMRANGE Excel function

For example, we have the example table below, using the LEN function in Excel to count the number of characters in each cell in column A. You click on any cell and enter the formula =LEN(A:A) as shown.

Instructions for using the TRIMRANGE function to clean up Excel tables Picture 1

And when you press Enter to return the result, you will see that the cells without content will return 0 until the end of column A.

Instructions for using the TRIMRANGE function to clean up Excel tables Picture 2

When using the Excel TRIMRANGE function , the calculation will require Excel to remove all blank cells, trim excess data, and leave only the cells with the data to be calculated.

The TRIMRANGE function formula =LEN(TRIMRANGE(A:A)). When Enter is pressed, the result of the returned data table will remove the zeros as shown below.

Instructions for using the TRIMRANGE function to clean up Excel tables Picture 3

When you continue to add values ​​to column A, Excel automatically returns the corresponding results below.

Instructions for using the TRIMRANGE function to clean up Excel tables Picture 4

Note, this function is currently only available to Microsoft Insider members in the Beta Channel (Version 2409, Build 18020.2000 or later).

Marvin Fry

Update 04 September 2024