At the very least, you probably already use paste special to strip out unwanted formatting and formulas (Paste special > Values). There are so many things you can do with paste special it's a topic in itself. At that point, you'll need to chose the type of paste you want to perform. This shortcut, Control + Alt + V (Mac: Control + Command + V) doesn't actually finish the paste it just opens the Paste Special dialog box. It shows a few different ways that you can manually expand and collapse the pivot items in an Excel pivot table.Video: 30 popular Excel shortcuts in 12 minutes Display the Paste Special dialog box You’ll also find sample files and pivot table macro examples that help you show or hide the pivot table details.Īnd for a quick overview of hiding and showing details in a pivot table, watch this short video. There are more Pivot Table Collapse/Expand tips on my Contextures website. NOTE: If you add a field accidentally, click the Undo button, or press Ctrl+Z, to remove it. In the screen shot below, Store was added to the pivot table. If you select one of the fields, it is added to the Row area, as the new Inner field. If you point to a cell in the Inner field, Product, press Shift and scroll UP to expand, you’ll see the Show Details dialog box. If you continue to press Shift and scroll DOWN, the remaining fields will collapse, one by one.Instead, the pivot table collapses to the last outer field – Category.Product is the Inner field, at the far right of the Row area.īecause Product is the inner field, there are no details below it to collapse. To expand the pivot table again, point to a cell in the Category field, press Shift, and scroll the mouse wheel UP The pivot table details are hidden for the Category and Product fields.Press Shift and scroll the mouse wheel DOWN, once.To expand the pivot table again, point to a cell in the Region field, press Shift, and scroll the mouse wheel UPĬategory is the third outer field, below Region and City the Row area. Press Shift and scroll the mouse wheel DOWN, to collapse the pivot table details down to the Region field.Region is the first outer field, at the far left of the Row area. Here are a couple of examples of what happens when you use the Shift and Scroll shortcut. In this pivot table, Region, City and Category are outer fields, and Product is the inner field. Inner fields – have no fields below them in the PivotTable Field List.Outer fields – have one or more fields below them in the PivotTable Field List.There are 2 types of fields in the Row area: There are a few examples shown in the next section. You’ll get different results, based on the cell you’re pointing at, so experiment to see how it works. TIP: When collapsing, point to a cell near the top of the pivot table, so the pointer doesn’t end up outside of the pivot table range. To collapse, press Shift and scroll DOWN with the mouse wheel.To expand, press Shift and scroll UP with the mouse wheel.In a pivot table, point to a cell in the Row or Column area. Click the +/- Buttons command, to toggle the buttons on or offĭid you know that you can use the mouse scroll wheel to expand and collapse the field details in a pivot table?.On the Ribbon, under PivotTable Tools tab, click the Analyze tab.If you want to hide the expand and collapse buttons, follow these steps: Unless you change the default pivot table settings, the expand and collapse buttons appear automatically when you create a new pivot table. In the screen shot below, the Bars category is collapsed for both the East and the North regions. NOTE: When you click a minus button, to collapse an item, all instances of that pivot item are collapsed. These pivot table Expand and Collapse buttons let you show or hide the details for a specific item, such as the Bars category (shown below), or an entire field, like Category.
Expand and Collapse Buttonsīy default, there are little plus and minus signs in a pivot table, to the left of the pivot item labels. Do you know the pivot table shortcut to expand and collapse the details? Keep reading, to see a quick and easy way to do this. For example, hide details for the East region, so only its totals are showing, and leave all the West region details visible. After you set up a pivot table, you can use the plus and minus buttons to show or hide the pivot table details.