- Excel Book Excerpt
Excel Manually Re-sequence the Order of Data in a Pivot Table
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Manually Re-sequence the Order
of Data in a Pivot Table
Problem: By default, a pivot table organizes data alphabetically. For the Region field, this means the data is organized with Central first, East second, and West third. My manager wants the regions to appear in the order East, Central, West. After unsuccessfully lobbying to have the Central region renamed Middle, I need to find a way to have my table sequenced with the East region first.
Figure 823 Central, East, West is the alphabetical sequence.
Strategy: It is amazing that this trick works. Try it:
1. Select cell B4 in the pivot table.
2. In cell B4, type the word East.
Figure 824 Go to the Central cell and type a new heading.
3. When you press Enter, Excel senses what you are trying to do. All the data from the East region moves to Column B. Excel automatically moves the Central region heading and data to column C.
Figure 825 East and Central switch! Never try this outside a pivot table.
You can easily use this trick to re-sequence the fields into any order as necessary.
Additional Details: This technique will only change the Region sequence in a single pivot table. If you would like to change the sequence in all future pivot tables, you need to create a custom list with the regions in the proper sequence. See “Have the Fill Handle Fill Your List of Part Numbers” on page 66. Any pivot tables created will follow the custom list sequence.
For more resources for Microsoft Excel: