How to hide expand collapse buttons in pivot table?
In Excel, pivot tables are essential tools that allow users to analyze and summarize large data sets efficiently. When you create a pivot table, Excel adds expand and collapse buttons (also known as +/- buttons) by default. These buttons enable users to easily expand or collapse data to view detailed or summary information, as demonstrated in the screenshot below. However, you might occasionally want to restrict users from expanding or collapsing grouped items—for example, when sharing a report with colleagues or clients where data visibility should remain consistent. In such cases, hiding or disabling these expand and collapse controls ensures your pivot table remains in the preferred view.
Hide expand and collapse buttons with toggling the +/- Buttons
Hide expand and collapse buttons with PivotTable Options
Hide expand and collapse buttons using VBA code solution
Hide expand and collapse buttons with toggling the +/- Buttons
This method allows you to quickly hide or show the expand and collapse buttons directly from the ribbon when working in your pivot table. It's simple and effective for scenarios where you need to switch the view on and off without navigating through additional settings. This approach is best used for situations where fast toggling is required, but note it applies only to the selected pivot table at a time.
To hide the expand and collapse (±) buttons, follow these steps:
1. Click any cell within your pivot table to activate the PivotTable Tools on the ribbon. Ensure you are clicked inside the correct pivot table, especially if your worksheet contains more than one pivot table.
2. Under the PivotTable Analyze or Options tab (depending on your version of Excel), locate and click the +/- Buttons command, usually found within the 'Show' group. This will instantly hide the expand and collapse buttons for the current pivot table. Please see the sequence illustrated below:
 |
 |
 |
After executing this step, the pivot table will still function normally, but users will no longer have the option to manually expand or collapse grouped rows or columns. This can help preserve the intended structure of your summarized data.
Tip: To restore the expand and collapse buttons, simply click the +/- Buttons command again; this will toggle them back on. Remember, this change only affects the active pivot table you are working on.
Note: This method does not prevent users from using keyboard shortcuts or other methods to manipulate field groupings—it only hides the clickable interface buttons.
Hide expand and collapse buttons with PivotTable Options
For more granular control over the appearance of expand/collapse buttons—especially when you want the setting to persist or be less obvious to other users—you can configure this feature through the PivotTable Options dialog box. This approach is useful if you're preparing a final version of a pivot table for sharing, or if you want to align with specific reporting requirements. However, as with the previous method, it affects only the selected pivot table.
1. Right-click any cell inside your pivot table, and then select PivotTable Options from the context menu. This opens the PivotTable Options dialog where you can customize how your pivot table looks and behaves. Refer to the image for guidance:
2. Within the PivotTable Options dialog box, switch to the Display tab. In the Display section, you will find the option labeled Show expand/collapse buttons. Remove the checkmark by clicking the checkbox so that expand/collapse buttons are no longer displayed, as shown below:
3. Click OK to save your changes and close the dialog box. The expand and collapse buttons will disappear immediately from the pivot table, and users will not be able to interact with grouping levels via the buttons.
This setting is particularly useful if you are finalizing a report for stakeholders who should view a fixed summary or detail level, without the option to drill up or down.
Tip: To turn the expand and collapse buttons back on at a later time, repeat the above steps and simply check the Show expand/collapse buttons option before clicking OK.
Note: Both this method and the previous ribbon toggling method must be repeated for each pivot table individually; there is no native way in Excel to apply these settings to all pivot tables on a worksheet or in a workbook at once.
Practical reminder: If your pivot table appears not to update immediately after you change this setting, try refreshing the pivot table by right-clicking within the table and selecting Refresh. In addition, these settings only affect visual interaction—they do not impact the underlying data or summarizations.
Hide expand and collapse buttons using VBA code solution
In some cases, such as when you need to automate the process or hide expand/collapse buttons across multiple pivot tables at once, using a VBA macro can be extremely helpful. This solution is particularly valuable for advanced users or for anyone frequently working with complex reports. VBA allows for consistent application of pivot table settings throughout the workbook with minimal manual effort.
1. To use this method, first access the VBA editor by clicking Developer > Visual Basic. In the window that pops up, click Insert > Module and paste the following code into the opened module:
Sub HidePivotExpandCollapseButtons()
'Updated by Extendoffice 2025/8/8
Dim ws As Worksheet
Dim pt As PivotTable
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.ShowDrillIndicators = False ' Use False/True to hide/show the expand collapse buttons
pt.RefreshTable
Next pt
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "All expand/collapse (+/-) buttons in PivotTables are now hidden.", vbInformation, xTitleId
End Sub2. After the code is entered, click the 
button on the VBA toolbar to execute the macro. Then a message box will then pop up telling you that all expand and collapse buttons have been hidden, click OK to close it.
The expand and collapse buttons for all pivot tables throughout every worksheet in your workbook are now hidden.
Tip: To show the expand and collapse buttons again for all pivot tables, you can use the same code, but set pt.ShowDrillIndicators = True instead.
Cautions: Remember to save your workbook before running any macros, and enable macros when prompted. If you are unfamiliar with VBA, it is recommended to test your macro on a copy of your file.
When working with pivot tables, hiding expand and collapse buttons can improve presentation clarity and prevent unwanted changes to the reported data structure. However, hiding these buttons only limits the ability to expand or collapse data through the user interface; it does not remove or change the grouping of your data. If the buttons are not hiding as expected, ensure the PivotTable is active and refer to the troubleshooting tips above. For users who need even more control, leveraging Excel VBA makes it possible to apply changes across the entire workbook at once. Always consider the needs of your data audience and whether other users require the ability to drill deeper into the information.
Related articles:
How to preserve formatting after refreshing pivot table?
How to repeat row labels for group in pivot table?
How to show multiple grand totals in pivot table?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in