Function Problems

Does Excel TRIMRANGE Exclude Formula Cells That Return Blank?

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to know if the TRIMRANGE function ignores cells that appear empty but actually contain a formula returning an empty string, and seeks a method to retrieve the last visible non-blank value in a row.

Product: Excel
Device & OS: not provided
Scenario: Filtering a data range to find the last actual value while bypassing cells populated with formulas that evaluate to an empty string.
Observed behavior: TRIMRANGE does not remove cells containing formulas, even when those formulas return an empty string, preventing the user from dynamically trimming the visual blanks.

Before you start

Verify that you are using a version of Excel that supports Dynamic Array functions like LET, FILTER, and TAKE, as these are required for the workaround.

Solution 1Recommended

Use FILTER and TAKE to Extract the Last Non-Blank Value

Since TRIMRANGE only excludes completely empty cells, you can combine the FILTER and TAKE functions to dynamically ignore empty strings generated by formulas.

The TRIMRANGE function is specifically designed to exclude cells that are truly empty (containing neither values nor formulas). Because a formula returning an empty string ("") is still technically cell content, TRIMRANGE retains it.

To bypass this limitation and get the last visible value, you can use the FILTER function to exclude empty strings, and then use the TAKE function to extract the last item from that filtered list.

Select the target cell

Click on the cell where you want the final extracted value to be displayed.

Enter the dynamic array formula

Type the following formula into the formula bar: =LET(range, B2:Z2, TAKE(FILTER(range, range<>""),, -1))

Apply the formula

Press Enter. The formula will evaluate the range from B2 to Z2, filter out any cells that equal an empty string, and pull the last remaining value from the row.

Customizing the Range: You can change 'B2:Z2' in the LET function to match the specific row or column range you are working with in your dataset.

Advanced Formula Support

Handle Dynamic Arrays and Complex Formulas Smoothly in WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive support for modern array formulas and complex data processing tasks, making it easy to filter, trim, and analyze your data efficiently.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Select the result cell: Click on the cell where you wish to display the extracted value.
3. Input the array formula: Enter your LET, FILTER, and TAKE functions exactly as you would in standard spreadsheet software.
4. Calculate instantly: Press Enter to instantly process the array and return the last non-blank value.

High compatibility with Microsoft Excel functions and .xlsx formatsSupports advanced array formulas for seamless data manipulationLightweight, fast, and completely free to useFamiliar interface ensures zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does TRIMRANGE count formula cells as non-blank?

Excel treats any cell that contains data, including an underlying formula, as non-blank. The TRIMRANGE function looks at the cell's actual contents (the formula itself) rather than the resulting output (the empty string).

What does the FILTER function do in this workaround?

The FILTER function checks the specified range and removes any cells that evaluate to an empty string by using the logical condition 'range<>""'.

Can I use this method to find the first non-blank value instead?

Yes. By changing the last argument in the TAKE function from -1 to 1 (e.g., TAKE(...,,1)), you can retrieve the first non-blank value from the filtered range.

What does the LET function do in this formula?

The LET function allows you to assign a name ('range') to your cell reference (B2:Z2). This makes the formula shorter, easier to read, and prevents Excel from having to calculate the same range reference multiple times.

Excel & Data Problems