Hide Pivot Table Buttons and Labels

If you’re sharing an Excel pivot table with colleagues who aren’t too skilled in Excel, you might want to hide some of the buttons and drop downs in the pivot table before you send it.

For example, in the pivot table shown below, the sales are summarized by city and product, and has filter buttons and expand/collapse buttons showing.

PivotButtons01

Hide the Buttons

You can change the pivot table options to hide some of the buttons and captions in the pivot table. Follow these steps to hide the buttons:

  1. Right-click a cell in the pivot table and, in the popup menu, click PivotTable Options.
  2. On the Display tab, remove the check mark from Show Expand/Collapse Buttons. This
    hides the buttons to the left of the outer Row Labels and Column Labels.PivotButtons02
  3. Remove the check mark from Display Field Captions and Filter Drop Downs. This
    hides the filter buttons, and the Row Labels and Column Labels captions.
  4. Click OK to close the PivotTable Options dialog box.

Without the buttons and labels, the pivot table looks cleaner, and may be easier for your colleagues to read.

PivotButtons03

_____________

You may also like...

12 Responses

  1. Jon Peltier says:

    If the colleagues are truly unskilled, there’s another approach which may be more reliable: Copy – Paste Special Values.

  2. Jon, good solution in some cases, but you can’t paste the pivot table formatting, which is a nuisance.

  3. Jon Peltier says:

    You can copy the entire pivot table, paste it the regular way, then immediately paste special – values.

  4. Are you using an old version of Excel? Pivot table formatting can’t be pasted in Excel 2007 — or I haven’t discovered how to do it.

  5. Jon Peltier says:

    Doesn’t the formatting go with a pivot table when you copy it and paste it? Or is that another thing they broke in 2007?

    Okay, I tested it. I copied and pasted the pivot table, and it looked just like the original. Then I pasted values, and the formatting went away! In 2003 the pivot table formatting would have stayed after the paste special.

  6. Here’s how to copy a pivot table (as values) and retain the formatting:

    http://spreadsheetpage.com/index.php/tip/unlinking_a_pivot_table_from_its_source_data/

    It’s the only time the Office Clipboard is useful for anyting.

  7. Thanks John and Elias, good to know that the Clipboard has a purpose!

  8. Contextures Blog » Pivot Table Formatting Old Style says:

    […] Hide Pivot Table Buttons and Labels […]

  9. MK says:

    Is there a way to display the row labels without the drop down buttons?

  10. MK, you can use programming to hide the drop down arrows. There’s an example here:

    Disable Pivot Table Selection

  11. MK says:

    Wonderful. Thanks very much. It makes presenting the report to managers much cleaner.

    By the way, heartiest compliments on your website. It is a powerhouse of information.

Leave a Reply to Elias Cancel reply

Your email address will not be published. Required fields are marked *