Last week, someone asked me if there is a menu command for moving the fields in a pivot table layout. For example, if the Region field is in the Columns area, can you use a command to move it to the Rows area, or do you have to drag it with the mouse, in the PivotTable Field List?
Move a Pivot Field
Usually, if I want to move a pivot field, after adding it to the layout, I use the mouse to drag it. It’s quick and easy, and you can drag a field to a specific position within the existing fields in another area.
For example, the Region field can be dragged from the Columns area to the Rows area, above the City field.
Active Field Commands on the Ribbon
What if you don’t want to drag? What other ways can you move a pivot field?
If you select a cell in a pivot table, the PivotTable Tools tab appears, and you can click the Analyze tab (or Options in Excel 2010), to see the available commands for the Active Field.
There isn’t too much in the Active Field group. You can:
- see the name of the active field
- expand or collapse the field
- drill up or down (Excel 2013)
- open the Field Settings dialog box.
However, there isn’t a command on the Ribbon that will let you move the active field.
Move the Fields Without Dragging
Although there aren’t any commands on the Ribbon to do the job, there are commands on the field buttons, in the PivotTable Field List.
To see the PivotTable Field List, click any cell in the pivot table.
NOTE: If the list doesn’t appear, click the Analyze tab on the Ribbon, then click Field List.
In the Field List, click the arrow at the right side of a field button, then click one of the commands.
In the screen shot below, the Region field will move to the Rows area.
The Region field moves to the Row area, at the end of the existing fields.
If you want a field to appear in a different position within a section, you can use the commands to move it up or down.
In the screen shot below, the Region field will move to the top of the fields in the Rows area, after I click Move to Beginning.
Commands on the Worksheet
You can also use commands that appear if you right-click a pivot table Row or Column cell on the worksheet, and click Move.
In the screen shot below, I’m moving the Region to the right, so it appears after the City field. There are also commands to reposition the pivot item in the active cell – “East” in this example.
NOTE: With this menu, you can only move fields between the Row and Column areas — not to the Values or Report Filter areas.
To use these commands with the keyboard,
- Select a Row or Column cell in a pivot table
- Press the Menu key on the keyboard
- Use the Up and Down arrow keys to move through the list.
- Use the Right and Left arrow keys to open and close the submenus.
- Press Enter to select commands
Keyboard Shortcuts in the PivotTable Field List
If you like to keep your hands on the keyboard, you can use keys to access the menus for the field buttons in the PivotTable Field List.
- Press the F6 key as many times as necessary, to set the focus on the PivotTable Field List. It can be hard to tell what has focus, but something in the Field List will show as selected, when that pane has the focus.
In the screen shot below, you can see a faint outline around the TotalPrice field in the top section of the Field List.
- While the Field List has the focus, use the Tab key to move through its sections and fields.
- Tools (gear button at top right)
- Fields Section (top)
- More Tables
- Areas Section Field Buttons
- Defer Layout Update
- Use the Up and Down arrow keys to move within a section
In the screen shot below, the Region field is selected, in the Rows area.
- To open a list, press the Down arrow key
- Use the Up and Down arrow keys to move through the menu
- Press Enter to select a command, or press Esc to exit the menu.
I’ll stick with dragging and dropping though – it’s much quicker for me!
______________________
