Forum Discussion
Trim Range Operator not working in Range Reference for Pivot Table
I am getting an error message when I try to use Trim Range Operator does not work.
Not sure why? Not sure if there is any resolution or bypass to this?
7 Replies
In this case you may consider to use PIVOTBY() function with TRIMRANGE operator.
It works in my version of Excel but the reference will revert to a static range (e.g. Sheet1!$A:.$B becomes Sheet1!$A$1:$B$16). The only convenience is blanks aren't pivoted initially but that can be avoided by specifying the range or using a table.
On Excel desktop it's better to add data to data model creating PivotTable. Extra blank rows at the end will be cut automatically and source range won't be changed.
On which Excel you are? At least on Excel Desktop for Win it works.
I am using
Microsoft 365 Family
Microsoft® Excel® for Microsoft 365 MSO (Version 2507 Build 16.0.19009.20000) 64-bit
Windows 10 Version 22H2 (OS Build 19045.5965)
Yes, it doesn't work if you are staying in empty Sheet3 start with Insert->PivotTable->add table range on another sheet->Ok
However, if you stay at any cell within TDS Seduction Detail sheet, Insert->PivotTable->add table range->choose where to place PivotTable: Existing worksheet, select cell in Sheet3->Ok when it works.
Alternatively you may create PivotTable directly within TDS Deduction Detail sheet and after that move on to desired place in another sheet.
Please note, as Patrick2788 mentioned range won't be dynamic. It'll be trancated into static range by applied trimming during creation of PivotTable. Aftre that the only way to change source range is to use PivotTable Analysis -> Change Data Source. Thus trimming give nothing.
You are on Beta channel with Family subscription, I believe data model shall be available for it. But I don't see option "Add to data model" on your screenshot, have no idea why. With it source range could be really dynamic since blank rows at the end are cut with that option.
