How can I hide or collapse subtotal rows in an Excel PivotTable?

Question

I'm wondering if this is possible, as with pivot tables in excel there is an option to expand or collapse all fields. If you try to record this in Automate, it says this action cannot be recorded. A work around I'm currently using is this code:


// Collapse pivot item "APAC" in pivot "Region"
newPivotTable.getHierarchy("Region")
             .getFields()[0]
             .getPivotItem("APAC")
             .setIsExpanded(false);

However it requires you to name each pivot item in order to collapse them. I'm looking for a way for it to collapse all pivot items dynamically without having to hard code them.

taller · Accepted Answer · 2025-10-26 01:36:08Z

The script can dynamically collapse all pivot items without the need to hard-code their names.

function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook.getWorksheets()[0];
    // Get the first PivotTable on sheet
    const pivotTable = sheet.getPivotTables()[0];
    // Access the row hierarchy
    const rowHierarchies = pivotTable.getRowHierarchies();

    const TARGET = "Region"; // modify as needed
    for (let i = 0; i < rowHierarchies.length; i++) {
        const hierarchy = rowHierarchies[i];
        if (hierarchy.getName() === TARGET) {
            // Get the field
            const field = hierarchy.getFields()[0];
            // Get the items
            const items = field.getItems();
            for (let k = 0; k < items.length; k++) {
                const item = items[k];
                if (item.getIsExpanded()) {
                    item.setIsExpanded(false)
                }
            }
        }
    }
}

The revised script below allows toggling between collapsed and expanded states.

            for (let k = 0; k < items.length; k++) {
                const item = items[k];
                // if (item.getIsExpanded()) {
                //     item.setIsExpanded(false)
                // }
                item.setIsExpanded(!item.getIsExpanded())
            }

Collectives™ on Stack Overflow

How can I hide or collapse subtotal rows in an Excel PivotTable?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related