How to Remove Expand/Collapse in Excel Pivot

The expand and collapse buttons in an Excel PivotTable - those little plus and minus signs next to your row labels - are great for drilling down into your data. But when it's time to present your findings, they can clutter up a polished report. This guide will walk you through several quick and easy methods to hide these buttons, from a simple mouse click to a bit of automation for more complex workbooks.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Remove the Expand/Collapse Buttons in a PivotTable?

Before we jump into the "how," let's briefly touch on the "why." While incredibly useful for a data analyst actively exploring information, those +/- buttons aren't always ideal for a final report. Here are the most common reasons you'd want to hide them:

• Creating a Clean Presentation: The primary reason is aesthetics. Removing the buttons gives your report a clean, static, and professional look, making it easier for your audience (like leadership or clients) to read without getting distracted by interactive elements.
• Simplifying the View: For stakeholders who aren't familiar with PivotTable mechanics, the buttons can be confusing. Hiding them presents the data as a straightforward summary table, which is often all they need to see.
• Preventing Accidental Changes: When you share an Excel file, you might not want users to accidentally expand or collapse rows, which could change the layout and the focus of your report. Hiding the buttons helps lock in the view you intended to share.
• Formatting for Dashboards: If your PivotTable is part of a larger Excel dashboard, removing the +/- signs ensures a consistent, non-interactive appearance that aligns with other dashboard elements like charts and summary cards.

Ultimately, it’s about shifting the PivotTable from an analytical tool to a presentation-ready report. Now, let’s look at how to do it.

Method 1: The Quickest Fix Using the PivotTable Analyze Tab

This is the most common and direct way to hide the expand/collapse buttons. It’s built right into the Excel ribbon and takes just a couple of clicks.

Step-by-Step Instructions:

1. Select your PivotTable: Click anywhere inside the PivotTable you want to modify. This will activate the "PivotTable Analyze" and "Design" tabs in the Excel ribbon at the top of your screen.
2. Navigate to the PivotTable Analyze Tab: Click on the "PivotTable Analyze" tab. Note: In some older versions of Excel (2010 or earlier), this tab might be named "Options."
3. Find the '+/- Buttons' Option: Look to the far right of the PivotTable Analyze ribbon. You'll see a group called "Show." Within this group, there is a button labeled "+/- Buttons."
4. Click to Hide: Simply click the "+/- Buttons" button. The expand and collapse icons will instantly disappear from your PivotTable.

That’s it! This function works as a toggle. If you ever need to bring the buttons back for further analysis, just click inside the PivotTable and press the "+/- Buttons" button again to make them reappear.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Using the PivotTable Options Menu

This alternative method achieves the exact same result as the first one but through a different interface — the PivotTable Options dialog box. Some people prefer this right-click approach, as this menu contains many other useful settings for customizing a PivotTable's behavior.

Step-by-Step Instructions:

1. Right-Click the PivotTable: Position your cursor anywhere inside your PivotTable and right-click to open the context menu.
2. Select 'PivotTable Options...': From the list that appears, choose "PivotTable Options...". This will open a new dialog window with multiple tabs.
3. Go to the 'Display' Tab: In the PivotTable Options window, click on the "Display" tab.
4. Uncheck the Box: Look for the checkbox labeled "Show expand/collapse buttons." It will be checked by default. Click on it to uncheck it.
5. Confirm Your Choice: Click the "OK" button at the bottom of the window to apply the change. The buttons will now be hidden from your report.

Like the ribbon method, this is easily reversible. Just follow the same steps and re-check the "Show expand/collapse buttons" box to bring them back.

Method 3: Turn Off Buttons on All PivotTables with a VBA Macro

What if you have a workbook with dozens of PivotTables across multiple sheets? Toggling the buttons off one by one would be tedious. This is where a simple VBA (Visual Basic for Applications) macro can save you a significant amount of time by turning them all off at once.

Don’t worry if you’ve never written code before. You can copy and paste this script directly.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Instructions:

1. Open the VBA Editor: On your keyboard, press Alt + F11. This will open the VBA editor in a new window.
2. Insert a New Module: In the VBA editor’s top menu, click "Insert" and then select "Module." A blank white code window will appear on the right side.
3. Copy and Paste the Code: Copy the code below and paste it into the new module window you just created.

Sub HideAllPivotButtons()
    Dim ws As Worksheet
    Dim pt As PivotTable

    On Error Resume Next
    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each PivotTable on the worksheet
        For Each pt In ws.PivotTables
            ' Set the property to hide the expand/collapse buttons
            pt.ShowDrillIndicators = False
        Next pt
    Next ws
    On Error GoTo 0

    MsgBox "All expand/collapse buttons have been hidden.", vbInformation

End Sub

1. Run the Macro: To execute the code, press the F5 key on your keyboard or click the green "run" triangle icon in the toolbar. Alternatively, you can close the VBA editor, go back to your Excel workbook, press Alt + F8, select "HideAllPivotButtons" from the list, and click "Run."

A pop-up message will indicate the process has been completed. You can check your workbook’s PivotTables — all buttons will be hidden. You can bring the buttons back by reversing the code, changing pt.ShowDrillIndicators = False to pt.ShowDrillIndicators = True and running it as a new macro.

Important Note: To save a workbook with a macro, you must save it as an "Excel Macro-Enabled Workbook" (with a .xlsm file extension). Otherwise, the code will be removed from the file.

Method 4: For a Truly Static Report, Convert to Values

Sometimes, your goal isn’t just to hide the buttons, it’s to create a completely non-interactive, static version of your report. This is especially useful when sending a final version to executives or clients where you don’t want them to have access to the underlying PivotTable data or functionality. In this case, the best solution is to copy the PivotTable and paste it as static values.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions:

1. Select the Entire PivotTable: Click inside your PivotTable. Then, press Ctrl + A to select the entire report area.
2. Copy the Data: Press Ctrl + C to copy the selected data.
3. Choose a Destination: Click on an empty cell where you want to place the static report. This can be in the same worksheet or a new one entirely.
4. Use Paste Special: Right-click on the destination cell and find "Paste Special" in the context menu (often depicted by a clipboard with "123"). Choose the option for "Values & Source Formatting" or just "Values" to paste the data without PivotTable functionality.

The result is a regular range of cells that looks identical to your formatted PivotTable but has no interactive elements. The expand/collapse buttons are no longer present because it’s no longer a PivotTable. Be aware that this process is one-way — the new table is disconnected from your source data and will not update if the source data changes.

Final Thoughts

Removing the expand and collapse buttons from your Excel PivotTables is a simple but effective technique to transform your data analysis workspace into a polished, presentation-ready report. Whether you prefer a quick click on the ribbon, a VBA macro for mass changes, or converting the table to static values, you now have the tools needed to tailor your reports for any audience.

The effort we put into formatting reports in tools like Excel is all about making data clear and actionable for others. We built Graphed to simplify that process entirely. Instead of pulling data, building PivotTables, and then spending more time cleaning them up for presentation, we enable you to instantly create clean, shareable dashboards directly from your live data sources using natural language. It removes the manual steps, so you can focus on the insights, not just the formatting.