After you build a pivot table, and add fields, you can rearrange the items in one or more of the fields
When you add a field to the Row Label or Column Label area of the pivot table, its labels are usually sorted alphabetically.
If you want the labels in a nonalphabetical order, you can manually move them, instead of using the Sort options. The following video shows 3 ways to manually move the labels, and the written instructions are below the video.
TIP: Use can use the same techniques to move pivot fields too. When field names are visible in Outline or Tabular Layout, you can type a field name that isn't in the layout, and it will be added.
This short video shows 3 ways to manually move the labels in a pivot table, and the written instructions are below the video.
To move a pivot table label to a different position in the list, you can drag it:
The existing labels shift down, and the moved label takes its new position.
To move a pivot table label to a different position in the list, you can use commands in the right-click menu:
The existing labels shift down, and the moved label takes its new position.
To move a pivot table label to a different position in the list, you can type its name over another label. Read the warning below, before you try this method.
The existing labels shift down, and the moved label takes its new position. For example, type "West" in cell A4, over the existing District name, "Central"
Then, press Enter, to complete the change. West moves to cell A4, and Central moves down to A5.
WARNING: Be careful when using this method. If you type a name that is not an existing label, you will rename the label that you typed over, instead of moving it.
For example, in the screen shot below, I typed "Est" instead of "East" in cell A4, when typing over the West label. The East label remains in cell A7, and the West label has been renamed as "Est"
If a pivot field is not in the layout on the worksheet, you can type its name over an existing label, to add it to the layout. This video shows the steps, and written instructions are below the video.
Here are the steps for moving or adding a field:
NOTE: Be sure type the field name correctly! If you make a typo, you'll change the label for the existing pivot field, instead of adding the new pivot field
The existing fields shift down, and the added field takes its new position.
In the screen shot below, I'm typing "Techs" where the District field label is.
When I press Enter, the Techs field is added to the Row area, and District moves to the right.
To follow along with this tutorial, download the Move Pivot Table Labels sample file. The zipped file is in xlsx format, and does not contain macros.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: October 26, 2018 9:20 AM
Contextures RSS Feed