Hide Drop-Down Arrows in Excel Pivot Table

After you set up a pivot table, you might want to discourage people from filtering the pivot fields, which could hide some of the data. This video shows a couple of ways hide the drop down arrows in the pivot table headings.

Change a Pivot Table Setting

Here’s first way to hide the heading arrows. It’s quick and easy, but you might not like the results.

Right-click any cell in the pivot table
In the pop-up menu, click PivotTable Options
In the PivotTable Options dialog box, go to the Display tab
In the Display section, remove the check mark from Display field captions and filter drop downs
Click the OK button, to apply the setting, and to close the dialog box.

Now, the heading arrows are gone, but so is the heading text!

To put the arrows and heading text back:

Use the Undo shortcut – Ctrl + Z
Or, go back into the option settings, and add the check mark that you removed.

Use a Macro to Hide Arrows

Instead of changing that pivot table option, you can use a macro to hide those drop down arrows.

This gives you better control over what is hidden – you could hide some arrows, and leave others showing.

For example, in this screen shot, the Region arrow is showing, but all the arrows are hidden.

Video: Hide Selection Arrows in Pivot Table

There’s sample code on my Contextures site, on the Pivot Table Protection page, to hide those arrows.

Watch this video to see how to see how the code works, and the steps for getting that macro code, into your Excel workbook.

Video Timeline

0:00 Introduction
0:18 Hide Arrows with Setting
0:55 Hide Arrows with Macro
1:44 Code Overview
2:39 Copy and Paste Macro Code
3:49 Test the Code
4:21 Add Code to Show Arrows
4:52 Get the Workbook

Get the Sample File

You can get the Excel file with the macro code on my Contextures site, on the Pivot Table Protection page

The zipped file is in Excel xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macros.

Note: Before you unzip the files, follow the steps below, to unblock the files

In Windows Explorer, right-click on the zipped file
In the pop up menu, click Properties
Add a check mark in the Unblock box, near the bottom of the Properties window.
Click OK, to close the Properties window.

Due to Windows security settings, files that you get from the internet might not work correctly, unless you unblock them, or store them in a Trusted Location.

_________________