Pain Free Way to Hide Excel Pivot Table Items

Remember to enter the Excel 2010 Future of Productivity Giveaway — it ends at noon (Eastern time) on Tuesday, June 8th. You could win a copy of Microsoft Office 2010 (with fancy new pivot table features), a Flip video camera, and a Seagate 1TB hard drive.

If you’re working with an Excel 2007 pivot table, you might want to temporarily hide one or more of the items in a Row field or Column field. To do that, you probably click the drop down arrow for the Row or Column Labels, then remove the check mark for items you want to remove. That method works, but if the items aren’t in ascending or descending order, it can be a pain to find the item you’re looking.

pivothidesel01

Quickly Hide Items

Excel 2007 has enhanced filtering in pivot tables, and this can save you some time. Instead of searching through a long list of items in a drop down list, you can use a right-click command to hide the selected items. You can do this with one selected item, or several adjacent items.

  1. Right-click on an item in the Row Labels or Column Labels
  2. In the pop-up menu, click Filter, and then click Hide Selected Items.
  3. The item is immediately hidden in the pivot table.

pivothidesel02

Quickly Hide All But a Few Items

You can use a similar technique to hide most of the items in the Row Labels or Column Labels. This is an even bigger time-saver than the Hide Selected Items trick.

  1. Select the pivot table items that you want to keep visible
  2. Right-click on one of the selected items
  3. In the pop-up menu, click Filter, then click Keep Only Selected Items.
  4. All but the selected items are immediately hidden in the pivot table.

pivothidesel03

Watch the Hide Items Video

To see the steps for quickly hiding or keeping the selected items in an Excel 2007 Pivot Table, you can watch this short video tutorial.

____________

You may also like...

6 Responses

  1. Ewoud Vogels says:

    Hi Debra,
    This is a great tip, thank you!
    I always used the filter, unchecked all items and the clicked only one (without checkin) so that I could type the item I was looking for. Excel then searches in the items-list the items that matches the typed input.
    This method worked for finding a single item (and checkin it) but hot the other way around (when you need the complete list but want to uncheck one or two single items). And your method does!
    Thanks again,
    Ewoud

  2. Contextures Blog » Easy Filtering in Excel 2007 says:

    […] month you saw a quick way to filter for the selected item in a pivot table, and today you’ll see a similar technique for a worksheet list in Excel 2007. (For the Excel 2003 […]

  3. Khushnood Viccaji says:

    Hi Debra, GREAT tips. Thanks !

    Just a quick addition of my two-paisa to this tip:
    To quickly hide the selected item(s) in the pivot table, the keyboard short-cut is Ctrl + – (hyphen).
    The advantage of this method is that you can hide one or more items or columns easily.

    Cheers!
    Khushnood Viccaji
    Mumbai, India

  4. Debra: Is there a painless way to hide COLUMNS in an Excel 2010 pivot table?

    Doug in York PA

  5. Jannik Magelund Madsen says:

    Dear all,

    I am looking for a way to hide specific values in a column in a pivot table so these doesn’t show in the associated pivot table chart. My task is to create a chart that shows “Realized” vs. “Planned” production. Realized has to show accumulated Year-to-week values (bar chart type), whereas Plan has to show the accumulated plan for the full year (line chart type). Problem is that when I use display values as “Running total in calendar week” it will accumulate for the weeks that has no production. E.g. week 40 is the latest production week with data. Let’s say that running total amounts to 1.000. Week 41 will has zero data, but it will show 1.000 because excel will add “0” to 1.000 – and it will repeat this pattern until week 52. However in the chart I do not want to show anything from week 40 and onwards until there is realized data. Hope you understand the issue. A manual fix is to click on the individual bars in the chart and choose “white” as fill colour as this will make them look blank – however I’m hoping there is an easier way as this takes a lot of time – especially if you are starting in week 1 ;-)

    Looking forward to your help!!

    br

    Jannik

Leave a Reply to Debra Dalgleish Cancel reply

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